Voice of the DBA

Tag: administration

  • Does the SQL Server Database Filename Matter?

    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.

    cd_a

    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.

    cd_b

    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.

  • Regression Testing before CUs and Service Packs

    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.

  • A Patch Disaster

    This isn't the dialog box you want to see on a server.
    This isn’t the dialog box you want to see on a server.

    Can you imagine sending the wrong patch to the wrong machines? That’s what happened with an Australian bank. Am OS patch was sent to many more machines than it should have. The patch was designed for desktops, but managed to get deployed on servers and resulted in some sort of software corruption.

    SQL Server users are fortunate that we rarely have security patches for our platform. There are cumulative updates every other month, but the majority of them aren’t aimed at SQL Server, and many of them may not even be required for the host Windows OS. Your organization’s policy may require the OS patches, and if they do, you should be aware of when and how they are being deployed.

    Even if patches are not supposed to be deployed to your servers, you should plan on being aware of the deployment date. You never know when an administrator will make a mistake and deploy a patch to your database server and necessitate a restore. I would recommend that you double check your backups and ensure restore scripts are handy on any patch day.

    Our computer infrastructure becomes more complex all the time. At the same time, many of us become more specialized, working in a more focused area, and counting on others to manage the parts of our system we do not have time to worry about. People will make mistakes, and we should ensure we can recover our systems from those mistakes.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • sp_Blitz, v16, on SQLServerCentral

    Brent Ozar Unlimited released v16 of this sp_blitz script, which is designed to run on a SQL Server instance and give you a quick, comprehensive report of the state of the instnace. It’s a great script, and if you have the chance to see Brent present on the script, go see it. It’s a great show and a good explanation of why you might want this type of script.

    I haven’t had to manage the SQLServerCentral instances, but I decided to run this and see what jumped out at me. I started a series at SQLServerCentral where you can follow along with my results. The first article went up today.

    • Security
    • Reliability
    • Performance
    • Query Plans
    • Backup
    • Global Trace Flag
    • High Availability
    • Informational
    • Non-Default Server Config

    You should think about auditing your servers periodically with something like the sp_Blitz script, which will allow you to ensure your servers are running at peak performance.

    Administering your instances is an ongoing job. It’s not a time intensive daily job, but it’s something you should be doing periodically on all your instances, with monitoring setup on your servers to let you know when things might be causing problems.