Moving the SQL 2005 System Databases

A typical installation of Microsoft SQL 2005 ends up with system databases buried five levels deep under the C:\Program Files directory. Most DBAs prefer their data, including system databases, reside on a different drive than the executables. But moving the system databases, including master and the new hidden mssqlsystemresource database, is non-trivial, even scary. After doing this several times manually, I wrote a script to perform this task.

System Databases

The system databases, according to Microsoft (System Databases) include:
Master - Holds the description of other databases, logins, and master-only system tables.
Model - The snapshot of new databases that are created.
Msdb - Contains code and data for the SQL Server Agent and SQL Server Management Studio.
Tempdb - Where #tables and ##tables get created, and internal sorting space is written.
Mssqlresourcedb - Internal hidden read-only database containing system objects Other databases, such as distribution, may also be included depending upon your configuration. In this article, I refer to only the above five databases. Why would you want to move these databases? A few reasons. Personally, I don't like the idea of important database changes happening somewhere in the depths of the Program Files directory. I'd rather know that all my databases are on an easy to find directory structure. More importantly, database storage is usually in a RAID array or SAN separate from the C: boot disk. Why not have the protection and performance those disks afford on the critical system data? If you lose your boot disk, you can reinstall the OS and SQL and after pointing to the right locations, you can be back up quickly with no data loss.

A Special Database Requires Special Care

The newest member of the system databases is mssqlsystemresource. Its hard to tell exactly what it does, but Books Online (Resource Database) indicates that "SQL Server system objects, such as sys.objects, are physically persisted in the Resource database." We are also told there that the only supported operation on this database is to move it, so we're not breaking any rules! Another tidbit: this database can't be backed up. It must be copied like an EXE file. A very important fact that is easy to miss is that this database MUST appear in the same location as the master database. I didn't notice this restriction at first, and it caused some strange errors when we upgraded to SQL 2005 SP1. Turns out the service pack upgraded an older unused copy of the resource database, but not the copy in the same location as master. To play it safe, don't keep unused copies of this database in the default location, but rather keep backup copies in clearly marked backup directory. In order to move this database, we'll need special startup and trace flags and SQL DDL commands. And, of course, we need to ensure the path for the master database (specified in the service startup parameters in the registry) and the location of the mssqlsystemresource.mdf and .ldf files are the same.

Steps to Moving

The steps to moving the databases are outlined well in Books Online (Moving System Databases). To move the databases, we'll need to do the following:
Use ALTER DATABASE MODIFY FILE to tell the master database where most of the system databases will reside.

• 
  Update the service startup parameters in the registry so that the service finds the master database and log.
  Stop the service to unlock the files.
• 
  Move the files to the new location.
• 
  Start the service with the -f (minimal configuration) flag and the -T3608 trace flag to prevent automatic recovery.
• 
  Use ALTER DATABASE MODIFY FILE to record the new location of the mssqlsystemresource database.
• 
  Move the mssqlsystemresource file.
• 
  Set mssqlsystemresource database to read only.
• 
  Stop and Start the SQL Server Service in normal mode.

Examining The Code

Download the code

The .CMD script MUST be run on the SQL Server machine itself, and the accompanying .SQL must live in the same directory as the .CMD script. The script requires two parameters - the instance name and the full path where the data files will be moved (leave off the trailing backslash). If using the default instance, provide MSSQLSERVER for the instance name. After initial setup and parameter checking, SQLCMD is called with the -Q parameter to accomplish step a. for model, msdb, and tempdb. Then SQLCMD is called again using the -i parameter to run the sql script. Interestingly, though, it is called from the FOR command. This powerful but arcane command can do a lot in a batch script, but it is often far from obvious just what its doing. In this case, FOR is used as means to communicate from SQL back to the calling script. The script will return the old path of the system files, information we will need to move the files in a later step. But how to get the variable from SQL to the calling batch script? This was enough of a challenge that I considered writing it all as a SQL script using SQLCMD's new parameters, but again, going from SQL @variables to SQLCMD $(variables) proved difficult. I considered writing the script in Perl, but didn't want to require everyone to download the Perl runtime. I finally settled on the ugly, but working, FOR /F command to execute SQLCMD with an input file and return its result as an environment variable that can be used later in the script. With the /F switch, FOR will run the single-quoted command specified in the parenthesis, then set the batch variable (%%s) to the output of the command. Specifying -h-1 on the SQLCMD indicates no column headers and dash divider lines on output. We also use the "delims=;" option to have the batch variable read up to the first semicolon it sees, since the default action of delimiting on spaces would choke in the presence of "Program Files" and "Microsoft SQL Server". The .SQL Script first uses xp_regread procedure to get the directory path (MSSQL.n) associated with an instance name. Notice the use of the $(InstName) variable. SQLCMD, unlike OSQL, can easily read environment variables. The value of this key tells us where to search in the registry for the SQL Server service parameters. Plugging in the values, we read the SQLArg0, SQLArg1, and SQLArg2 values that contain the -d, -l, and -e parameters. There is no set order to these parameters, so we need to iterate through them all. We obtain the old path for sending back to the calling script, replace it with the new path, and call the xp_regwrite procedure to write it out. The script can then proceed in a straightforward way to accomplish steps c-i.

Caveats

The code assumes that all of the system databases are in the same location. If you've already moved TEMPDB, for example, you might want to comment that portion of the code from the .CMD script. The assumption is also made that the -d and -l parameters occur within the first three parameters of the service startup. Its not likely, but other options such as trace flags could be specified before these parameters and break this part of the code. Before running this code, always backup your data files. Get a screen print of the Advanced tab in the SQL Server Configuration Manager, so that you can restore the registry values manually if necessary. Run it on a test server first if at all possible. If you don't have a test server, consider installing the server on your workstation to test it out.

Conclusion

Moving the system databases offers some real advantages but needs to be done carefully and consistently. The script presented here can help to accomplish this, particularly when run soon after installation of a new SQL instance

Working Your SOX Off

Introduction
It used to be a mainframe joke. "What is a SOC4?" people would ask when the error came up. The answer: "To put on your foot, of course". Now, however, SOX is a word that strikes terror in the hearts of IT people everywhere.
In 2002, the United States government passed a law called the Sarbanes-Oxley Act. Nicknamed SOX (or Sarbox by some), this act has confused many a manager and implementation guru because of its broad, open-to-interpretation language. Having just gone through a major implementation at my workplace, I understand the worry and fear that accompanies this law. I hope my own experiences can shed a little light on this major, and often misunderstood, project.
If you are in the United States, or do business in the United States, and your company is over a certain size, you have to abide by a lot of regulatory laws - privacy, financial and documentation. SOX is a combination of financial and documentation. It consists of several different Titles, each of which establish just what is legal and illegal under the new act, but the gist of what applies to the IT world is the word "accountability".
Accountability in this context means documentation of established procedures, automation of reporting / tasks wherever possible and the ability to prove to an independent auditor that documented procedures are followed all the time. This includes financial reporting. Contrary to popular belief (and I was surprised by this one too), not all financial reporting have to be completely automated. There are instances where you can have financials in a spreadsheet that the accountants manipulate and change. The trick is to have this information documented. Always remember it's about "What, Why, Who and How".
The best way to start with a SOX implementation is to do a discovery phase। Find out what processes you currently have documented. Then list the ones you don't have documented. Processes include --but are not limited to-- security, access, permissions, reports, responsibilities and documentation location. I'll go through each of these one at a time.

Security
This goes hand in hand with access and permissions. You need to list every single possible database, application, file share and server that you have control over. Then you need a list of who has any permissions or access to these items. This includes listing out Windows Groups (not the individual members), Windows Users, SQL Logins, or anyone who has local admin access to a Server.
Now there are certain circumstances where you won't have complete control over your security. For instance, if you're a DBA who does soft Admin tasks on a server where another team controls the hardware, you might not be able to keep them from adding groups or users to the Server's OS. Don't sweat it. Just find out the name of the team in question and include a notation in your documentation that says what you are responsible for and that Team X also has the ability to add logins to Server ABC.
Access & Permissions
Here we go with the hand in hand stuff I mentioned earlier. On the security list you just created, you need to include the access levels and permissions of each individual user / group. Since access and permissions aren't necessarily the same thing, I've listed them both.
Remember how Santa Claus checks his list twice? Creating the security / access / permissions list is just the beginning. Once you have it, you have to make a commitment (and document that commitment) to check the list on a regular basis, matching it against the reality of what security is actually implemented. You also have to fix problems as you find them and have a documented process for what to do when for the fixes.
Lastly, you have to have a documented process for granting permissions / access when someone joins the team or company and removing those permissions when someone leaves the team or company. And most important, you have to prove that you follow that procedures!
Here's a real life example। In my workplace, I wrote up the policy dictating who got what permissions in what SQL environments (and on what file shares) while a co-worker created a SQL job that appends the current S/A/P settings to an Excel sheet on a weekly basis. We double-check that sheet each Monday to see who has what and if anything violates the written policy. We then investigate how that permission got there (including the who) and then remove the permission. Sometimes, we even remove the permissions of the person who granted that access if it turns out that grantor is doing things outside the scope of his/her job duties. Lastly, we document those violations for later reference and tell the boss (so he doesn't get side-blinded when someone asks him about the violation).

Reports
This may sound silly, but SOX requires you to document your reports. The documentation for this should include the business rules used in generating the reports and, if necessary, an explanation of the math behind it. Also, if you have financial reports being generated by hand (data entry into a spreadsheet anyone?), you have to automate as much of the report as possible. Hand-keyed financials are no longer acceptable to the Feds unless the data in question is something that changes constantly, like an interest rate.
This is important to note. Not everything has to be automated when it comes to financial reporting. Just as much as possible. Then you have to document why you didn't automate something, what process is to be followed in the manual entry, who can make those manual entries and how those entries are to be made. And, again, you have to be able to prove to the auditor that someone isn't just making up numbers.
The general rule to this one is, start with the simple reports (working your way to the more complicated ones) and automate as many of them as you can। But make sure you write requirements on those reports as you work or an auditor may come along and make you redo the whole process.

Responsibilities
Make sure you document who is responsible for what area of SOX implementation and follow-up. For example: if you have an internal Help Desk who is responsible for setting up Windows Logins, group memberships and permissions, don't forget to include them in your documentation (and your project). When asked, you need to be able to tell an auditor that it was the Help Desk that gave that user Domain Admin permissions.
And the auditors will ask. They want to see if you actually know the SOX processes as this gives them a good idea if you're even following them. After all, if you don't know the processes, chances are you aren't following them because you can't answer their questions.
So make sure to integrate your SOX documentation with that of all affected teams, to get their input and to document everyone's roles and responsibilities for tracking SOX violations. Doing this before the auditors show up will make your life that much easier.
Documentation Location
Last but not least, you have to document your documentation location.
Say what??? Yep, as strange as it sounds, you have to put down in your SOX documentation where you plan to store (electronically & physically) all the stuff you just wrote up. You also have to publish those locations to all affected team members. Auditors do actually ask you where your documents are and want you to prove you can get to them. And if you forget to tell one person about it, you get dinged severely in the audit.
Implementation Advice
Not all companies have to implement SOX. If you're a small business under a certain number of employees (and I don't know the number off the top of my head), you are exempt from SOX. It's always good to implement anyway under the notion that you'll improve your company security. And, if your company grows, you won't have to do it later on when life gets more complicated. Finally, when it comes to actually doing the project, there are a few "shortcuts" that might be available to you.
First, get your lists together of what might be affected. Meet with the entire team to weed out anything that doesn't really need to be done or add things that have been forgotten. Get a Project Manager to write up a project timeline and assign priorities to the different tasks. This is the most important thing you can do. If you get a surprise visit from the auditors and can prove that you're at least working on the issue (show them the project plan), they probably won't ding you as badly as if you didn't have anything done and had no proof you were in the middle of implementation.
Second, if you're in a large company, find out if other departments have done their SOX implementations yet. If so, they might be able to give you advice or a sample list of what they did. This could save you some time if you know what your own auditors are looking for.
Third, call the auditors! Beat them to the punch. Tell them you're in the middle of an implementation and you're stuck. You'd like advice on what you should be checking and, if your boss doesn't mind, can they come in and give you a pseudo-audit so you have an idea of what they are looking for. Believe it or not, the auditors will be more than happy to help you out. After all, it looks good on their records if you come out as a compliance All-Star.
Fourth, while it might be better to over-document than under-document, you still have to make sure that everyone on your team understands the documents. If they don't, you've just wasted an awful lot of time for nothing.
Last, there are rumors that SOX is due to expire soon, but skimming the text of the actual law, I don't see any expiration date listed। And since SOX compliance can actually help your business more than hurt it, I would recommend going through the processes anyway. Certainly you could clear away a lot of deadwood processes simply by finding out what would need to be documented for SOX. After all, why write documentation on a legacy report with no data and that no one ever looks at? Just delete the report and go on to the next thing.

Conclusion
SOX isn't actually as hard as a lot of people make it out to be. The main problem is that it is time and resource intensive, which means lots of big dollar signs if you have to pay people overtime or hire temps to help you out during the implementation. It's also very easy to let it slide when you have more pressing projects to complete. But if you document as you work on those other projects, you've just saved yourself time later down the road.