Voice of the DBA

Tag: sql server

Using Trace Flags

At SQLServerCentral, we recently published a list of all the trace flags from Konstantin Taranov. It’s a great list, and thanks to Konstantin for compiling it. While I haven’t often used trace flags, there are some that have really helped me at various times when I needed to change SQL Server behavior. If you aren’t sure what trace flags are, Erin Stellato of SQLskills wrote a great post recently. I see trace flags as feature flags. The development team can allow us to experiment, test, and use functionality at our discretion, or ignore it.

This week, I wanted to ask how many of you are using Trace Flags right now. Do you have any running in code or set for startup on your instances? If you don’t know how to do this, we’ve got a short piece to help you.

In Erin’s post, she notes that SQLskills only recommends three trace flags (depending on version) for their customers. In general, I think that’s good advice. There is a risk with using flags, and certainly I would be wary of using without substantial testing. I do think Erin’s list is good, and you might consider using those. I also become wary about trace flags that aren’t embedded directly in code. I think these trace flags end up being hidden from anyone troubleshooting issues. After all, how many of you actually go to the Configuration Manager or the Services Applet and look for parameters?

I expect that most of you don’t run trace flags on your instances. There may be some of you that have never heard or, or used, a trace flag in your career. That’s fine, though I hope you use today to a) let us know, and b) educate yourself to ensure you know how to enable a flag you need one. If nothing else, add one to a test instance, and ensure you have the skills to actually make the change.

Steve Jones

The Voice of the DBA Podcast

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

March 17, 2017
Global v Session Trace Flags

I wrote a short article on enabling and disabling trace flags. You can read it, but I didn’t really discuss the implications of session v global trace flags, which is something I’d like to do here.

In the article, I set trace flag 3226 for my session. This showed that a second backup wasn’t in the error log. Note the image below doesn’t have a backup message after (above) the trace flag change entry. You’ll have to trust me that I ran the backup, enabled the traceflag, and then re-ran the backup to get this image.

However, if I have a backup job, as I do here, does the trace flag affect this? This is, after all, run by SQL Agent, which would be a different session.

It turns out that the session trace flag doesn’t affect this. I ran the backup job and there was a message in the error log.

To suppress this, I’d have to use DBCC TRACEON (3226, –1) or put this in the startup parameters to ensure none of these messages appear.

March 16, 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