Today we have a guest editorial from Hilda E.

Sometimes part of a DBA career is to inherit database administration responsibilities from other DBAs that have moved on to other bigger and better opportunities. Other times a DBA gets stuck with that forgotten database that keeps that important information but that for one reason or another it was never managed correctly. Either way, when faced with such new “opportunities” a DBA needs to evaluate the database configuration and settings to appropriately bring the database to current standards.

During my IT career I have been a PC tech support person, a programmer, a system analyst, and a database administrator. I consider myself a newbie in the database administration field and although I feel like I can manage a reasonably sized database shop, I’m constantly facing problems and learning something new about SQL Server every day. I recently got my first full time job opportunity as a database administrator, and one of my first assignments was to review our database server configuration as well as the backup and restore procedures. Consistency has always been one of my mottos, and in this case I was sure to follow it not only for the sake of doing things right, but also because it’s easier on my mind to remember what steps I’ve done and how systems are configured.

I went into this journey and checked my database servers. I found that most of the database configurations were up to the standards I was accustomed to; however, the database and transaction files were residing in different hard drives locations. The previous DBA didn’t set up the database and transaction files with any particular standard directory structure. I discussed my finding with my supervisor and suggested we move the database and transaction files to be in a consistent location. In addition to consistency, backup to our remote location would be much easier to manage.

After getting management approval, scheduling time after hours to perform this task and notifying the right user groups, I felt very confident that the plan I had laid out was going to work properly.

The files were scattered around on the server, with some on the C: drive in multiple locations and others either on a second or third drive. I left the system database and transaction files in their default locations, and one by one, I reset the databases to single mode, detached them and moved the files. I moved the database files to a second drive and the transaction files to a third drive. Then I attached all the databases back again. I ran a few scripts on each database to make sure I could access the data, run GUI and web applications and all seemed to be working fine. After about three hours, nothing wasgiving me a headache. So far, so good. 

I got everything tested except for one small part of our web program.  Our intranet had the functionality to connect to our SQL Server and run precompiled programs through stored procedures. I knew of such programs but I totally forgot to test them.  SQL Server has the functionality to run CLR assemblies through stored procedures (see this link to get more information on that: How to Create and Run a CLR SQL Server Stored Procedure), which comes in handy if you want to manage your code outside stored procedure. 

Having failed to test the CLR SQL Server Stored Procedures, I went home that night only to come back the next morning with tickets flowing through our help desk.  It took us about an hour to figure out the problem.  When a restore or attach process is done for a database, some properties for the database get reset.  One of those settings is the database TRUSTWORTHY Property.  By default this property is set to OFF for security reasons.  This property is used to indicate whether the instance of SQL Server trusts the database and the contents within it (see http://msdn.microsoft.com/en-us/library/ms187861(v=sql.105).aspx).  We required this property to be set on. An easy alter database statement was all it took to fix the problem.

ALTER DATABASE AdventureWorks2008R2 SET TRUSTWORTHY ON;

Once we set the database property TRUSTWORTHY to ON, we were able to run our website and access the stored procedures that contained the CLR assemblies. 

Some days while I’m checking the databases and the servers I feel too confident, but other days like this I feel overwhelmed and realized how powerful SQL Server is. I realize there is an extensive volume of knowledge that I have yet to acquire, either by reading about it every day or by experiencing moments like this one. Every day is a new day for a DBA, some days go easy, and others are hard and come without notice even if you think your plan it accordingly.