Voice of the DBA

Tag: syndicated

  • 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.

  • Full-Text Search – Word Breakers and Stemmers

    There are numerous components to the Full-Text Search (FTS) subsystem in SQL Server that help provide efficient, relative answers to queries. Full-text Search is a little complex, and as I’ve been working with the system in an effort to learn more about it, I decided to document how a few things work.

    Word Breakers and stemmers are two interesting parts of the FTS system. They deal with certain language specific operations that help searches work better. They are related as the two items are loaded together if you use third party word breakers and stemmers.

    I haven’t seen third party word breakers, but some work from other products. As an example, here’s a post to load the Greek FTS search word breaker and stemmer from Sharepoint server if you are on SQL Server 2008. It’s included in SQL Server 2012.

    Word Breakers

    Let’s start with word breakers, which do just what the term implies: they break words. It would seem to be obvious that spaces are the word boundaries, and they are in English, but not necessarily in all languages. There are also the issues of characters in Asian languages like Japanese and Chinese. You can’t count spaces as the word boundaries on those languages.

    Word breakers use the lexical rules of the language to determine word boundaries. Essentially they find what the words are, and then further action can be taken in building the FTS index or processing the query.

    The “words” that the word breaker spits out are seen a “tokens” to the FTS index, and each can then be processed by stemmers, stoplists, thesaurus, etc.

    Stemmers

    Stemmers are an interesting part of the full-text search system. They remind me of my high school Latin classes, where we had to conjugate words. A stemmer takes a word and generates inflectional forms, or conjugations. The example in Books Online, and an easy one to understand is “run”. There are various forms of "run” that we would want to consider as equivalent when performing a search. For example, you would want to consider:

    • ran
    • running
    • runs
    • runner (perhaps)

    The same could be said for “lay”. That would generate

    • lie
    • laying
    • lain
    • lays

    This is one of the big advantages over the LIKE predicate in that stemmers can match these forms of the word being searched for. The index would relate all of these to the core, base word.

    More

    The Books Online page for Word Breakers and Stemmers has technical information on checking what’s installed, some troubleshooting, language settings, and some drier documentation on what you can do with word breakers, but not a lot of explanatory detail.

    I used a little of the information in Books Online, and some from Pro Full-Text Search in SQL Server 2008. You can read more, but unfortunately I haven’t found a lot more documentation on the details of how things work.

    You would probably learn more if you write your own Word Breaker and Stemmer, and there is a sample in the Windows SDK to get you started, but that’s beyond what I want to do.

  • 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.