Tag: administration

In a recent post I showed how the file extension for a database doesn’t matter. It can be confusing, however, and you might wish to “fix” the filenames to conform to the proper extension. How can you do this?

Well, to change a file name, or location, you need to take the database offline. This is noted in the Books Online Move Database procedure. Why? Well, the files need to be physically changed in the file system (either a rename or copy), so there is downtime here. Locations are one thing, but what about renames?

The rename is simpler, and if you script this, downtime is minimal. The procedure is the same as listed in BOL:

• set the database offline
• rename the file
• run the ALTER DATABASE command
• set the database online

This is pretty simple. We want to run this code:

ALTER DATABASE [NameTest2] SET OFFLINE
GO
ALTER DATABASE [NameTest2]
 MODIFY FILE ( NAME = NameTest2
             , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.mdf' )
GO
ALTER DATABASE [NameTest2] SET ONLINE
GO

However that code misses item #2 from above. I can manually perform that step, which is pretty easy, or I can script it if I allow xp_cmdshell changes. I know this is a security risk, but I can enable it and disable it all in the script:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO 
ALTER DATABASE [NameTest2] SET OFFLINE
GO
EXEC xp_cmdshell 'rename C:\"Program Files"\"Microsoft SQL Server"\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.ldf nametest2.mdf'
GO
;
ALTER DATABASE [NameTest2]
 MODIFY FILE ( NAME = NameTest2
             , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.mdf' )
GO
ALTER DATABASE [NameTest2] SET ONLINE
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO 

Note in here that I need some quotes in the RENAME command inside the shell so that Windows handles the spaces correctly in the path.

Do you know the basics of how to create a database? Hopefully you do and can do so without the GUI. However do you know the extensions are for database files? As of SQL Server 2012, these are the extensions:

• Main data file – .mdf
• Secondary data files – .ndf
• Transaction Log files – .ldf
• Full backup files – .bak
• Differential backup files – .dif
• Transaction Log backup files – .trn

However these are merely suggestions, and dictated by convention. In fact, in the Files and Filegroup Architecture page, BOL says that the “recommended” extensions are those I’ve listed for different types of files. For backups, these aren’t documented since you can actually include different types of backups in the same file (Don’t do this).

Here’s a quick test:

CREATE DATABASE [NameTest1] ON  PRIMARY 
( NAME = N'NameTest1'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest.mdf' 
, SIZE = 2 )
 LOG ON 
( NAME = N'NameTest1_log'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest_log.mdf' 
, SIZE = 1 )
GO

If you notice, I’ve created a database with one data file and one log file, both using the extentions “.mdf”. This works fine and the database is usable.

I can do the same thing with ldf.

CREATE DATABASE [NameTest2] ON  PRIMARY 
( NAME = N'NameTest2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.ldf' 
, SIZE = 2 )
,
( NAME = N'NameTest2_Data2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2_data.ldf' 
, SIZE = 2 )
 LOG ON 
( NAME = N'NameTest2_log'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2_log.ldf' 
, SIZE = 1 )
GO

In this example I even added a secondary data file. If I check the physical file locations, I see the files I created.

Note that Explorer sees these as the type of file based on the extension it has associated with that filename, but that doesn’t affect how SQL Server uses the files. If I look in the properties for the database, I see the files listed as expected.

These don’t affect the operation of SQL Server or the database at all, however they can be confusing for DBAs. I recommend that you stick with the customary extensions for SQL Server files.

Someone asked me on Twitter recently if I ran full regression tests before applying Cumulative Updates (CUs). I decided it wasn’t worth discussing in 140 character chunks, so I decided to jot a few notes down. I’ll also expand this to encompass Service Packs since these are almost CU rollups delivered yearly.

The short answer: it depends.

I hate giving that answer, but it’s honestly the correct one. There isn’t a single way to answer this question without examining the situation and environment in which I’m working.

Do I Have Regression Tests?

You’d be surprised how many apps I’ve worked on, whether third party or developed internally, where we didn’t have a set of comprehensive regression tests. If I was lucky, we had a good set of tests for each release, but more often than not I’ve found developers and testers focusing on specific features and ignoring the overall application.

If I don’t have full tests, then no, I don’t run them. I can’t.

However what I can do is schedule someone to look at the application on a test system after the CU/SP has been applied. It isn’t comprehensive, and it doesn’t necessarily prove the patch hasn’t broken anything, but it does get the “business” to sign off on the patch.

Timing and Resources

If I have full regression tests, and I have had them for some applications, the timing of the patch comes into play. CUs are released every other month, which is a fairly rapid pace. It’s one reason I don’t recommend applying them IF you don’t have a specific issue addressed by the CU.

As a DBA, I’m paid to ensure that the databases are running at an acceptable level and I can recover them (quickly) if some disaster befalls the system. However I’m also paid to be strategic and improve my employer’s ability to conduct their business. CUs interrupt my schedule, that of testers, and distract from getting other work done. Therefore, I avoid CUs if I don’t need to apply them for a specific reason.

If I do need to address an issue, I would perform regression tests on the specific system(s) that has the issue and if it passes the tests, only upgrade  those system(s). I have found that testing every system isn’t worth the time it takes to keep all systems at the same level. I always have exceptions anyway due to vendor support issues.

For Service Packs I would always schedule some testing and apply them within a couple months of release. If possible I’d get them the month they are released, but they aren’t usually a high priority, more like medium high.

Severity

There are exceptions to every process and I would agree I make exceptions. Most CUs are patching bugs, not security issues. Therefore the severity for their application, even if they address an issue I’m having, is likely middle of the road.

If a high severity patch is released, I would schedule testing of some sort. Full regression testing is preferred, but in the case of some apps it’s as little as

• apply the patch to a test system
• reboot it
• see if the application comes up and someone can log in.

That’s not great, but it’s all I’ve had at times. Note that I’d still get sign off (see below).

Automation

The key to this process is always automation. Getting regression tests set up in a harness of some sort is time consuming, and likely would take a year in many environments to cover all the instances. Even small environments are full of a variety of instances, and I’d ensure that I can successfully patch the development environments as well as production.

Working with developers, testers, and the business to write checks takes time. Building some sort of harness (SQLCMD, Powershell, etc.) is software development, and it’s something you iterate through to ensure you can check the results, not just execute the tests. I wished I’d had something like SQL Test when I was doing this to at least cover the T-SQL side of things.

However you do it, whether with a formal tool or with cobbled together checks, stick them in source control and ensure they can be executed as a group.

Sign Off

That’s how I’ve approached patches, and it’s worked well for me. I’m conservative, and don’t like to “fix” things that aren’t broken. I avoid patches I don’t need. Others feel differently and some of it depends if you are performing lots of development on applications. In that case, you might apply patches so developers don’t run into (and code around) bugs.

The last thing I’ll mention is that even with regression tests, I’d always ask someone from the client side to work on the test system. I can’t always enforce that or guarantee it has happened, but I do ensure they sign off on the patch, having tested it or not, before I apply it.

And, of course, I always make sure I have a backup of the system, off the local disks, before I apply the CU.