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.

backups, yeah yeah, we've done that before

So, it was the end of the week, and the beginning of the weekend. I was attempting to do one of the loneliest tasks a DBA does. Backups. I have done these tons of times, and been successful in my sleep. This is a task we all learn, and worry over, and mess up on occasion. Such a simple thing, but ever present.

My thoughts here today are to remind you to simply take your time, plan ahead, and execute successfully. That can be a generalized statement for most anything, but in this case, it holds particular interest to me for backups.

Like I said, it was the end of the week. I was not thinking straight, and wanted to get this simple task done, so I could continue with non-SQL life. I had to restore a series of backups to a test machine. I should have this scripted out somewhere, but didn't. In a pinch, I had to rely on my own grey matter to perform the task adequately. I stole 1 sample that I did have laying around, and attempted to apply it to all 12 different databases. I quickly copied and pasted the sample, and renamed the dbs, the filenames, the internal names. Some of the internal filenames are DB_1.mdf, while others are DB_data.mdf. So copy and paste failed me, and just produced a bunch of TSQL that looked like it should work, yet did not. So, I had to run a bunch of Restore FILELISTONLY commands to ensure that I had the right quantity of internal files, as well as names. This simply took time that I didn't have.

Back to the filenames. I did this quickly, without thinking. I ended up copying the wrong path. The restoration location ended up being c:\mssql\backups, not the typical c:\mssql\data. This wasnt noticed until I had done about 5 databases restored. So, when I was done, I went back down the list and restored them again. More time.

Along the way, I missed a couple of the 12 databases, and had to go back to square
one, and generate the TSQL to restore them, with the correct filenames, and paths.

So, in the end, a task that we all believe we can do in our sleep, and have done many times over in the waking hours, proved to be a thorn in my side. Not because of the nature of the task was difficult, but my hurried approach, and lack of planning caused me to spend much more time than necessary performing said task. Needless to say, I now have a script that contains all the dbs to restore. This, I have already used, to perform a restore of one of these dbs in another test environment. It has already saved me time.

Suggestion. Instead of reading updates on Facebook, or talking with coworkers, or any other task that seems more fun, I should take the time to script out these items that I take for granted, so that I'm ready in a pinch to perform the task quickly, efficiently and correctly.