Rebuilding System Databases

Sometimes in fishing you really want a do-over. Last weekend I was fishing a lake up in Onieda County for muskies (my favorite!). As we drifted over a shallow finger that extended into deep water, I hooked up with a solid muskie about 10 – 15 feet away from the boat. It appeared out of nowhere, and just grabbed the bait and held it. Not feeling a strong strike, I wasn’t even aware the fish was there until I felt some weight and looked over to see it. Then I made a series of mistakes that cost me a catch. First, I didn’t give a sharp hookset. I put a lot of weight against the fish by pulling hard, but did not drive the hooks in with a sharp tug. The second mistake was made a couple weeks before. I was using the lure, a shallowraider, when I caught my opening-day muskie, and I never re-sharpened the hooks. Due to the hard boney mouth of the muskie, it is incredibly important to keep your hooks razor sharp. Third, I didn’t stay in control of the fish. As he approached the boat straight on, I had nowhere to go. I also became distracted as I could feel him coming up for a jump. The fish leapt out of the water and shook his head viscously. When he landed back in the water, the lure popped out of his mouth and the fish was lost. I got a great look at him and he was definitely around 40 – 41″, a good sized muskie. Losing that fish will continue to gnaw at me until my next encounter with a muskie.
DBA’s can also really want a do-over. As I have been practicing my cutover for the server migration, I have made a lot of mistakes. My last one was to try and restore the Production server’s master database to the new server. This wouldn’t usually be a problem except the drive letters are different on the new server, as is the path of the system databases. By restoring the mater database, I ran into a situation where I was not able to get the service to come all the way back up. My only option is to start over fresh. I’m going to try to rebuild the system database. I use the instructions from Microsoft here.
First I Mount the SQL Server 2008 R2 installation media.
20150529 System Database Rebuild
Next I run this command in an elevanted command prompt.
20150529 System Database Rebuild Cmd
Upon completion there is no message in the command prompt.
20150529 System Database Rebuild Cmd Completed
Lastly, I am able to start the service.
20150529 System Database Rebuild Service Back Up
Use this only in extreme cases or in test environments as it will wipe out all the data in each of the system databases. The last step would be to restore each database from a backup, if possible.

Set Up Database Mirroring in SQL Server

I’ve been really excited by how enthusiastic my four year old son is to go fishing. There’s never a time when he isn’t in the mood to go catch some fish, although he still needs to be consistently catching fish or else he gets bored pretty quickly. Last Sunday we had talked about fishing on a small lake near our house, but as we were preparing to leave it started raining. It was a cool day and pretty windy, and I knew we’d get cold and wet fairly quickly so I asked him if he really wanted to go. Ever the fisherman, he assured me that he wanted to fish despite the poor weather. We ended up catching only a single fish, but it was his first crappie ever, so it was worth the trip.

Nate with Crappie

I got a tip on another good panfish lake near our home, so we’ll be trying that one in the next couple of weeks. Hopefully the weather will cooperate a little better so we can have a more enjoyable trip. The key catching enough fish to keep small children interested is practice. Panfish are usually quite plentiful, so you need only to practice to learn where the fish like to congregate. Then you can keep your children happy for a couple hours, and make some great memories at the same time.

Practice is also important as a DBA, particularily when attempting a large project. As I have noted in a few of my recent posts, we are going to be doing a server migration at work in the next couple months. We have a brand new production server and need to move SQL Server, including SSIS and SSRS, over to the new server. Downtime isn’t a huge concern for my current employer based on our system configuration and requirements, but I’m still going to try to minimize it as practice for the future when it will be important. I’ve decided the method for moving the databases over to the new server will involve setting up database mirroring from the new server to the old. This will allow the servers to be pretty much in sync when it’s time to cut over. First I need some practice.

Today I’m going to demonstrate the steps I take to set up database mirroring for our Test environment. We aren’t going to be ready to cut over our Production environment for a few months, but I want to make sure I’ve got the process down before attempting it. The first step to set up database mirroring is to take a full backup and log backup of the database. I’m going to start with our largest database which has nine data files and is roughly 54 GB in size. Since this is our test environment I first need to change the database into Full Recovery mode.

USE MASTER;
ALTER DATABASE [App_Test] SET RECOVERY FULL;

Next I take a full backup and a transaction log backup of the database. This is occuring on the current Test environment SQL Server, which I’ll call the Principal for the remainder of this blog post.

BACKUP DATABASE [App_Test] TO  DISK = N'S:\SQLData\AppTestFull.bak' WITH NOFORMAT, NOINIT,  
NAME = N'App_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
GO

BACKUP LOG [App_Test] TO  DISK = N'S:\SQLData\AppTestLog.trn' WITH NOFORMAT, NOINIT,  
NAME = N'App_Test-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Now I’ll restore these backups on the new server, which I’ll call Mirror for the remainder of this blog post. I moved the backup files to a shared drive on a storage server so the Mirror server could access it. The one important option is to keep the database in NORECOVERY so that we can apply the additional transaction log backups and the database stays offline. A database that has been restored with NORECOVERY will show up in SSMS in the Restoring… status:

20150514 Set Up Database Mirroring NORECOVERY

USE [master];
RESTORE DATABASE [App_Test] FROM  DISK = N'\\server\SQLBackup\AppTestFull.bak' WITH  FILE = 1,  
MOVE N'cwidb_Data' TO N'D:\DataFiles\App_Test.mdf',  
MOVE N'AppTest2' TO N'D:\DataFiles\AppTest2.ndf',  
MOVE N'AppTest3' TO N'D:\DataFiles\AppTest3.ndf',  
MOVE N'AppTest4' TO N'D:\DataFiles\AppTest4.ndf',  
MOVE N'AppTest5' TO N'D:\DataFiles\AppTest5.ndf',  
MOVE N'AppTest6' TO N'D:\DataFiles\AppTest6.ndf',  
MOVE N'AppTest7' TO N'D:\DataFiles\AppTest7.ndf',  
MOVE N'AppTest8' TO N'D:\DataFiles\App_Test8.ndf',  
MOVE N'AppTest9' TO N'D:\DataFiles\App_Test9.ndf',  
MOVE N'cwidb_Log' TO N'E:\LogFiles\App_Test_1.ldf',  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5;
GO

RESTORE LOG [App_Test] FROM  DISK = N'\\Server\SQLBackup\AppTestLog.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Next go to the Mirroring tab of the database properties on the Principal server.  Click Configure Security…20150514 Set Up Database Mirroring Principal Database Properties

A wizard will start to guide you through the setup. First it will ask if I want to include a Witness Server. This server watches the principal so you can automatically failover to the mirror if the principal becomes unavailable. Since I am only using mirroring to prep the Mirror server to be the Production server, I’m not going to set up a Witness.

20150514 Set Up Database Mirroring Wizard No Witness

The next screen allows us to set up some basic information about the Principal Server. I will leave the defaults for this.

20150514 Set Up Database Mirroring Set up Principal

The next screen is for setting up the Mirror server. Again, I will leave the defaults for this.

20150514 Set Up Database Mirroring Mirror Database Setup

The next screen lets you specify accounts for the mirror process. Since the service accounts that run SQL on both these instances already have SQL logins with CONNECT permissions, I originally tried to leave these text boxes blank.  However, I got a connect error (at the end of the process) when I did this:

20150514 Set Up Database Mirroring Port Not Found

So I ended up filling in the boxes with the domain account running SQL:

20150514 Set Up Database Mirroring Wizard Service Accounts

Lastly I get a summary screen. Press Finish to run the Configuration.

20150514 Set Up Database Mirroring Wizard Summary

After pressing Finish, it took a very short time for the configuration to complete:

20150514 Set Up Database Mirroring Configuration Complete

Press Close and another message box will start up asking if I want to start mirroring. I do want to start mirroring, but first I need to change the Operating mode. It is currently set to synchronous, which is high safety without automatic failover. Since this is just to prep a server migration, I don’t want the mirror to be slowing down transactions on the principal server. I need to choose asynchronous, which means that transactions will occur on the principal server immediately, and then be transfered over to the mirror. I choose Do Not Start Mirroring:

20150514 Set Up Database Mirroring Start Mirroring

Next I go back to the properties and change the operating mode to asynchronous:

20150514 Set Up Database Mirroring Change Operating Mode

At this point I chose to start mirroring:

20150514 Set Up Database Mirroring Working

20150514 Set Up Database Mirroring Working2

That is how you set up mirroring.  Now when I am ready to migrate servers, I will have all the data already on the new server.

How to get Read/Write percentages for a SQL Server database

The 2015 fishing opener started out with some frustration, but ended up being quite successful for me. I got up at 4:40 AM, which was just a bit later than I had wanted. I also took a bit too much time showering and eating breakfast, so I got to the boat launch on Okauchee lake at 6:10 AM, 25 minutes later than I had intended. Now this was my first fishing opener with a boat, and I clearly underestimated how many other people were as pumped up about it as I was, because the boat launch was full! I was disappointed because I had really wanted to fish Okauchee, but the public launch on Pewaukee at Naga-Waukee is only a ten minute drive from there, so I decided to head over to Pewaukee. Upon arrival, I was shocked to see that this launch, which is much larger than Okauchee with 71 trailer parking places, was also full. I ended up going over to Smokey’s Bait Shop on the far east end of the lake and launching there. I’d never fished the east end of Pewaukee before but I knew it was shallow and weedy all over. As I motored out from the launch, I discovered good weeds almost immediately, and decided this was as good a spot as any to search out some muskies. The wind was coming from the southwest, and I drifted toward the north shore of the lake. I saw a muskie pretty quickly, although it didn’t seem to be following my bait, it was still a good sign that their were fish in the area. The weeds were pretty high in this area, and I was cleaning seaweed off the hooks of my perch colored shallow raider after almost every cast. I began to get close to shore and was about to stop fishing so I could motor back toward the middle of the lake when I saw a hard weedline stretching parallel to the shore. The weeds seemed to completely stop when they got about 20 – 30 feet from shore. This looked like an excellent ambush location, so I fired off a cast west along the weedline. As the lure got to the boat I looked down to see a beautiful muskie following the bait closely. She bit the bait and gave a head shake, and I quickly pulled the rod tip up and drove the hook into her mouth. The fight wasn’t too intense, although she did walk me completely around the boat once. I netted and unhooked her, got a couple of pictures, and then released her back into the water.
20150502 Muskie Enhanced[2]
The rest of the outting was good, but I didn’t land any more muskies. I saw three others in the water, saw four other boats land muskies, and also landed a pretty solid largemouth bass. It was a fun day, and great to be back out on the water after a long winter.

We are going to be replacing our product SQL Server in the next couple months. We have already picked out the new hardware, and I have begun the process of installing SQL Server and running some tests. One important test that should always be run when replacing a server is disk I/O, especially if you are not using a SAN. There is a great tool that can be used to measure disk I/O, but I will demonstrate that in a later post. First we need some preliminary information, the most important one being the read/write ratio of your databases. Fortunately, SQL Server tallies each and every read and write in the form of index stats in the sys.dm_db_index_usage_stats DMV. Using this view, we can get a great idea of the percentage of reads and writes in each database.

I wrote this query, which takes the sum of all reads, both system and user, and divides it by the sum of all reads and writes to get the total read percentage. Similarly, it takes the sum of all writes and divides it by the sum of all reads and writes to get the write percentage. Lastly, I group by database so I can see the difference in each database on the instance. This wouldn’t be required if you wanted an overall picture of all databases on the instance, but I know what my most important and largest databases are, and I want to really know the read/write percentage on those databases.

SELECT  DB_NAME(s.database_id) AS Database_Name ,
        SUM(s.user_seeks +0.000
           + s.user_scans 
           + s.user_lookups 
           + s.system_seeks
            + s.system_scans 
           + s.system_lookups) / SUM(s.user_seeks
                                       + s.user_scans
                                       + s.user_lookups
                                       + s.user_updates
                                       + s.system_seeks
                                       + s.system_scans
                                       + s.system_lookups
                                       + s.system_updates) AS Read_Percentage ,
        SUM(s.user_updates + 0.000
           + s.system_updates) / SUM(s.user_seeks
                                        + s.user_scans
                                        + s.user_lookups
                                        + s.user_updates
                                        + s.system_seeks
                                        + s.system_scans
                                        + s.system_lookups
                                        + s.system_updates) AS Write_Percentage
FROM    sys.dm_db_index_usage_stats AS s
GROUP BY s.database_id
ORDER BY DB_NAME(s.database_id);

20150506 Read Write Percentages
A couple notes on this query:

  • The stats are cumulative since the last SQL service restart.  The longer you wait to run this since the last server reboot or service restart, the more meaningful the stats will be.
  • The DMV used in this query does not exist on versions prior to SQL Server 2008.

Stay tuned for an upcoming post where I will use the results of this query to measure disk I/O for a new server compared to the current server.