Voice of the DBA

Tag: sql server

  • If You Need To Fix Database Filename Extensions

    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.

  • Full-Text Search – Thesaurus

    I would hope that most of us have used a thesaurus at some point in our careers. These allow us to substitute words for one another, providing for richer and more interesting communication.

    Full-text search in SQL Server includes a thesaurus that you can customize for your searches. As with the thesaurus some of us use when writing, this features allows the search engine to substitute one word for another in searches.

    You actually have to customize it. Here’s the default thesaurus for SQL Server 2008, which is stored in this location:

    <SQL_Server_data_files_path>\MSSQL11.MSSQLSERVER\MSSQL\FTDATA\

    If you look in this folder, you see a lot of XML files. These are the thesaurus files and they are named as tsxxx.xml, where xxx is the three letter language code. For English, the thesaurus is tseng.xml.

    ftsthesaurus1

    If I open up the English file, you can see there’s not much there in terms of entries.

    ftsthesaurus2

    This looks like the file that came with SQL Server 2005, if not SQL Server 2000. Note also that everything is commented out, and you need to remove these comment lines if you want to edit this file.

    The configuration isn’t that complex, but let’s look at a simple example. I’ll set up a small table and create a full text index on it.

    CREATE TABLE FTSTemp
( id INT
, Notes varchar(8000)
CONSTRAINT pk_ftstemp PRIMARY KEY (id)
)
;
GO
INSERT ftstemp SELECT 1, 'The quick brown fox jumped over the lazy dog'
INSERT ftstemp SELECT 2, 'I run WinXP.'

    I’m going to edit my thesaurus file to include these entries:

    <expansion>

        <sub>XP</sub>

        <sub>WinXP</sub>

    </expansion>

    And also

    <expansion>

        <sub>leaped</sub>

        <sub>jumped</sub>

    </expansion>

    I am only using expansion sets here. There are also replacement sets, but that’s for another post. In this case, when I search for any of the terms above, elements matching any other terms will be returned.

    NOTE: The US English file is tsenu.xml. The UK English file is tseng.xml.

    I’ll now create a full text index on this table, on the Notes column.

    I can issue this search, which I expect to work:

    SELECT 
  id
, notes
 FROM ftstemp
 WHERE CONTAINS(notes, 'quick')

    That returns the data I expect.

    Now to check the expansion set. To do that, I’ll need to use a FREETEXT query. Once I do this, I get results from both of my entries.

    ftsthesaurus4

    Note that if you edit the thesaurus, in order for your changes to show up in queries, you need to reload the Thesaurus file with this:

    EXEC sys.sp_fulltext_load_thesaurus_file 1033;

    The 1033 is for English. This is the LCID, which varies for each language.

    If you want to broaden your searches, include acronyms, etc, then the thesaurus is a good way to do this. Beware, however, that your entries will apply to all searches on the instance, so if you have disparate applications on the same instance, you might encounter some strange results.

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