Thursday, January 22, 2009

Improvements To Scripting In Management Studio 2008

SQL Server Management Studio (SSMS) 2008 contains some minor but very welcome improvements to scripting. In this post I’ll highlight the ones I’ve noticed so far.

Object Explorer
In Object Explorer you can right click almost anything and chances are there’s a menu option for Script [OBJECT] as with submenu options for CREATE To, ALTER To, DROP To, etc.. In SSMS 2005 there are three choices available: New Query Editor Window, File…, and Clipboard. The screenshot below shows an example of these options when scripting a table:

SSMS 2005 "Script [Object] as" menu options
SSMS 2005 "Script [Object] as" menu options

The screenshot below shows the same menu options in SSMS 2008. In addition to new eye candy – icons for each option – there is a new option to script to an Agent job. Choosing this option opens a new window which allows you to schedule a one time SQL Agent job that will perform the scripted action.

There’s also a new action to DROP And CREATE the object. My typical usage pattern when scripting individual objects in SSMS 2005 was to script the drop to a new window, then script the create to the clipboard and paste it into the query window I just created for the drop. The new action saves me the extra clicks and makes for a good user experience.

SSMS 2008 "Script [Object] as" options
SSMS 2008 "Script [Object] as" menu options

Generating Replication Scripts
Some details about publications and subscriptions are only exposed by generating a script. When scripting replication in SSMS 2005 there is only a single option to write the script to a file. For someone like me who works with replication regularly this is a HUGE annoyance. That means I have to write the script to a file, close the scripting window, and open the file I just created. That’s a lot of extra steps for something that should be simple.

SSMS 2005 Replication scripting optionSSMS 2005 Replication scripting option

Fortunately this has been corrected in SSMS 2008. Now there are options for Save to Clipboard and Open in New Query Window in addition to Save to File. Finally Microsoft doesn’t make you jump through hoops just to look at the underlying script for a publication!

SSMS 2008 Replication scripting options
SSMS 2008 Replication scripting options


Scripting Options
Scripts are generated in Object Explorer based on the options set in the SSMS Options dialog. To get to this menu in SSMS 2005 click Tools –> Options, then navigate to Scripting; In SSMS 2008 click Tools –> Options, then navigate to SQL Server Object Explorer\Scripting. Here are screenshots of the options in both versions:

SSMS 2005 Scripting options
SSMS 2005 Scripting options

SSMS 2008 Scripting options
SSMS 2008 Scripting options

SSMS 2008 adds two new options. They are:

  • Script change tracking: This option compliments the new change tracking features in SQL Server 2008. When this option is enabled, change tracking options that apply to the object will be included in the script. This option has no effect if the target script’s version is set to SQL 200 or SQL 2005.
  • Script Data Compression Options: Another SQL 2008 specific option that will include any data compression options relevant to the object being scripted.

Curiously, two existing options were renamed. Include vardecimal options was renamed as IncludeVarDecimal and Include system constraint names was renamed as ScriptDriIncludeSystemNames. These were probably unintentional and just slipped by during QA.

Though minor, the scripting options enhancements in SSMS 2008 are extremely useful; they eliminate frustrating annoyances and help improve productivity by reducing the time and number of clicks it takes to perform common repetitive tasks. The improvements are a sign that the development team at Microsoft is listening to feedback to help create a more polished tool and better user experience.

Thursday, January 15, 2009

Changing Machine SID With NewSID Breaks SQL Server (And How To Fix It)

Like a lot of other companies, we use imaging software to get new\repurposed machines up and running quickly. We recently switched to a new imaging product and it seemed to work well. We used it on a couple of SQL Servers that made their way into production and shortly after they went live we noticed intermittent errors with clients trying to connect using Windows authentication that looked like this:

Login failed for user ''. The user is not associated with a trusted SQL Server connection.

This usually happened a few times a day for a couple of minutes and then the system self-corrected. We also found that right before the login failures occurred was this entry in the system event log:

Event Type:    Error
Event Source:    NETLOGON
Event ID:    5719
This computer was not able to set up a secure session with a domain controller in domain [OUR DOMAIN] due to the following:
Not enough storage is available to process this command. 
This may lead to authentication problems. Make sure that this computer is connected to the network. If the problem persists, please contact your domain administrator.

So the NETLOGON service was failing and killing SQL’s ability to authenticate users. While digging through the logs from each image looking for a possible clue one of my coworkers noticed a single line buried deep within that said something to the effect of “SID change failed”.

SID stands for “Security Identifier” and is a unique alpha character string that’s used in Windows and Active Directory as the basis for identifying computers and objects in the operating system (read more about SIDs here). Usually when you apply an image you change the SID to make your server unique, thereby avoiding potential security problems (see Microsoft KB article 162001), only in our case the imaging software failed to do so and as a result we had several machines on the network that had duplicate SIDs.

One Solution…And The Problem It Causes 
One solution to the dupe SID problem is to use the NewSID utility from Sysinternals to create a new SID for the affected servers. But here’s the catch: Local accounts, groups, object names, etc. in the OS are based on the machine SID. Change the machine SID and every other SID gets changed too. During installation SQL Server creates a set of local machine groups (e.g. MACHINE\SQLServer2005MSSQLUser$MACHINE$MSSQLSERVER) that are granted rights throughout the OS and in SQL Server so that the services can run. SQL Server stores the logins - and SID - for each of those groups in master.dbo.syslogins. NewSID wasn’t written with SQL awareness so after using it to change the machine SID (which in turn changes the SID for each local group) you’re going to be left with the old group SIDs in SQL, new group SIDs in the OS, a SQL service that won’t start, and probably a lot of people wondering “WTF when is the server going to be back up?”

A Solution To The Problem Caused By The Solution
I’ve been through the SID change exercise a few times now so I’ll share with you what to do if you need to change the SID on one of your SQL severs.

0. Find out what Service Pack and Cumulative Update your SQL service is running using the statement below. Get a copy of it and keep it handy because you’ll need it later.

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

1. Change SQL service and SQL Agent service to run as Local Service (or Local System) and manual startup.

2. Remove server from domain. Reboot.

3. Change SID using NewSID (note: record your old SID before changing it in case you have to roll back!). Reboot.

4. Your SQL service is now broken by the SID change. Here’s where it gets fun. The SQL service will start but the SQL Agent service won’t and a couple of other components like DB Mail will throw permissions errors that show up in the SQL error log. To get things back on track resynchronize what’s in master.dbo.syslogins with the new local group SIDs by dropping and recreating the SQL logins (and associated permissions) for the groups. First, using Object Explorer script out the drop and create statements for each local group login. Then use this script I posted previously to script the statements which grant all the necessary server level permissions to each group. Cut and paste the statements that apply to the local groups into your working script. What you’re going to execute for each group will look something like this:

EXEC sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'SQL2300\SQLServer2005MSSQLUser$MACHINE$MSSQLSERVER'

Execute your script and restart the SQL and SQL Agent services to get them back up and running.

5. Join the server back into the domain. Reboot.

6. When you use Configuration Manager to try and change your services to run under a domain account again you’ll get this WMI error:

No mapping between account names and security IDs was done [0x80070534]

To fix the WMI error simply reapply a Service Pack\Cumulative Update that is the same or newer than the SQL service is already running at. But…when you try to do the install it will fail. Dig through the install log in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix and you’ll find this error:

MSP Error: 29528  The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.

Basically the problem is that old SIDs are stored in the registry and need to be deleted. Check out Microsoft KB article 925976 for details on what to do. Once you’ve deleted the registry entries you can proceed with reinstalling the current SP\CU. Reboot. Now you should be able to use Configuration Manager to change your services to run under a different account.

At this point everything should be running again; the problems caused by changing the SID are resolved and the login errors from the dupe SID should also go away. *PHEW*!

Although I was able to reproduce and fix the problem several times your installation might have some nuances that mine doesn’t, so consider these steps as more of a guide than a hard and fast rule. Although I used NewSID there are other SID changing utilities out there that might create the same problem. As always, test this out in a non-production environment first!

Wednesday, January 14, 2009

Scripting Server Permissions And Role Assignments

No doubt at some point you’ve had to move or copy a database from one server to another. Usually you’d use sp_help_revlogin to script out any server logins that you needed to preserve but it falls short on scripting the server permissions and role assignments associated with said logins. Here’s a script that I use to accomplish that task:

/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */

'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context'

-- Role Members
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
QUOTENAME(, '''') + ', @loginame =' + SPACE(1)
QUOTENAME(, '''') AS '--Role Memberships'
FROM    sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC

-- Permissions
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']' AS '--Server Level Permissions'
FROM    sys.server_permissions AS server_permissions WITH ( NOLOCK )
INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE   server_principals.type IN ( 'S', 'U', 'G' )

Thursday, January 8, 2009

Installing SQL Express 2008 As A Default Instance

I installed SQL Express 2008 yesterday for the first time and ran into an interesting “feature”. I was trying to set it up as a default instance, which seems pretty straightforward based on this screenshot:


The only problem is that after the install was complete I had a named instance called SQLEXPRESS. I did the install again, thinking maybe I had missed something, and got the same result. It turns out that the current release of SQL Express simply ignores that “Default instance” checkbox and creates an instance with whatever name was entered in the “Instance ID” field.

Fortunately there’s a workaround: Create a named instance called MSSQLSERVER (also using MSSQLSERVER as the Instance ID):


This issue has been reported as Bug ID 361809 in Connect and Microsoft has indicated that it will be fixed in the next public update which will be released “soon”

Wednesday, January 7, 2009

SQL Server Management Studio 2008 Installation Walkthrough

I did a SQL 2008 install today which reminded me I had written this a few months ago. My original intent was to write a series on SSMS 2008 but I got sidetracked by work, life, etc. and filed this away. There’s no sense in having put the effort into it and then let it go to waste, so for your reading pleasure…

When you're ready to install SSMS 2008 be prepared to set aside at least an hour and as many as three depending on the speed of your machine and how current you are on patches and updates. There is are also 1-2 reboots required to complete the install. This walkthrough covers a Developer edition install on a clean Windows XP SP3 configuration that's been patched with all critical updates available at the time on an HP nc8000 laptop.

Note: Click on each thumbnail to view a full size image.

The first thing you'll get when you run the installer is a warning that the .NET framework needs to be installed along with some other updates.

Prerequisite Warning Dialog


The setup needs to install .NET Framework 3.5 SP1 and wants to download 52 MB of files to do it. The files are actually on the DVD though, so setup moves right along to the installation. Install time: ~15 minutes, no reboot required.

.NET Framework Install


After the .NET Framework I need to install a Hotfix for KB942288 which is really Windows Installer 4.5. No download required. Install time: ~2 minutes, plus a reboot.

Windows Installer 4.5 Install


If you've got Visual Studio 2008 installed you'll also be required to apply VS 2008 SP1....yet another download, install, and reboot. This download is not on the DVD so be prepared to take a coffee break while the download completes. Total install time for VS 2008 SP1 was about an hour on my laptop.

Finally after all the prerequisites are taken care of I can get to work on installing Management Studio. SQL 2008 uses an Installation Center with high level tasks on the left and subtasks on the right.

Installation Center: Planning


Choose Installation on the left, followed by "New SQL Server stand-alone installation or add features to an existing installation" on the right.

Installation Center: Installation


This launches another setup window which checks some more prerequisites (called Setup Support Rules)

Setup Support Rules


Enter the product key. The MSDN version I used was pre-populated.

Enter Product Key


Accept that Microsoft will one day rule the world (aka License Terms)

Accept License Terms


Now watch the “Setup Support Files” progress. Install Time: ~5 minutes

Install Setup Support Files


Another setup support rules screen which checks more prerequisites.

More Setup Support Rules


Finally we’re at feature selection. There's now two kinds of installs for Management Tools: Basic, which installs only Management Studio, SQLCMD, and Powershell...and Complete, which adds support for Reporting Services, Analysis Services, and Integration Services plus Profiler and the Database Tuning Advisor. For this post I did a Complete install and Books Online.

Feature Selection


Review the disk space requirements.

Disk Space Requirements


Decide whether or not you want to send Microsoft data about how your installation went.

Error and Usage Reporting


Setup then checks to make sure the installation won’t be blocked by anything. I’m thinking this should have been checked earlier; I’d hate to have gotten this far only to be blocked by something else not already accounted for.

Installation Rules


Finally an "Install" Button!

Ready To Install


Now watch the installation progress. Take another coffee break, read your daily blogs, go to lunch...or be prepared to watch this one for a while.

Installation Progress


Install Complete!

Install Complete


Install Summary. Install Time: ~45 minutes

Install Summary

Installation wasn’t particularly painful or difficult, just time consuming. Set aside at least an hour or more to do this, and it’s probably best to do it at as an end of day or evening task when you don’t need to use your computer to do anything particularly important or otherwise work-related.

Friday, January 2, 2009

New Year’s Resolutions

2008 is in the rear view mirror and it’s time to participate in the annual tradition of making resolutions for the new year. Here’s mine:

  1. Learn…
    • PowerShell. I’ve got a soft spot for command line scripting and PowerShell finally brings Windows up to date with the kind of scripting that Unix\Linux has had for quite a while. It’s time to graduate from DOS batch files and grow up.
    • More about DMVs. They expose a boatload of information about server usage and performance, yet I only know a handful of them at best.
    • SQL 2008. It’s been out 5 months now and I’ve done little more than install it once on a virtual PC.
    • Analysis Services. I said the same thing last year. This year I mean it. No, really!
  2. Be a better mentor. For 2 years at my job I was the sole production DBA and got used to doing everything myself. This year my company hired a second DBA. It’s been a challenge to let go of certain things and this year I’ve got to do a better job of passing on what I know.
  3. Publish twice a week. When I started this blog I set a goal to post at least once a week. In 2009 I’m shooting for at least twice a week.
  4. Go to the 2009 PASS Community Summit. I have never been and from what I read I really missed out by not making it in 2008. I intend to go in 2009 – be it as a speaker, a volunteer, a reporter, or just as myself.
  5. Run a half-marathon. I need to take better care of my health, and that means more than playing Wii Fit every now and then.

I hope that you have a happy and productive 2009!