Useful for SQL Server upgrade and migration, instance consistency check in the enterprise, et cetera.

Known assumptions:
1. pyodbc and proper SQL Server database driver setup and tested;
2. Windows Active Directory trusted connection to both instances. Login/password can also be used with very simple modification of the code;
3. Proper permission is set on both instances.

Known limitations:
1. It is based on object name comparison. In many cases, this is “good enough”;
2. It does not compare End Points or Server level triggers, although that should be added easily.

Command line usage:
scriptName -s sourceInstance -t targetInstance

[sourcecode language=”python”]
import pyodbc, argparse

def getAllRows(cn, sql):
cursor = cn.cursor()
cursor.execute(sql)
return cursor.fetchall()

def rowCompare(objectType, rowsSource, rowsTarget):
if rowsSource == rowsTarget:
print “%s(s) appeared to have been migrated.” % objectType
else:
print “%s(s) not migrated yet:” % objectType
for row in rowsSource:
if row not in rowsTarget:
print row[0]

parser = argparse.ArgumentParser(description=’SQL Server migration/upgrade comparison: databases’)
parser.add_argument(‘-s’, ‘–source-server’, help=’Source server/instance’, required=True, dest=’sInstance’)
parser.add_argument(‘-t’, ‘–target-server’, help=’Target server/instance’, required=True, dest=’dInstance’)

argList = parser.parse_args()

try:
cnSource = pyodbc.connect(“DRIVER={SQL Server};SERVER=%s;DATABASE=master;Trusted_Connection=yes” % argList.sInstance)
cnTarget = pyodbc.connect(“DRIVER={SQL Server};SERVER=%s;DATABASE=master;Trusted_Connection=yes” % argList.dInstance)
except:
print “Couldn’t connect to %s. It is down or you might have had a typo.”

“””1. Compare if all databases on the source server are present at the target server”””
print “\n”
sql = “select name from master.sys.databases order by name”
rowsSource = getAllRows(cnSource, sql)
rowsTarget = getAllRows(cnTarget, sql)
rowCompare(‘Database’, rowsSource, rowsTarget)

“””2. Compare if all logins on the source server are present at the target server”””
print “\n”
sql = “select name from master.dbo.syslogins where name not like ‘##%’ order by name”
rowsSource = getAllRows(cnSource, sql)
rowsTarget = getAllRows(cnTarget, sql)
rowCompare(‘Login’, rowsSource, rowsTarget)

“””3. Compare if all linked servers on the source server are present at the target server”””
print “\n”
sql = “select name from master.sys.servers where server_id > 0 order by name”
rowsSource = getAllRows(cnSource, sql)
rowsTarget = getAllRows(cnTarget, sql)
rowCompare(‘Linked server’, rowsSource, rowsTarget)

“””4. Compare if all jobs on the source server are present at the target server”””
print “\n”
sql = “select name from msdb.dbo.sysjobs order by name”
rowsSource = getAllRows(cnSource, sql)
rowsTarget = getAllRows(cnTarget, sql)
rowCompare(‘Job’, rowsSource, rowsTarget)

“””5. Compare if all credentials on the source server are present at the target server”””
print “\n”
sql = “select name from master.sys.credentials order by name”
rowsSource = getAllRows(cnSource, sql)
rowsTarget = getAllRows(cnTarget, sql)
rowCompare(‘Credential’, rowsSource, rowsTarget)

“””6. Compare if all system stored procedures inside master that start with sp_ on the source server are present at the target server”””
print “\n”
sql = “select name from master.sys.all_objects where type = ‘p’ and name like ‘sp_%’ and is_ms_shipped = 0 order by name”
rowsSource = getAllRows(cnSource, sql)
rowsTarget = getAllRows(cnTarget, sql)
rowCompare(‘System stored procedure’, rowsSource, rowsTarget)
[/sourcecode]