DPM 2010 Slow when Selecting Roverypoint to Recover

It took almost an hour to select a date and time to recover in DPM 2010. It appeared the this was because of high cpu usage by SQL. The query that seemed to be responsible for this was:

SELECT Path, FileSpec, IsRecursive
    FROM tbl_RM_RecoverableObjectFileSpec
    WHERE RecoverableObjectId = @RecoverableObjectId AND 
          DatasetId = @DatasetId and
          iSgcED = 0

I didn't dig into things too much, but it appeared as though it was running this query for every single recovery point for the item selected and it was doing a clustered index scan for each recovery point. I created the following statistic and covering nonclustered index in the DPM db:

CREATE STATISTICS [_dta_stat_1042102753_9_2_3] ON [dbo].[tbl_RM_RecoverableObjectFileSpec]([IsGCed], [RecoverableObjectId], [DatasetId])

CREATE NONCLUSTERED INDEX [_dta_index_tbl_RM_RecoverableObjectFileSpec_7_1042102753__K2_K3_K9_5_6] ON [dbo].[tbl_RM_RecoverableObjectFileSpec] 
(
[RecoverableObjectId] ASC,
[DatasetId] ASC,
[IsGCed] ASC
)
INCLUDE ( [FileSpec],
[IsRecursive]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Now the above query changed from a clustered index scan to a index seek and key lookup. The time it took to select a recovery point went from about an hour down to a minute.

Email When Scheduled Task Fails

1. Create a new scheduled task
2. On the actions tab, click new
3. Change the action to send an e-mail
4. Enter the to, from, subject, text and smtp server information
 For subject and text enter something along the lines of "A schedule task failed on server so and so"
5. Click OK

6. On the triggers tab click new
7. Change the "Begin the task" drop down to "On an event"
8. Click "Custom" under settings
9. Click "New Event Filter"
10. Click the XML tab
11. Check Edit query manually
12. Enter the following XML for the query:

<QueryList>
  <Query Id="0" Path="Microsoft-Windows-TaskScheduler/Operational">
    <Select Path="Microsoft-Windows-TaskScheduler/Operational">*[System[Provider[@Name='Microsoft-Windows-TaskScheduler'] and (EventID=201) ]]
and
*[EventData[Data[@Name='ResultCode'] and (Data='1')]] </Select>
  </Query>
</QueryList>

Now anytime a schedule task completes with a result code of 1, an email will go out letting you know. 1 in our case indicates an error. What ever task you have may return other codes to indicate errors. You could say were data > 0.

IIS and Certificates Random Musings

If you view a cert, windows goes out and downloads the root and puts it in the third-party trusted root certificate authorities store.

If you select a cert in IIS binding, windows goes out and downloads the root and puts it in the third-party trusted root certificate authorities store. It also downloads the intermediate certificates and places them in the intermediate certificate authorities store. (Assuming the cert has a proper AIA configured and accessible.) Thus, IIS will work and serve the intermediate certs even if you didn't explicitly install them into the intermediate cert store. I'm sure this was done to make things simple on admins.

I disabled the gateway and dns so that the windows box could not get out to the internet. Windows did not download the chain when viewing the cert by double clicking and IIS only served the one certificate.  IIS did not download the intermediate certs. (It couldn't.)

You can view the certificates IIS serves with openssl:

openssl s_client -showcerts -connect www.domainname.com:443

Of note, it appears as though the windows crypto APIs cache previous root certs. I viewed a cert while the server had internet access. Windows downloaded the root cert and put it in the third-party trusted root certificate authorities store. I removed the root cert from the third party store, disabled internet connectivity and viewed the same leaf cert again. Without an internet it placed the root cert in the third-party trusted root certificate authorities store again.

While disconnected, I manually installed intermediate certs in intermediate store. IIS did not server them until I touched the bindings of the site in IIS. You need to edit the binding in IIS and just hit ok for the new chain to be served. Just adding intermediate certs into the store will not do it.

It looks like IIS creates the cert chain when the binding is configured and then stores it somewhere for future access. Likely so that path resolution doesn't have to occur over and over. IIS just has to do it once on binding configuration. Restarting IIS/the website had no effect. The binding needed to be modified/re-applied. Restarting the server seems to have updated the certificates served by IIS. IIS's cert chain storage must not be persistent.

I tried placing the intermediate cert in the personal store and IIS did not serve it. I tried placing the intermediate cert in the root store and IIS did server it. So technically intermediate certs could go in the root or intermediate store. I would not put them in the root though as that has other implications.

The big take aways here are:

1. Windows/IIS will try to make things easy for you as an admin and download/configure cert chains for you
2. IIS will only update the certificates it send to clients when you update the binding or restart the server

In the future I'd like to test out certs with multiple paths and discover how IIS/path resolution determines path priority. Will one store take precedence? Does it base it solely on whatever window's path selection algorithm is? How will auto downloading play in? I personally think the way IIS serves certificates could use an overhaul. It would be nice to be able to server multiple certificates from IIS. For example, depending on client handshake capabilities, serve either RSA or ECDSA hashed cert for authentication to root CA. Then we could easily transition to ECC certs.

Find Custom Config Section in web.config by Type instead of by String

Instead of calling Configuration ConfigurationManager.GetSection("name of section"), call the following:

CustomSection section = GetConfigSection(typeof(CustomSection)) as CustomSection;

This way, you don't have to hard code the name of the section into your code and it allows who ever is consuming your code to change section names and group them as they see fit.

You'll need to add the following code for the above function. You could add it to your config class and add strong typing or you could add it to a utility class.

/// <summary>

/// Instead of getting the config section by name, use this to get the config section by type. It will return the first section with a matching type. 

/// Thus if there are multiple sections with the same type, it will only return the first one.

/// Call like CustomMailWebEventProviderSection section = GetConfigSection(typeof(CustomMailWebEventProviderSection)) as CustomMailWebEventProviderSection;

/// </summary>

/// <param name="configSectiontype">Type of config section you're looking for</param>

/// <returns>Config section of the specified type or null if no section of that type is found</returns>

public ConfigurationSection GetConfigSection( Type configSectiontype )

{

    // Set it up and call recursive function

    Configuration config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");

    return GetConfigSection(configSectiontype, config.Sections, config.SectionGroups);

}

// returns first config section that matchs the type we're looking for

public ConfigurationSection GetConfigSection(Type configSectiontype, ConfigurationSectionCollection sections, ConfigurationSectionGroupCollection groups)

{

    if (sections != null)

    {

        foreach (ConfigurationSection section in sections)

        {

            if (section.GetType() == configSectiontype)

            {

                return section;

            }

        }

    }

    if (groups != null)

    {

        foreach (ConfigurationSectionGroup group in groups)

        {

            ConfigurationSection section = GetConfigSection(configSectiontype, group.Sections, group.SectionGroups);

            if (section != null)

            {

                return section;

            }

        }

    }

    //section not found

    return null;

}

Microsoft SQL Useful Database Role for Service Broker: db_servicebroker

If you need to use the service broker for cache expiration or what not, you should create a service broker database role called db_servicebroker. Then assign any user that require the service broker this role. Below is the code to create the db_servicebroker role. It sets up all the database permissions needed:

/** CREATE ROLE AND GIVE PERMISSIONS **/

CREATE ROLE [db_servicebroker] AUTHORIZATION [dbo]

GO

GRANT CREATE PROCEDURE TO db_servicebroker;

GRANT CREATE SERVICE TO db_servicebroker;

GRANT CREATE QUEUE TO db_servicebroker;

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO db_servicebroker;

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO db_servicebroker;

GRANT RECEIVE ON QueryNotificationErrorsQueue TO db_servicebroker;

Now any time a user requires service broker permissions you can just assign them to this role. You can also view all the users in this role and know who has service broker permissions.

I've left one thing out though, and that's schemas. The service broker uses the default schema of the user to create the needed sprocs, queues and services. Therefore there are two routes you can take. One is to use the same schema, usually dbo, and the other is to create a separate schema per user. I prefer the later. Then when you're looking at the sprocs and queues, you know who it belongs to. I also prefer separate schemas because of the about grant permissions. If you have separate schemas, the users will be sand-boxed into their own respective schema.

If you're going the single schema route, which I don't recommend, you'll need to run the following:

GRANT CONTROL ON SCHEMA::[dbo] to [db_servicebroker];

GRANT IMPERSONATE ON USER::DBO to [db_servicebroker];

Just add users to the db_servicebroker role. This basically will give whoever is in the db_servicebroker role full keys to the kingdom.  Which is not good.

A better approach is to create a separate schema per user, set the user as the schema owner and set the schema as the user's default schema, replacing <UserName> with the name of the user execute the following per user:

CREATE SCHEMA [<UserName>Schema] AUTHORIZATION [<UserName>]

ALTER USER [<UserName>] WITH DEFAULT_SCHEMA=[<UserName>Schema]

Then just add the user to the db_servicebroker role.

Microsoft SQL Useful Database Role to Execute Stored Procedures: db_executor

I find it convenient to create a db_executor database role to give users the ability to execute stored procedures. This way you can look at the user's roles and just know they have the ability to execute stored procedures. Otherwise you have to go look at the permissions at the database level and most people forget or don't know to look there. You create the role as follows:

-- Create a db_executor role
CREATE ROLE db_executor 

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor 

Then add the user to the role as follows, replacing <UserAccount> with the name of the user:

EXEC sp_addrolemember N'db_executor', N'<UserAccount>'

or if you have a newer version of SQL server:

ALTER ROLE [db_executor] ADD MEMBER [<UserAccount>]

MSSQL Database/Server User/Login Mapping

This is just a quick post, mainly so that I can refer back to it when I forget the correct syntax.

In Microsoft SQL server there two separate principals. There is a principal (user) at the database level and there is a principal (login) at the server level. A login is mapped to one or more users in one or more databases. A password or an windows account is associated to the login. When you move a database or restore from one server to another, this mapping breaks and must restore it. People all too often drop the user and login and then recreated them both. If you have permissions associated to the user though, you'll have to recreate those. The best thing to do is just update the mapping (which just updates the associated SIDs in the appropriate system tables.)

USE [DatabaseName]
ALTER USER [UserAccount] WITH LOGIN = [LoginAccount]

It's simple, but since I'm not always in DB land, I find myself forgetting the syntax.