Author: way0utwest

Using tSQLt to Find Min/Max Times

I love tSQLt. It’s a good way to write tests that can determine if your code is actually working. Since I’m a fan of unit testing, I think using tests to verify your logic is great. What’s excellent with tSQLt is that I can verify a number of cases at once.

I ran across this post asking for help with a query. Given the sample data and results, I wrote this proc and test. In the test, my “Act” is calling a proc I wrote that executes the first post’s query.

CREATE OR ALTER PROCEDURE RunTimeTests
AS
BEGIN
    SELECT
        Taskid,
        MIN(StartTime),
        MAX(EndTime),
        DATEDIFF(MINUTE, MIN(StartTime), MAX(EndTime))
    FROM TimeTests
    GROUP BY Taskid;
END;
GO
EXEC tsqlt.NewTestClass @ClassName = N'tTimeTests'
GO
CREATE OR ALTER PROCEDURE [tTimeTests].[test calculation min max time from timetests]
AS
BEGIN
    -- assemble
    EXEC tsqlt.FakeTable @TableName = N'TimeTests', @SchemaName = N'dbo'

    INSERT into TimeTests
        VALUES 
        (1, '2017-02-23 09:48:47.413',NULL ),
        (1, '2017-02-23 09:50:47.413', '2017-02-23 10:59:47.413' ),
        (1, '2017-02-23 09:49:47.413',Null ),
        (2, '2017-02-23 10:40:47.413','2017-02-23 11:55:47.413' ),
        (2, '2017-02-23 10:39:47.413', NULL ),
        (2, '2017-02-23 10:11:47.413','2017-02-23 11:30:47.413')

    CREATE TABLE tTimeTests.Expected
    ( taskid INT, Mindtime DATETIME2(3), maxtime DATETIME2(3), Minutes int)

    INSERT tTimeTests.Expected
     VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71)
          , (2, '2017-02-23 10:39:47.413', '2017-02-23 11:55:47.413', 76)   

    SELECT *
     INTO tTimeTests.Actual
      FROM tTimeTests.Expected
      WHERE 1 = 0;
    -- act
    INSERT tTimeTests.Actual EXEC RunTimeTests;

    -- assert
    EXEC tsqlt.AssertEqualsTable
     @Expected = N'tTimeTests.Expected', @Actual = N'tTimeTests.Actual', @Message = N'Incorrect times'
    
END

When I run this, it easily verifies the answer that the data is incorrect from the poster.

If I change my expected results:

    INSERT tTimeTests.Expected
     VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71)
          , (2, '2017-02-23 10:11:47.413', '2017-02-23 11:55:47.413', 104)

and re-run the test, it succeeds.

Now, does this mean the developer wouldn’t make this mistake? After all, if you think you should be getting those results, you will struggle with the query.

It doesn’t help there. However, it does help if you modify this code later and start to have strange results. This also means that I can add in more rows to the data, even more cases, and determine if the procedure still works. If I’m trying to cover a dozen cases, it’s much easier to re-run a tSQLt test than manually looking through results.

Give tsqlt a try. It’s free, and if you have the SQL Toolbelt, you can get a GUI with SQL Test for executing your tests.

March 13, 2017

Change Your SQL Server Oil

One of the things I learned early in life was that some regular car maintenance goes a long way. I didn’t need to be fanatical about checks or ensure that I did the every recommended maintenance item at every interval, but one thing I’ve always done is change my oil regularly. I could let brake fluid, or coolant, or even tires go a touch longer than I’d like if funds were tight, but changing the oil was critical. I’ve lived by that with most of my vehicles over the years, and they’ve tended to last a long time.

I read a post from Glenn Berry that reminded me of this recently. Glenn talks about the need to perform maintenance on your SQL Server instances in the form of regular patches. I think that’s a good analogy, but certainly not complete. On top of these oil changes, you should think about more regular index maintenance, checking security and backups, monitoring space and more. Those are topics for another day, but no less important than patching.

So how often do you change your oil, or patch your database? For cars, we used to change every 3,000 miles or 3 months (I typically used the former). Over the years some cars have gone to 5,000 miles, though with more modern vehicles and better oil, many cars have gone to 7,500-10,000 mile intervals. My BMW says 15,000 miles, though I usually get nervous around 10k. No matter what your interval, it’s good to have a value and stick to it. That prevents confusion, forgetfulness, or other human errors. Choose a distance or time and maintain your vehicle regularly.

For your SQL Server, I’d typically do the same thing. While Glenn likes to ensure he’s got the latest patches for his customers, I’m not completely sold on applying patches as soon as they’re released. There is a risk of things breaking, or even just service disruption, so I prefer to limit the issues. I prefer to let CU patches come out for a few months and see if any major issues are reported. I also likely would apply patches only 3-4 times a year, rather than the 6 times that the patches are released. In the absence of issues, I prefer stability. I do recognize that I’m taking a chance that I could run into an issue that requires patching to get support, or a security patch is released and needs to be installed quickly. In those situations, I’d need to allocate some testing time and deal with the potential issues at that time.

Along those same lines, I don’t like updating all my instances at once. Certainly not all of a same version. If I have 20 SQL Server 2016 instances, I don’t necessarily want to patch them all today. I might patch 2-3 to ensure that things work smoothly. If that’s the case, then I’d schedule the rest next week. My experience with patching is that we usually need at least two groups, and sometimes three. I have an early and normal patch group, and sometimes a delayed one where the patch timing is problematic. However, I can’t get too far out of date as it’s easy to forget about patching older servers, and that can cause plenty of other issues in the future. In general, I prefer just two patch groups.

As Glenn mentions, there are other benefits to regularly patching. You touch these servers and have the chance to ensure that your DR/HA plans are up to date. You think about potential issues, and in general, don’t become complacent with regards to the health of your systems. I certainly think you should apply SPs, and at least a few CUs a year to keep semi-current. And, of course, since you want to treat your instances like cattle, not pets, you also need to ensure you patch test and development systems to match their production counterparts.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

March 13, 2017
Create a DACPAC to Move Databases–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In my last post, I talked about what a DACPAC was. Now let’s create one. This turns out to be about as simple as it could be. First, let’s choose a database. In my case, I’ll use the PartsUnlimited database on my SQL Server 2016 instance. I’ll start by right clicking the database and selecting “Tasks”.

Down near the bottom there is an “Extract Data-tier Application” entry. Choose that. Once you do, you should get a wizard screen. We’ll click past the first screen.

Next we need to set the properties of the DACPAC we are building. The application name is usually the database. The version can be anything, but ideally you are versioning your database in some way. Most people don’t, so they can leave this as 1.0.0.0. If you are actually building your database somehow, you ought to be using a CI process and have some version number. Ideally you’d keep this somewhere in the db and rev it.

We also need to include an optional description and a path to the file. I’ll leave the defaults, but feel free to change these as needed.

The next screen is a summary. If everything looks OK, click Next. This will start the creation of the .DACPAC.

The final screen will show progress, which is fairly short and simple. I think this has always worked for me. If I click the “Finish” button at the bottom (not shown), the dialog disappears.

If I go to the file location, I’ll see my DACPAC in the filesystem. The file is recognized as an SSMS file, and in another post we’ll look at how we unpack this.

I can check this file, however, to be sure there is something inside it. This is a zip file, and if I open it in 7-Zip, I see this:

Opening the model.xml file, I see data that seems like it describes my database. I talked about this in my previous post.

There, a simple DACPAC. This is a format Microsoft uses, and while it’s not perfect, at least I now know how to build one.

SQLNewBlogger

This post really took me about 10 minutes, across a few days. I started it after building a DACPAC, and taking the screenshots, but I didn’t have time to write it. I finally spent the other 6 or so minutes putting these words down the next week.

A quick showcase of something I learned.

March 8, 2017
The Migration Checklist
One of the things I always recommend is that when you upgrade a SQL Server instance, you perform a side by side migration to a new host instead of an in-place upgrade. My main concern is risk. While the upgrade process is fairly smooth, I still have hiccups installing SQL Server at times, and for a live server, the last thing I want to do is have to uninstall SQL Server and reinstall an old version.

Apart from the risk, I also think an upgrade is a great time to refresh hardware. If you’re paying for the latest bits, I’d spend a little more for newer hardware if I can. The cost usually isn’t much compared to SQL licenses, especially these days with hardware being very cheap and powerful. New hardware also gives me a staging place to test the migrations, without disturbing the existing system.

Planning the migration across hosts usually isn’t too difficult, but that there can always be small issues that I need to fix after the job is done. In most cases, that’s not a problem. In some, it can cause downtime (and plenty of embarassment), not to mention a loss of confidence in the DBA team. I find that I often end up building a checklist, working through the existing instance to ensure I don’t forget any items, fixing my list as I run test migrations to new hardware and find issues.

That makes me think I should have a good checklist that I can start from, marking this up for my specific instances. BOL doesn’t give much information. I did see a nice one in a forum post on SQLServerCentral, but I’d really like a good, solid checklist. An overall list from @spaghettidba might be a good start, and I’m hoping he’ll write an article on each of these, with the list for someone to check. However, I’m hoping to give him, and others, a jump start.

What items do you need in a checklist for a SQL Server migration?

This could be just moving the existing SQL Server instance to new hardware. It could be a version upgrade, consolidation, or any other reason to move. In any case, I’ll start with a general list of things to check. Let me know what I’ve missed:

Instance Level
- Check new hardware/software meets requirements for SQL Server
- Verify patches levels are the same (with items needed for installs)
- Map paths from old to new drives, verifying space
- Ensure all logins, server roles, credentials, and permissions are migrated.
- Migrate all sp_configure items
- Migrate startup stored procedures
- Migrate all linked servers
- Migrate all XE sessions
- Migrate Audits
- Migrate any server level cryptographic objects
- Migrate all jobs and agent settings (operators, alerts)
- Migrate SSIS stuff
- Migrate Resource Governor data
- Migrate Database Mail settings
- migrate replication settings at the instance.
Database Level
- Backup all databases
- Backup and certificates needed for TDE
- Restore databases with new paths
- Verify database ownership
- Ensure backups are running on the new instance
As a side note, dbatools will perform much, or maybe all (still trying to determine that), of what you need. There is a Start-SqlMigration that is very impressive. While I would still want a checklist to ensure the new system works as needed, I think I’d use the PoSh tools and then add anything else I need to them.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn.
March 8, 2017