Learn to Use Filegroups

This editorial was originally published on May 12, 2011. It is being re-run as Steve is away at SQL Relay today.

In SQL Server, filegroups are a management technique that I don’t see many people using. It’s amazing how many people ask questions about filegroups on the discussion forums, often unsure of how they fit into a well architected SQL Server. I have tended to use filegroups mostly as a space management technique, when I need to add more disks to my server, but they can be used in many more places.

We continue to grow our data sizes all the time. While many databases are still measured in the single digits of gigabytes (or smaller), it is fairly common to find many database servers with over a terabyte of disk space. Our disks grow larger and larger, but it seems that data grows faster than disks, requiring larger storage subsystems all the time.

While our storage grows larger, the tolerance for delays shrinks and demands for better performance increase. That means that data professionals need to be more cognizant of not only how their code is written, but also how they design storage. Tiering storage is one idea that I think has merit, but one that requires some planning.

In SQL Server, we can’t split a table across filegroups. Or can we? We can partition a table (Enterprise Edition and higher), which can allow us to manage performance and storage appropriately. There is also the recommended practice of only having system objects in the primary partition and using separate filegroups for user data. That allows you to bring a partial database online, again, in Enterprise Edition only, while you restore different filegroups.

This isn’t the first thing I would recommend you learn about SQL Server, but as you advance your knowledge, you should better understand when and how filegroups can help you. You will use them at some point and being comfortable with a filegroup restore is one of the skills that separates the accidental DBA from the data professional.

Steve Jones

T-SQL Tuesday #70 – The Enterprise

tsqltuesdayIt’s hard to believe this is the 70th edition of T-SQL Tuesday. I haven’t missed many, and I’ve enjoyed them all. I hope more of you are participating in this monthly blog party started by Adam Machanic (b / t). Whether you write today, or you write at some time in the future, T-SQL Tuesday topics are good ways to showcase your knowledge.

This month’s invitation comes from Jen McCown, of Midnight DBA. Jen asks us to write about managing an Enterprise of SQL Servers. That’s a good topic, since many of us struggle to manage multiple instances. Whether you have 5 or 500, there are some good ideas that you might implement in any environment, and I’m looking forward to reading what people have to say.

Decoupled Consistency

A long time ago I was thrust into the role of managing hundreds of SQL Server instances at a large company. I’d managed dozens of machines before, but this was a whole new level of scale. What’s more, the majority of machines were set up completely independently of each other, with no concerns other than a mandate to try and keep versions close to each other. That was a challenge in and of itself.

The only common tool in use was Patrol, which was mainly used to monitor performance counters, but we didn’t have the SQL Server specifics, so we really could only get general performance counters, and even those were difficult to access when thousands of hosts were being monitored.

I brought an idea with me from a previous job that had served me well with a handful of disparate instances. We’d consistently set up each instance, both installation, configuration, and monitoring, however we’d decouple each instance from others. Our goal was each machine capable of operating independently from all the others.

We had found that central servers go down, that we had no good way of tracking the status from machines when this happened, and most importantly, there are always exceptions.

With this in mind, we

  • built a procedure to install SQL Server, but included a number of scripts for post installation that would standardize settings. This allowed our server build people to easily handle SQL Server installation as part of their job. These days I’d use something like FineBuild to make this easy.
  • set up a DBA database on each instance that monitored jobs and other important status for the instance. If the instance was up and SQL Agent running, we’d know the status of that instance.
  • Performed basic monitoring of some key performance counters for a quick trend of the latest performance over the last week, similar to what SQL Monitor shows. Getting a quick snapshot was quicker than accessing central monitoring, especially in a crisis.
  • Assembled a report for the instance each day, calling out exceptions at the top, and leaving expected data below. We needed documentation for our ISO certification, and our auditors loved this.
  • We did use a central server to assemble the reports from all instances and compile them for the DBAs to review. All exceptions were at the top, and we used a left join to compare the list of instances with current reports. If any were missing, we bubbled that to the top as an exception.

These were all good, simple ideas that allowed a team of 3 DBAs to manage 400-500 instances of SQL Server. We were flexible enough to handle the needs of mission critical Finance instances as well as often changing development machines. Our data was simple and always available for us to give to clients when they had questions.

Most importantly, we built a system that allowed us to deal with exceptions, but not review the mundane stuff. Our time was precious, and that’s the case in any enterprise situation. You need to focus on the 10-20% of systems that really need regular attention while ensuring the other 80-90% of them are still being maintained.

Upgrading a SQL Azure Database to v12

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

I was looking to test some of the Azure v12 capabilities, but I needed to upgrade a database. Here’s a quick look at how.

In the old portal, you’ll see your databases.

2015-08-14 13_49_10-SQL Databases - Microsoft Azure

I love that there’s no information on database version. I’m sure the Azure people love this, but I don’t. Very, very annoying.

If I pick one of these, and go to the “Quick Glance” items on the lower right, I’ll see the “update to v12” message.

2015-08-14 13_49_34-SQL Databases - Microsoft Azure

However in the new portal, it’s different. At first glance it looks the same, but zoom in.

2015-08-14 13_51_00-SQL databases - Microsoft Azure

Now you can see the icons are different. I couldn’t read the 12 at first, but I did notice the color difference.

2015-08-14 13_52_12-SQL databases - Microsoft Azure

Now I can go into the properties of the database and see those. Mine said v2 here, which was strange. Anyway, pick that.

2015-08-14 13_54_02-Movies & TV

You’ll get a new blade, which will show you the upgrade benefits. Select “Upgrade this server”.

2015-08-14 13_54_07-Latest SQL database update - Microsoft Azure

Now you get a blade to upgrade the server.

You must type the server name, not the database name, to upgrade. Note that all databases on this server will be upgraded. Be aware of that. However, it’s not the full server name.

2015-08-14 13_54_48-New notification

It’s just the host, but a tool tip will prompt you if you mess that up.

2015-08-14 13_55_00-

Once that’s done, the upgrade will start on the blade. You can close this.

2015-08-14 13_55_07-Movies & TV

The upgrade will also appear in the main blade for the database. These are scheduled, not real time.

2015-08-14 13_55_14-Movies & TV

However, within about 3-4 minutes, mine changed to “upgrading” from the schedule notice.

2015-08-14 14_00_05-Movies & TV

This was a small database, with just one table in it. However the complete upgrade took awhile. The database is available and usable while this is happening, but it does take time to complete. I think mine took about 15 minutes before it was done, but I wasn’t watching the entire time, so I could be wrong.

Once it’s done, it has the new version

2015-08-14 14_04_30-Mytester - Microsoft Azure

Easy enough, and if you need to upgrade your systems, here’s a simple way. If you have lots of them, I’d use PoSh.


This post didn’t take that long to write. I had the database ready, and I took a few screen shots then started the upgrade. I wrote most of this while waiting for the process to complete and then went on to other work and came back for the final screenshot.

Easy enough, and you should practice this and document it. You’ll have some idea of how to use Azure if someone asks you to later along with some notes for yourself.



The Log Chain

I ran across a question recently from someone that received a similar message to the one showed in this image:

2015-07-30 12_02_22-SQLQuery3.sql - ARISTOTLE.master (ARISTOTLE_Steve (62))_ - Microsoft SQL Server

In this case, the log chain is broken. I’ve received this message, usually from a script that is automatically restoring all the logs in a folder. I’ve also had clients call me with this message, saying their log shipping system is broken.

What has happened is that you (or someone) is restoring the logs out of order. Each log backup forms part of a sequence that we call the log chain. The restore process requires that each log backup be restored in the same sequence in which is was created.

In the error message above, SQL Server is letting you know that the Log Sequence Number (LSN) contained in the file from which a restore was attempted doesn’t match the last LSN in the restored database.

Fixing This

The fix is easy; restore the correct files in the correct order. In this case, I knew I’d taken 3 log backups, and I was restoring in the incorrect order. I’d restored log backup 1, and was now trying to restore log backup 3.

If I restored log backup 2 (using with norecovery), the restore would work. I could then restore log backup 3 and eventually bring the database online.

If you can’t find the correct log backup, then your database is still usable. Just run RESTORE DATABASE WITH RECOVERY, and you can access your data. However the data will only be correct to the point in time of the last successful restore.

That could be a serious problem if you are missing a log backup early in your restore sequence. It could be a career limiting problem.

Always be sure you keep all log backups since the earliest full backup you keep handy for restores and protect those files. Losing one could be a major problem for your organization.

Create a SQL Azure Database–Old Portal

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

I worry about beta software, so when I wanted to create a new Azure database, I stuck with the old portal. Call me nervous, but the new portal worries me at times. It works, but since the default is the old one, I often use that one.

You can use either, but here I’ll show you how to create a database with the old (which will be gone someday) portal.  It’s simple and easy, and this only takes a few minutes.

Creating a Database

Here’s the “old” portal. I wish it had a major version number, because that’s what it needs.

2015-08-14 13_36_27-Movies & TV

I’ve got a few things running here. In the lower left corner is a “New” button, which I can pick. When I do, I choose Data Services and get this.

2015-08-14 13_36_42-SQL Databases - Microsoft Azure

I select SQL Database and then click “Custom.” You could click quick create, but I wanted to show more options. There aren’t a lot, but you can put in a few.

You get the settings page. The first thing you need is a database name. You can pick your subscription as well as the size of the system. Be aware of charges when you pick sizes. S0 is not cheap.

2015-08-14 12_21_27-SQL Databases - Microsoft Azure

I had an existing server, so I used that, but the system will create one for you if you need it. This is really the name you connect to, so if you care, create one first and then use it here.

The next screen is really for the main admin account. Enter a decent password and save it. You can reset this form the portal once it’s up, so don’t be too concerned, but build good habits. Don’t use the same password everywhere and use a password manager so you pick strong ones.

2015-08-14 12_22_28-Store

Once that’s done, create the database. It will take a little time, but you’ll see a moving “Creating” item in the status. There’s no percentage, but I think it was about 3-4 minutes for me one afternoon.

2015-08-14 12_22_39-SQL Databases - Microsoft Azure

Once it’s done, the database list will show you the database as online.

2015-08-14 12_28_16-Store

If you go to the dashboard, you’ll see  a number of items listed.

2015-08-14 12_28_47-Greenshot capture form

Scrolling down, you might see the important item that you care about. How to connect. The server is listed, and if you’re using an app with ADO, PHP, OLEDB, etc, you can click the link and get the strings you will use.

2015-08-14 12_29_01-SQL Databases - Microsoft Azure

That’s it. Simple, easy, and ready for you to use.


I was actually working on something in the new portal and wanted to go back to the old portal to see how things worked. As I waited for the main screen to refresh, I though, hey, write a post.

So I captured screen shots. It took maybe 10-15 minutes to type this up.


None used.

Start SQL Server in Single User Mode

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

I saw someone recently ask how to reset the sa password, and myself (along with a few others) suggested starting SQL Server in single user mode as an administrator. The poster had problems and at one point I suggested using the command line, which I had used in the past. However that didn’t’ work,  and when I tried it myself, it gave me errors.

The errors were Operating System errors, which indicated that the errorlog location wasn’t accessible by me. So I decided to elevate my privileges.

2015-07-06 13_48_41-Start menu

Once I did that, I was able to get the service to start:

2015-07-06 13_52_27-Administrator_ Command Prompt - sqlservr   -m -s sql2012

I’m not sure what was hard here, and this is how I’ve always managed to start and stop SQL Server in single user mode from the command line. I then see the output of what is sent to the error log at the console.

However I also searched around and found a few other ways that are easy to accomplish.

Net Start

I haven’t often used net start for commands, but I have a few times. In this case, I looked at BOL and found I could do this:

2015-07-06 13_43_20-Administrator_ Command Prompt

This is essentially what clicking "start" in the services applet or Configuration Manager does. However I can add in my "m" parameter with a slash (/) instead of a dash.

To stop the service, I use NET STOP.

2015-07-06 13_43_44-Administrator_ Command Prompt

Configuration Manager

If you run Configuration Manager, you can also add parameters. First, right click the service and get the properties. Then you want the "Startup Parameters" tab. In there, you can add a parameter in the top box, as I’ve done here.

2015-07-06 13_44_19-SQL Server (SQL2012) Properties

Clicking "Add" will put it in the list.

2015-07-06 13_44_26-SQL Server (SQL2012) Properties

When you stop the service, the next startup will have this parameter take effect.

2015-07-06 13_44_06-Sql Server Configuration Manager

Beware that when you start things up, the first connection that successfully logs into SQL Server will be the only one allowed. Any applications looking to connect, monitoring programs (SQL Monitor or DLM Dashboard) or other clients can take your connection, so be careful and quick.


This was really a quick writeup. I stopped my service and played for 10 minutes, ran a few searches, and took some screenshots. I would have been faster, but I wanted to document this.

This is great practice for a skill you need rarely, but when you do, you’ll be stressed. Be sure you can start and stop SQL Server a few ways, and add parameters like trace flags and the -m for emergencies.


Using the DAC

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

The DAC is a really important tool for anyone managing a SQL Server. You might not use it often, but when you need it, you really need it. I’d suggest you connect to your instances one a month or so using the DAC, just to be sure you know how to do it.

The most common reason to use the DAC is when a runaway query is consuming so many resources the user can’t connect. The DAC is guaranteed resources, so you can always connect.

When you use SQLCMD, the -A switch lets the connection method know you want to use the DAC. The DAC only allows one connection, so only one admin can connect. This also means it’s important to disconnect quickly if you don’t need the DAC.

To connect, I use a normal set of parameters, -S for my server and -E for Windows auth. However I then add the -A, which connects me through the DAC.

2015-07-13 10_48_04-Command Prompt

Once I connect, I can run any of the normal commands, I need, like rebuilding master or killing a connection.

2015-07-13 10_48_23-SQLCMD

Keep this as a tool in your toolbox and practice regularly.


This was a quick post that follows on from starting SQL in single user mode as well as using the DAC in SSMS. I really just took some shots as I connected from SQLCMD.


The first link I saw was from my friend, Pinal Dave.

Password for SQL Server Service Accounts

I wrote recently about my philosophy for service accounts, and wanted to add a few more thoughts.

Security is important for our database servers. One of the loopholes that everyone should be aware of is that the service running SQL Server has complete control over the service and potentially if this account were compromised, the security of our installation would be at risk.

In this post I wanted to address two things related to service account passwords. The mechanics of building and working with these passwords and the ongoing maintenance in terms of changing the passwords.

Creating Passwords

One of the tools I recommend for anyone administering computer systems, including my parents on their personal computers, is a password manager. There should be a way for you to create and store complex passwords that are not easily guessed. I use Password Safe, but 1Password, KeyPass, and others are just as good.

Typically I’ve used these to store the administrative passwords for various systems for all DBAs, sysops, etc. to use. However I haven’t used these for service accounts.

Why not?

Mostly because I don’t think any of us should be logging in as services. Apart from initial setup and testing, we shouldn’t use service accounts for anything.

I always recommend long, complex, random passwords for services. The password should be created and written down long enough for someone to enter it twice in the areas reserved for credentials, and then the paper should be destroyed.

I write these down because I want extremely long (20+), random strings that aren’t memorable and are really a one-time use string. Used just long enough to enter into the Services applet or as a credential in a PoSh (or other) script.

If you use groups for your account rights, and you should even for service accounts (SQL Server makes this easy), you can always use another account to test access. Grant it the same permissions and groups, and perform your tests.

Changing Passwords

I don’t worry about changing service account passwords. Yes, I know this isn’t recommended, but services rarely change or are used to log on, we can limit the access of an account to a particular machine, and since the password isn’t stored, it’s not very vulnerable to cracking.

If you are worried, then create a new, long, random string for the particular service(s) that are suspected to be vulnerable.

I don’t allow expiration of service account passwords, though in a few organizations that have required yearly service account password changes, we’ve scheduled the changes for slow periods, not waiting until the expiration occurred. I can almost guarantee that accounts will expire during a critical time when machines should not go down.

One caution. I know that changing passwords to long, complex strings is hard, and that there’s a temptation to set services to the same password or use some pattern to build passwords.


Patterns are poor security, and coupling services together with the same password (or account) is not worth the risk of issues if one system requires a change or the password is disclosed.


No Defaults – T-SQL Tuesday #68


It’s the second Tuesday of the month, and time for T-SQL Tuesday. This month’s invitation is from Andy Yun, where he asks you to Just Say No to Defaults.

This is the monthly blog party started by Adam Machanic, and it’s the chance for you to write on a particular topic every month. The hosts rotate, so you have to watch the #tsql2sday hashtag for the topic. Posts for the month need to go live during the day according to UTC time.

I also keep a list of topics on the blog here, and you should feel free to write about any past topics and post something on your blog. It’s great practice, and a good way to get started as a #SQLNewBlogger.

Default Changes

There are all sorts of defaults in SQL Server. The setup program presents you with a number of choices, but in most cases a default exists for setting because SQL Server needs something.

I used to have a setup script that I would run for every new install. In a few jobs, back when we used to have physical servers, a hardware person or sysadmin would install Windows and SQL Server onto a new computer and then send me the name for customization. My script, which was really a series of SQLCMD calls in a batch file that in turn called various other scripts, was designed to add an administrative database, create some jobs to track the system, setup backups, and more.

The process really did part of what Policy Based Management can do, but was simpler and tailored to ensure that all of our SQL Servers worked in a similar manner. We could override settings, but this quick script gave us a starting point that all DBAs understood. We even ran this on development machines for instances we didn’t manage as it allowed us to troubleshoot other issues, it only took a few minutes, and it removed some of the management headaches from the developers’ minds.

However, there is one thing I’ve almost always changed on my instances. I try to do it during setup, but at times I need to do it later. That setting is the default location for files. I do this as I want to usually have data files, log files, and backup files separate from each other.

Even if I don’t have different drives, but setting up separate locations here now, I can easily move the files later and make one change here for the defaults and I know I’ll have things separate.

I’m not running a new install this week, but I’ll show you how to change it on an instance that’s installed. First, right click the instance in Object Explorer and click Properties.

2015-07-06 14_17_48-SQLQuery2.sql - not connected_ - Microsoft SQL Server Management Studio

Next, go to the Database Settings section.

2015-07-06 14_24_07-Server Properties - JOLLYGREENGIANT_SQL2012

At the bottom here you see locations for data, log, and backup. In this case, on my laptop, I only have two drives, so I can’t achieve great separation.

However in any production system, I’d have the data and logs separated to different physical drives, or at least different LUNs. Backups might go with logs, but they’d ideally be separated to another location.