Friday, July 29, 2016

Protecting Key Tables

In most production systems you've got a half to a couple dozen tables that contain configuration or system-wide critical information. Things like State abbreviation tables, printer routing tables, user rights tables, hierarchies, workflow parameters, flag mapping key-value pair descriptions, et cetera. Generally you want to lock these tables down pretty tightly so that you don't allow somebody to accidentally update or delete a row, without some kind of more general awareness of what is happening. The way to do this is with a Database Role. At first this concept may seem confusing so I'll try to tease out the particulars in a way that slowly reveals its sense.

You already are familiar with the Instance level Server Roles, things like sysadmin and public. When you click on the Security folder off of the instance level, and then the folder under that called Server Roles, you can see all of the Instance level roles.

You also have seen the Database level Roles, for example after you add a new user login and then set up their access to the databases. You are familiar with db_datareader, db_datawriter, db_ddladmin, db_owner, et cetera. If you want to see these roles from the top down (rather than from the Logins mapping level) then click on a database, expand the Security folder under that database, and then click on the Roles folder, and then double-click the Database Roles folder.

This is where you add a Role to protect the tables within a database. Right click and select New database Role.  Give it a name (I like to use the name KeyTablesReadOnly). In a moment we'll revisit the remainder of this setup, but first a side digression. The point I'm making here though is that data protection roles belong in the database level of Roles, not in the Instance level.

How do you identify what tables to protect? A good clue on a fairly mature production database is to sort the tables in the Object Explorer Details pane by the column titled Data Space Used. Those that are small in size (and non-zero) likely contain the key configuration and mapping information. If you're using something like "Toad" you can select and paste these table names into a spreadsheet (which comes in handy shortly).

You probably will need to confirm these table names with the Development Manger so send that spreadsheet off for approval before proceeding. When you get confirmation, go back and double-click on that KeyTablesReadOnly database Role. The properties pane has two areas: "General" is for assigning the role to specific users, and "Securables" is for setting the protection level on objects within the database. On the tables that you want to protect, check the Deny column next to Delete, Insert, and Update, and then go ahead and add all the users (or you may leave a couple of users with full rights if they specifically are responsible for maintaining these tables).

If somebody needs to do an update, then the fast way to adjust them is to go to the Instance security folder, click on Logins, then their User mapping, highlight the database affected, and then remove them from the KeyTablesReadOnly role.

Wednesday, July 6, 2016

Delta Blues

I don't know what your DBA life is like, but at my place at any given time only one gigantic database garners the majority of the development activity. I'm not directly involved in the development -- they don't fetch me into the design meetings nor copy me on design specs -- but I would still like to be aware of what changes are transpiring in the database (if anything to have some idea where to look when something suddenly goes awry). The quiet way to do this is to "delta" the database, comparing the current version to the database from say a week ago. To pull this off neatly (without having another copy of the gigantic database sitting around glomping up disk space) you need a couple of tricks and a good comparison tool.

First you should make a baseline "empty" copy of your production database -- a full copy without any data in the tables. From SSMS right click on your database, go to tasks, and then script database. When you get to the wizard page that specifies an output file location, after you type in a savefile name click on the Advanced button. Scroll down to where it has Create USE Statement and uncheck it. Also make sure the Script Triggers box is checked.

Now that you've got the whole thing scripted, create a new database with rather small file allocations (yeah you need to mimic the filegroup structures too) and while attached to your new database open up that sql query you just saved. Double check to assure you're attached to your new database and run your script. Boom, a new empty baseline database. Note though sometimes the sequence SSMS originally creates the script violates some dependencies between objects: you may have to run the same script over again so it actually creates everything.

A week of work goes by and now it's time to run a comparison. If you don't already have a commercial product then you will need to download a database comparison tool; several exist but I recommend Open dBdiff. It's especially nice since it comes with the source code to allow you to easily make small tweaks to change colors, button locations, and whatnot.

Once you run the comparison you will need to examine the change in each of the elements that differ. By clicking the checkbox in front of those that are significant, the tool builds the script to "true up" the versions. When you finish your review click on the Synchronized Script tab, and then the saveAs button to store the delta script to a file folder.

The next week attach to your previous-version database, run the script that was the delta from last week, and then open your comparison tool and delta your production database again. Now you have the changes for this week. If you do this every week, then after a while you have a whole history of changes. Working from the original fully scripted baseline database you can execute successive deltas to bring the empty database up to any particular point in time (well, within a week or so).