Mar 032014
 

It turns out that in System Center Operations Manager 2012 R2 (SCOM 2012 R2), there is a pretty nasty, but well documented bug that causes corruption and the following error:

SCOM Specified cast is not valid

After clicking OK a few times, you can attempt to view the Distribution tab, but will find it is now empty:

SCOM Empty Distribution Tab

The root cause of this is likely due to the uninstall of a previously managed agent. If you are in a large shop with multiple admins, which agent was it that is currently causing the problem? Since I’m a SQL guy, I took to Transact-SQL to find the answer. Log into your OperationsManager database instance, and run the following SQL to find the bad agent:

SELECT BME.DisplayName,
       BME.IsDeleted,
       CHS.SecureStorageElementId,
       CHS.HealthServiceId,
       CHS.LastModified
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
  JOIN [OperationsManager].[dbo].[CredentialHealthService] AS CHS
    ON CMSS.SecureStorageElementId = CHS.SecureStorageElementId
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service

Objects with the IsDeleted column set to 1 are likely your problem agents.

So, how to clean this up? Well, there are a few options. One, if you are lucky you can re-approve the agent and after which you should be back to normal. Two, you can delete the offending row from the dbo.CredentialHealthService table. Lastly, and the option I’ll use is to call the same procedure that the SCOM Console uses in the GUI. To do that, run the following SQL:

DECLARE @SecureStorageElementId NVARCHAR(36);
DECLARE @HealthServiceIdList NVARCHAR(MAX);

SELECT @SecureStorageElementId = CMSS.SecureStorageElementId
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service
   /* or use CMSS.DisplayName, which is what the SCOM console shows */;

SELECT @HealthServiceIdList = COALESCE(@HealthServiceIdList + N',', N'') + CAST(CHS.HealthServiceId AS NVARCHAR(MAX))
  FROM [OperationsManager].[dbo].[CredentialHealthService] AS CHS
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
   AND BME.IsDeleted = 0
 WHERE CHS.SecureStorageElementId = @SecureStorageElementId;

PRINT N'exec dbo.p_SetApprovedHealthServicesForDistribution 
            @SecureStorageElementId=''' + @SecureStorageElementId + N''', 
            @HealthServiceIdList=N''' + @HealthServiceIdList + N''', 
            @ApprovedHealthServicesResult=2;';

The output of which, you are free to inspect – no changes are made – until you are comfortable with running it. When you are, simply copy and paste the output and execute against your OperationsManager database. The output is exactly what the SCOM console would do if you were making changes to the distribution list. Running the output of the above script is done at your own risk. Changing data in the SCOM OperationsManager database is not officially supported. Contact Microsoft support if you are in an emergency. Feel free to mention the above solution to your support engineer, however.

Apr 232013
 

When installing the Microsoft Assessment and Planning (MAP) Toolkit (commonly used for licensing renewal discussions and migration planning), one of the options is to have the tool use the new SQL Server Express LocalDB database container. Because this isn’t a normal SQL service, you may find it a bit challenging to connect to this database outside of the MAP toolkit.

If you want to connect to this database instance with SQL Server Management Studio (SSMS), use the following as your connection string: (LocalDB)\MAPToolkit. As long as you are the owner of the LocalDB instance, you will be able to connect just fine. If you are not the owner of the instance, you will get the following error:

LocalDB Connection Failure

If you run into this, verify who the owner of the LocalDB instance is, by excecuting SqlLocalDb.exe from a command line as follows:

SqlLocalDB.exe info MAPToolkit

Which should return details about who owns the instance, when it was last started, and its state (running, for instance):

LocalDB instance "MAPToolkit" started.

C:\Users\Phil.Brammer>SqlLocalDb.exe info MapToolkit
Name:               MAPToolkit
Version:            11.0.2100.60
Shared name:        
Owner:              MyDomain\Phil.Brammer
Auto-create:        No
State:              Running
Last start time:    4/23/2013 9:04:31 AM
Instance pipe name: np:\\.\pipe\LOCALDB#SH2C50D1\tsql\query

Here we can see that “MyDomain\Phil.Brammer” owns this instance. Any other user will not be able to connect to it. In order to allow other users to connect, follow these steps:

  • Connect to the instance in SSMS (logged in as the owner shown above)
  • For the users/groups you want to be able to connect, add them with the SQL “sysadmin” server role.
  • Share the LocalDB instance. The following command will do the trick: SqlLocalDb.exe share MAPToolkit MAPToolkitShare
  • Stop the MAPToolkit LocalDB instance if it is running. SqlLocalDb.exe stop MAPToolkit
  • Start the MAPToolkit LocalDB instance. SqlLocalDb.exe start MAPToolkit
  • As the other user, open up SSMS and connect to the shared LocalDB instance using the following connection string: (localdb)\.\MAPToolkitShare

To see details about the shared LocalDB instance, you can execute SqlLocalDb.exe info MAPToolkit -or- SqlLocalDb.exe info .\MAPToolkitShare

Either command will show the same results:

C:\Users\Phil.Brammer>SqlLocalDb.exe info .\MapToolkitShare
Name:               MAPToolkit
Version:            11.0.2100.60
Shared name:        MAPToolkitShare
Owner:              MyDomain\Phil.Brammer
Auto-create:        No
State:              Running
Last start time:    4/23/2013 9:17:35 AM
Instance pipe name: np:\\.\pipe\LOCALDB#SH80EAD1\tsql\query

You can unshare the database with SqlLocalDb.exe unshare .\MapToolkitShare

Note: LocalDB does not allow remote connections. You will need to connect from the machine where the LocalDB instance is running.

Mar 072013
 

Fresh off of the press, Microsoft has announced the availability of SSDT for Visual Studio 2012. This means you can now build SSRS, SSAS, and SSIS BI projects within Visual Studio 2012, allowing you to abandon VS 2010 for SQL 2012 BI work.

For more details see the official announcement here. You can download the bits here.

When installing, please follow the below steps:
– Double-click the downloaded file from the above link to start the install.
– When you see “SQL Server 2012 Setup” don’t panic. You’re at the correct place.
– Accept the license terms, click Next.
– After the setup files are installed and you are at the “Installation Type” screen, click Next. (Do not “add features to an existing instance of SQL Server 2012″)
– On the “Feature Selection” screen, select “SQL Server Data Tools…” from the Shared Features section. Click Next.
– In the “Error Reporting” screen, select the error reporting option if you desire, otherwise just click Next.
– After the install complete, you may be presented with a “computer restart required” dialog box. Click OK.
– At this point, the install should be complete. Click close and restart if necessary.
– Open Visual Studio 2012 and start a new BI project.

When VS 2012 is open, you should see the familiar BI templates as below:
SSDT & VS2012

Enjoy!

Jan 312013
 

2013-11-26 – UPDATE – SQL Server 2012 CU 7 -or- SQL Server 2012 SP1 CU4 addresses the indexing recommendations. See KB 2829948 for more details.

I’ve been sitting on this content for about a year now, and due to external pressure (Thanks Matthew!) I’ve decided to put this into writing. One of the reasons I’ve been sitting on this content is that I didn’t want to share it publicly because the recommendations (after the break) can put you into a state of unsupportability with Microsoft. So with that, you take on the sole risk by implementing my recommendations. After you review them, you’ll see they are pretty basic, much needed, and well vetted. Jamie Thomson has written about these indexes (or rather mentions that they are available) as they have helped with his reporting pack Codeplex project. All-in-all, I now feel pretty safe documenting these, as others are starting to use them and talk about them publicly. With that, here goes.

Continue reading »

Jan 072013
 

Waaaaay back in 2012, I had the great pleasure of being a technical reviewer of another book, What’s New in SQL Server 2012, authored by Rachel Clements (@RachelClements) and Jon Reade (@JonReade) and published by Packt Publishing.

Together, these two authors put together a very nice desk reference of the new features introduced in SQL Server 2012, with topics ranging from an overview of the various SQL Server editions to new functions, such as EOMONTH (yep, there’s a new “End of Month” datetime function).

I highly recommend this book for any SQL Server user who is new to SQL Server 2012.

Obtain your copy here: http://www.packtpub.com/sql-server-2012-professional-tips-and-tricks/book

Jan 072013
 

If you are on SQL Server 2012 SP1 (11.0.3000) and are experiencing higher than normal CPU loads on your machine, please take a look at the Windows Application log for the following:

  • Warning / Event ID 1004 / MsiInstaller / Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_ANS’, component ‘{0CECE655-2A0F-4593-AF4B-EFC31D622982}’ failed. The resource ” does not exist.
  • Warning / Event ID 1001 / MsiInstaller / Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_ANS’ failed during request for component ‘{6E985C15-8B6D-413D-B456-4F624D9C11C2}’
  • Information / Event ID 1040 / MsiInstaller / Beginning a Windows Installer transaction: {A7037EB2-F953-4B12-B843-195F4D988DA1}. Client Process Id: 16708.
  • Information / Event ID 11724 / MsiInstaller / Product: SQL Server 2012 Management Studio — Install started.
  • Information / Event ID 10000 / RestartManager / Starting session 0 – 2013-01-07T12:40:46.549958800Z.
  • Information / Event ID 11728 / MsiInstaller / Product: SQL Server 2012 Management Studio — Configuration completed successfully.
  • Information / Event ID 1035 / MsiInstaller / Windows Installer reconfigured the product. Product Name: SQL Server 2012 Management Studio. Product Version: 11.1.3000.0. Product Language: 1033. Manufacturer: Microsoft Corporation. Reconfiguration success or error status: 0.
  • Information / Event ID 1042 / MsiInstaller / Ending a Windows Installer transaction: {A7037EB2-F953-4B12-B843-195F4D988DA1}. Client Process Id: 16708.
  • Information / Event ID 10001 / RestartManager / Ending session 0 started 2013-01-07T12:40:46.549958800Z.

If you have the above symptoms, please look at this non-security hotfix KB download: http://www.microsoft.com/en-us/download/details.aspx?id=36215. Do not install if you have already applied any post SP1 hotfixes, such as SP1 CU1. You can also view the SQL Server Connect bug here: http://connect.microsoft.com/SQLServer/feedback/details/770630.

Installing that fix will bring your SQL version to 11.0.3128, fix the problem with the MSI installer, and it should drop your CPU consumption a bit as well.

Dec 032012
 

While I have not been active in the Omaha SQL Server User Group (SSUG) for all of its five years, I am proud to be part of an ever-growing SQL Community in the Omaha area. Please be sure to join me on Wednesday, December 5th, at Farm Credit Services America for our last 2012 SSUG meeting.

To celebrate the five year anniversary of the Omaha SSUG, the topic of the evening will be … YOURS! John & Naras are bringing back the “Choose Your Own Adventure” agenda, where you get to bring your own questions, problems, fodder, tips, and best practices to be discussed by all attendees. If you haven’t been to one of these sessions before, or are feeling as if you just can’t quite find the answer to your questions online, be sure to take part in this meeting. They are extremely informational and beneficial to the SQL Community.

I will be in attendance, and if there are any questions that need to be raised to Microsoft, I will do my best to ensure the appropriate people see it.