Sql-server – How to compare two SQL Servers instance level objects for a migration

migrationscriptingsql server

I'm currently working on a SQL Server migration (from a SQL Server 2005 to a 2008 R2 server, on a new physical server).

The databases themselves will be transferred using a database backup and restore script.
I have tested this fine and it works without problems.

My worry is rather regarding all the other instance/system objects, like Logins, SQL Agent jobs.
These have been scripted out from the source server and reapplied on the new server, and I'm afraid I have forgot some.
I can obviously visually compare using SQL Management Studio, which is what I did, but it is time-consuming and error-prone.
So, before plugging the plug off the existing server in due time, I would prefer to have a more "scientific" way to confirm that everything is in there…

I've looked for software doing this kind of comparison and have found only schema or data compare tools.
I guess I could compare the system tables like sys.sql_logins and msdb.dbo.sysjobs, etc, but I don't know them all.

In short, is there a way to quickly compare the instance-level objects of two servers, and if possible, their contents as well rather than just by name?

Thank you.

Best Answer

If you're only concerned about Agent Jobs and logins, I think the easiest approach is to leverage a linked server and querying the relative tables. I would do this by first creating a linked server on the old instance that points at the new instance. Once I had that, I could run the following queries:

Logins

SELECT * FROM [SQL2005].[master].[sys].[server_principals]
WHERE name NOT IN (SELECT name FROM [SQL2008].[master].[sys].[server_principals])

Agent Jobs

SELECT * FROM [SQL2005].[msdb].[dbo].[sysjobs]
WHERE name NOT IN (SELECT name FROM [SQL2008].[msdb].[dbo].[sysjobs])

There might be some inconsistencies with some server specific accounts/logins, but these queries should show you everything that exists in the old server that doesn't exist in the new server. You should be able to extend this logic to other facets as long as you had system tables to query for it.

As for objects that need to be synchronized, I think the best approach is to review your instance for the objects that should be migrated. Typically this would only be logins and Agent Jobs, but could also include linked servers (sys.servers), end points (sys.endpoints), and other objects.

Related Solutions

Sql-server – How to shrink all files quickly for all databases

When you do "Tasks -> Shrink" from the GUI it actually issues a DBCC SHRINKDATABASE command behind the scenes. Try it. When the dialog box comes up, don't click the "OK" button. Instead, click the "Script" button. You'll see the command in a query window. Combine that with a query on sys.databases (leave out master and msdb), and you can make a script to shrink all of the databases.

For example (taken from jcolebrand's comment):

SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4;

Copy the output of that query and run it to shrink all your files.

Sql-server – How does performance of SQL Server CE and SQL Server Express compare

For your case and reasons to upgrade

merge replication is available in SQL Server CE 3.5

Stored procedures, views, triggers is not available in SQL Server CE 3.5

for a basic comparison between the 2 editions in your case working on a net book

1. SQL Server CE 3.5 can Runs as in-process with application SQL Server Express runs as a service

2. SQL CE supports maximum size of 4GB SQL Express can go up to 10GB

3. SQL CE Doesn't Support FILESTREAM SQL Express Supports FILESTREAM

Procedural T-SQL - Select Case, If, features is not supported in SQL CE Procedural T-SQL - Select Case, If, features is supported in SQL Express

5. Distributed transactions is not supported in SQL CE but supported in SQL express

Native XML, XQuery/XPath is not supported in SQL CE but supported in SQL express

7. Stored procedures, views, triggers is not supported in SQL CE but supported in SQL express

Role-based security is not supported in SQL CE but supported in SQL express

9. concurrent connections supported in SQL CE is limited to 100 but unlimited in sql express

this is a useful blog focused on sql CE

for the comparison between the 2 in your specific environment you can use these tools

SQL LOAD TEST SQL STRESS TEST TOOL

and check this post on SO about sql statment performance https://stackoverflow.com/questions/1555471/what-tools-are-available-to-test-sql-statement-performance

Best Answer

Related Solutions

Sql-server – How to shrink all files quickly for all databases

Sql-server – How does performance of SQL Server CE and SQL Server Express compare

Related Question