Security Comparison

We have a hot system, and a warm system. Replication keeps the data in sync. But at some point in the not too distant past, we had to do some work to create this warm system. We commission hardware to be purchased, configured and setup for us to create the databases needed. Besides the databases, we need jobs, dts packages, logins, and so on. When we create these other systems, our offline systems, we need to match it up to the existing hot system. Copying databases and restoring them is fairly easy. Database users are restored, but may be orphaned from logins. Jobs may not exist, dts' may not exist. All these pieces need to exist, and match the hot system.

One by one, we create the bits and pieces needed, then do comparisons to ensure all pieces are in their appropriate locations. We work closely with QA to compare our systems. Tasks are created to cover each type of comparison needed.

For DTS packages, we will extract each dts package from the two systems, dump them to file, and bring them down to another system. Once in another non productino system, we can restore them and compare them. I use a Red-Gate tool to compare them, and do a lot of manual checking of the comparison. Ensuring that each DTS has the appropriate paths, connections, and so on.

For Jobs, I use a handy SQL script that grabs several important fields, and performs a checksum on those combined values. This checksum is compared to the matching job on the other side. This will show off immediate discrepancies. Another way to compare these is to script them all out, from each system, and do a file based comparison. Using Beyond compare, I can compare the entire folders together, and look at differences.

For Security, I have struggled with db users permissions comparisons and login comparisons. I have a handy script that can extract both these into a lot of data columns. Manually comparing these can be cumbersome and possibly error prone. I typically dump out the data to excel, to store for historical purposes, and do manually comparisons. But yesterday and today, I finally created some tables and scripts that would let me more easily compare the data via SQL. I import the excel document into tables, then one by one database, dump side 1 into a table, and side 2 into a table. These are then compared, and any discrepancies are revealed. I was happy to finally take the time to create this as it came in handy the next couple comparisons I needed to perform.

I have been doing a write up of the details of the 'how to' steps for these comparisons. I hope to publish this at some point. The document has come in handy for myself each time i have had to perform these comparisons, as well as others I work with. These comparisons occur twice annually, as we perform db switches from hot to warm, and when we introduce new systems to the environment. Having these things documented, how we performed them in the past, has been extremely helpful.

I just wanted to write these thoughts down and share them with you.