Who’s Got a Preteen SQL Server?

This year we will have the end of extended support for SQL Server 2005. Microsoft has been reminding people they should upgrade, which may be important if your organization requires support for the running software. Personally I think if you’re still running SQL Server 2005, and it works for you, why upgrade?

It’s been ten years since that version was released, and while it was a huge step forward with the SQL CLR, better mail, and more, it had plenty of issues. Since that time we’ve had 4 major releases with a fifth coming this year. All of those versions have built on the base of SQL Server 2005’s completely rewritten codebase and have improved many features.

Recently I got a note from someone that noted that they have been upgrading their SQL 2005 systems over the last few years (along with SQL 2008 instances) and have made great progress. however they still have a few SQL 2005 instances that they are racing to upgrade. This person was wondering how many others are out there facing similar struggles.

So this week I’m wondering. How many of you have those instances growing up and getting close to double digit ages? Are you working to upgrade SQL 2005 instances? Have you been thinking about those 2008 instances who are already out of mainstream support and will leave extended support in a few years?

Let us know how you think about your aging database software.

Steve Jones

The Voice of the DBA Podcast

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

Creating a Database Snapshot

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

I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.

However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick "create database xx" syntax I often use, with a snapshot I need to be more specific.

The big item that I must specify is the location of the snapshot file. This is the file that gets written to with the copy-on-write process that ensures the original state of the pages when the snapshot is created are still available.

You do need to give the database a logical name as well, which can be anything, but the reference below has suggestions. I’d say that this is bad:

CREATE DATABASE sandbox_snapshot
ON (NAME = MySnap
, FILENAME = ‘h:\SQL Server\MySnap.ss’
)
AS SNAPSHOT OF Sandbox

But this is better

CREATE DATABASE sandbox_snapshot_20150122_1345
ON (NAME = SandBox_Snap_20150122
, FILENAME = ‘h:\SQL Server\MySnap.ss’
)
AS SNAPSHOT OF Sandbox

Because the snapshot is based on an existing database, at a particular point in time, it’s useful to specify the time when the snapshot was created, if possible. While you can get this from metadata, if you have people that look to multiple snapshots for information, it can be handy to know when each is from.

However if you are providing this on a schedule, like daily, for people to report from, you might need to have the same name every day. Think about this, as you cannot rename a snapshot once it’s created.

SQLNewBlogger

When I realized I had to lookup the syntax, I took a few notes and captured code, which meant I combined this writing (< 10 minutes) with other work I was doing.

You should do the same. When you tackle something new, take screenshots, save code, and drop it in a OneNote/EverNote/etc notebook for your weekly blog writing.

Reference

The references I used:

Disabling SQL Server Network Protocols

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

I ran across a question on network protocols recently, which is something I rarely deal with. Often the default setup for SQL Server is fine, but there are certainly times you should add or remove network connectivity according to your environment.

Here’s a short post on turning off (or on) a network protocol for SQL Sever.

What’s Enabled?

The easiest way to verify what’s enabled is to use the SQL Server Configuration Manager. You’ll need administrative permissions on the host to run this, but it’s easy to find.

2016-01-13 14_59_40-Start

Once you open it, typically you’ll have a list of the items that can be configured.

2016-01-13 15_02_09-Photos

We want the SQL Server Network Configuration, which is the server level configuration for this host. The Client configurations are for the host being used a client to connect to a SQL Server.

2016-01-13 15_02_31-Photos

As you can see here, I have Shared Memory and TCP/IP enabled for this instance, but Named Pipes disabled.

Disabling a Protocol

As you might expect, this is easy. I right click on a protocol, and I can change the status. In this case, I’ll disable Shared Memory

2016-01-13 15_03_50-Photos

Once I do that, the protocol is disabled. However not on the instance. I’ll get this message.

2016-01-13 15_04_56-Photos

I need to restart the server. Once that’s done, no one will be able to use Shared Memory on the host.

I can fix this

2016-01-13 15_04_49-Photos

Of course, I need to restart my instance again.

Checking the Log

When SQL Server starts, quite a bit of configuration information is written into the log. This is useful for troubleshooting in many cases. One of the things you’ll find is the network listeners, as shown here.

2016-01-13 15_08_14-Log File Viewer - JollyGreenGiant_SQL2016

This is usually after the database startup information, so if you look, you can see I have some local pipes and some TCP/IP settings here.

SQLNewBlogger

After reading a question, this was less than 10 minutes to write, with making screenshots. However I’ve done this before. If this was your first time, then it might take you longer to research and write, but I bet most DBAs could do this in 30-45 minutes.

Rebuilding a Heap–Don’t Do It

I saw someone mention recently that you can run a REBUILD on a heap table. I hadn’t realized that, but when I looked in BOL, I saw that indeed you can run this on a heap. That’s interesting, in that you can now move the pages in a heap around to remove fragmentation. At first glance that sounds good, but I wasn’t sure.  A little more searching was required.

The authoritative source for me on many things like this is SQLskills, and sure enough, Paul Randal has a myth piece on this. Paul has a great explanation, but basically if you rebuild the HEAP structure, you are creating work in that every non-clustered index also has to be rebuilt. Why? The pointers back to the heap pages, which are locations, will change.

What about adding a clustered index and dropping it? Nooooooo, and again, I learned something new. This causes two rebuilds of the non-clustered indexes as they are rebuilt with the cluster addition and then rebuilt when the table changes back to a heap (to get the heap locations). That’s crazy, and certainly not what we want.

The short answer here is that you want a clustered index, for the maintenance reasons here, as well as others. If you don’t have a fundamental reason not to create a clustered index, just add one.

Just do it.

An Impressive SQL Server

This editorial was originally published on May 6, 2011. It is being re-run for the Christmas holiday.

Merry Christmas and Happy Holidays!

Recently I heard about one SQL Server with 1TB of RAM and another one that was handling over 10,000 transactions a second. I read a note about a 1.1PB database, built on SQL Server to hold data from telescopes. That particular data warehouse is producing 1.4TB a day. I know it’s mostly binary data, but that’s a lot of 1s and 0s being stored in SQL Server.

Over the years I’ve heard regular complaints and concerns from executives about SQL Server scalability. Those complaints have dwindled, but I think there are people in the relational database world that still question SQL Server’s ability to handle high volumes or data sets.

This Friday, I wanted to see what might change their mind, or maybe what might make you view SQL Server differently. While I suspect most of you are SQL Server fans, that doesn’t mean that you don’t have concerns at times about how far you can push SQL Server. The question this week is:

What company would impress you if SQL Server backed their main systems?

This isn’t scientific, and I’m not defining which systems impress you. It could be their web site, their supply chain management, their largest data warehouse, or anything else. What company talking about using SQL Server in one of their systems would impress you? And which system would you like to see backed by SQL Server?

When I think about a lot of data, high volumes and rates, I think of a few things. The stock market, sports, and Amazon. If Amazon switched their website and ordering systems to SQL Server, I’d be impressed. If Major League Baseball, or the National Football League were to run all their statistical systems on SQL Server, I’d be impressed. With all the fantasy sports fans out there, the gathering and processing live data for real time calculations of performance, would be impressive.

I’ve left out other companies. Wal-Mart using SQL Server for supply chain management would be impressive. UPS managing packages on SQL Server would be something. I’m sure have your own ideas, and let us know this week.

Steve Jones

Tracking Logins with Extended Events

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

I was building a question the other day on Extended Events and needed to build a new session. One of the easier sessions to build is with the logins, so I decided to use that, and then wrote this post on how to build the session.

The first thing is to to to the Management tab in SSMS and then the Extended Events folder. I right click the Sessions folder and select New Session.

2015-11-18 10_40_06-Cortana

This gives you a dialog. Like many in SSMS, we start with a name. You could select run at server startup, which I’d do in the case of this being a production system.

2015-11-18 10_40_55-Photos

When I click next, I get to the "Events" tab, which lists all events. I’ll scroll down to login and select that. I need to click the arrow to the right.

2015-11-18 10_41_12-Photos

Once I do that, my event is in the session.

2015-11-18 10_41_19-Photos

After I pick the events, I choose the fields I’m going to capture. There is a "Configure" button in the upper right that you click. This scrolls the dialog over.

2015-11-18 10_41_38-Photos

I can select any number of fields for capture. You can see I’ve picked the client_hostname. I would also add the NT_Username and Username from the list. You could add more, but in this case, I’m more concerned with seeing who’s logged in.

I could add filters, but I choose not to. I click on Data Storage to determine where to store this data.

2015-11-18 10_45_59-Photos

For auditing, I might want a file. In this case, for testing, I’ll use the ring buffer, in memory storage.

2015-11-18 10_46_15-Photos

That’s it for getting the session set up. However it’s not started. To do that, I need to right click the session and select Start.

2015-11-18 10_47_34-Start

This will allow the server to start collecting data. Is it working? Let’s see. We’ll watch the data. Right click the session again and select Watch Live Data

2015-11-18 10_47_43-Cortana

This pops open a window. I usually make this a separate vertical tab group. Once that’s open, I’ll click "New Query" in SSMS, which will log me in again

2015-11-18 10_48_05-Photos

As you can see, a few events popped up here. I am capturing data. Don’t forget to stop the session after this if you don’t need it.

SQLNewBlogger

This post came out of work I was doing, and which I’d likely do as a DBA. However as soon as I got things working and tested, I knew this was a good post. In fact, I got a couple posts from the process. The setup and testing took about 20 minutes, including a little research. However the writing for this was about 10 minutes.

References

A few things I used.

The Writeable Warm Standby

I saw a question recently that went like this: I get one full backup from FTP. I’ll get daily log backups through FTP after this, but never another full. I need to restore this daily log backup and allow the group receiving the database to read/write the copy, and then reset it with the new log backup overnight.

First, this is untenable. At some point you’ll have some issue with transfer, lose a log, or the database will go corrupt. I can guanantee you that at some point you will need another full backup. Not every week, or even every month, but you will need one.

Second, this is a tough situation. I saw some answers, which I agreed with, but I started thinking about ways to get that data moved. My first thought it use STANDBY and move the data every day to a clean database. I’ve done this before, and in the GB range, even 100s of GB, this can work. It helps if you can whack indexes and constraints on the destination, but a copy of data table-by-table goes fast.

However then I thought about other ways. You can’t take a backup of a standby database, nor can you take a snapshot. However while searching, I saw an answer to this post on SO.

TL;DR: copy the mdf/ldf to a new database.

That was interesting, so I decided to test it. Turns out, it works pretty well. HOWEVER, it’s dangerous, and I think you should be very careful about this. I wouldn’t count on this being production stable, and certainly not data stable. You better have other copies of this data.

Here’s what I did. First, create a database with some data.

CREATE DATABASE mydb;
GO
USE mydb;
GO
CREATE TABLE mytable(id INT);
GO
INSERT mytable SELECT 1;
GO

Next, let’s back this up, take if offline, and then copy files.

USE master;
GO
BACKUP DATABASE mydb
 TO DISK = 'mydb.bak';
GO

Now we can copy the files to new files. There are UAC issues here, so you’ll need to add some rights to the files if you do this regularly. I left a comment in my script, but I actually did a CTRL+C,CTRL+V in the data folder, allowing the UAC permissions to work.

2015-12-11 08_42_22-Photos

Once that was done, I had new files:

2015-12-11 08_28_15-Photos

I tried to run at attach, but got a permissions error:

Msg 5120, Level 16, State 101, Line 30
Unable to open the physical file “D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb_reporting.mdf”. Operating system error 5: “5(Access is denied.)”.

The solution I found was to run SSMS as an administrator. Annoying, but it works.

At least for the OS error. However, then you get this:

Msg 1824, Level 16, State 1, Line 35 Cannot attach a database that was being restored.

You can’t do this. At least not easily.

You can do this. First, delete the files you copied over, then run this:

CREATE DATABASE mydb_reporting
go
alter database mydb_reporting set offline;

The next step is to delete the MDF and LDF files, which will be mydb_reporting.mdf and mydb_reporting_log.ldf  by default. I could specify other names, and would if this were something I needed to script.

Once those files were deleted, I’d next copy my files again and rename them. That would result in this:

  • mydb_reporting_base.mdf –> mydb_reporting.mdf
  • mydb_reporting_base_log.mdf –> mydb_reporting_log.ldf

Now I can go back to SSMS. In SSMS, I do a simple ALTER.

ALTER DATABASE MYDB_Reporting SET ONLINE

Then I can run this:

2015-12-11 09_03_28-Start

I have a copy of my database. Can I apply logs and move forward? Let’s try. First, let’s add data and make a log backup

USE mydb
GO
INSERT mytable SELECT 99
GO

BACKUP LOG mydb TO DISK = 'mydblog.trn'   
GO

Next we restore again. We also set the databases offline again.

RESTORE LOG mydb_reporting_base
 FROM DISK = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup\mydblog.trn'
 WITH  MOVE N'mydb' TO N'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb_reporting_base.mdf'
    ,  MOVE N'mydb_log' TO N'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb_reporting_base_log.ldf'
    , STANDBY = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup\undo.log';
GO
USE master
GO
ALTER DATABASE mydb_reporting_base
 SET OFFLINE;
GO
ALTER DATABASE mydb_reporting SET OFFLINE
GO

Once again it’s file copy time. The UAC comes into play again as I copy and rename the _base files. However once that’s done, things work.

2015-12-11 10_17_02-Photos

This works, but I am not recommending this as something you should do, especially for critical systems. This can work, but it’s dangerous, and really not supported by MS.

Custom Schemas

One of the things I seem to see more and more is an expanded use of schemas in their development efforts. This isn’t something I had done much in the past as almost half of my career was with SQL Server 2000 or earlier, where we didn’t really have schemas and we were loathe to tie too many objects to owners. However the last decade has had schemas as a first class citizen in all SQL Server versions since 2005.

I’ve seen a number of software packages use schemas, but it seemed more as a way of separating out development efforts than any well thought out architecture. However there have been a few interesting uses of schemas as a way of duplicating tables for various uses. ETL and auditing are two of the more common uses, though there have been others.

I ran into an interesting approach recently that I found interesting. A group of developers had added a new schema to separate out their custom objects from all other objects in the database. They did this as the database was created by, and maintained by a third party application. They wanted to create new objects, but wanted them separate from the vendor’s objects, and used a new schema.

I thought this was a good idea and wondered who else be doing something similar. This Friday, I wanted to ask the question of the rest of you.

Do you have a custom or reporting schema as a way to separate objects from those that aren’t under your development control?

Let us know if you’ve created some interesting use of schemas. Or are there reasons you don’t use schemas? Have you never considered the advantages of using schemas to group  objects in their own section of the database?

I think schemas can be a powerful way of grouping objects, applying separate security, and  organizing your database in a way that makes it easier for developers, and report writers, to manage the complex objects that may clutter up your database.

Steve Jones

The Voice of the DBA Podcast

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

Using Automated Tests to Raise Code Quality

Abstract

Agile development practices can speed the development of applications and increase the rate at which you can deploy features for your customers. But unless you include a high level of test coverage in your code, these practices can also increase the number of bugs that are released. Databases can be particularly challenging for developers. This session examines database refactorings that introduce bugs, which are detected by automated tests. This approach allows developers to rapidly fix their code before a customer is affected.

Level: 200

Demos

These are the demos shown in this talk.

  • Adding test data inline
  • Added test data in a procedure
  • Adding test data from a separate set of tables.
  • Exclusions to SQL Cop or other tests with Extended Properties.
  • Using FakeFunction
  • Using SpyProcedure
  • The boundary issues with multiple requirements for a function.
  • 0-1-Some testing
  • Catching dependencies.

Downloads

Here are the downloads for the talk.

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