Checklist for changing SQL Server instances

On my current project, several times I’ve had to configure a new machine with SQL Server to support a Visual Studio database project, or change instances (say to install SQL Dev to support Reporting Services instead of using SQL Express). 

BTW, whenever doing development, I highly recommend installing SQL Server Developer Edition prior to installing Visual Studio instead of letting Visual Studio install SQL Express.

So, here’s my checklist of things to do when setting up a new instance or changing from one instance to another:

1. Install the new instance.
2. Use the Surface Area Configuration manager to set the new instance for network access.
3. Create security accounts in the new instance matching those in the old instance.
4. Open the new instance in SQL Management Studio.
5. Right-click on the instance and select Properties to set the security for mixed mode (as shown).
6. Make a list of the security accounts in each database in the old instance that are bound to top-level security accounts in that instance (thus permitting log-in or network access).
7. Drop the databases from the old instance and attach them to the new one.
8. For each security account in each database that should bind to a top-level security account, note its permissions, delete the account, recreate the account with a binding to the appropriate top-level account, and re-grant it the same permissions.
9. Change connection parameters in your apps and test that connectivity works as it used to.  In Visual Studio Database Edition, there are two places to change the instance in the Tools > Options, and the important one is here:

Besides these items I’ve listed, also check out this link for getting Visual Studio Team Build to work in a CI environment with a new instance:

Error: You have insufficient permissions to create the database project

Comments

[Todd Girvin]

We ran into some other funky stuff while configuring our build server and this helped: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/793fa3e2-3852-4450-b37e-83ec673388dd/?prof=required