Permissions for SQL Profiler

I recently changed around the entire security structure at my company to give more modularization to personnel roles. In doing so I came across something about SQL Profiler. While I had given full SUID permissions to a group of users to everything in a database, they could not do any traces with SQL Profiler. This is documented in Books Online, but for the DBA who doesn’t venture into these permissions on a regular basis it may not be something often referenced. To enable the group to use SQL Profiler simply grant them the ALTER TRACE permission.

GRANT ALTER TRACE TO [user or group name]

Books Online does warn that granting this permission could reveal sensitive information such as passwords, so use this with caution.

Using an iPad to Manage SQL Server

Recently there was a contest by RedGate in which they gave out iPads and a license to SQL Monitor to 30 lucky winners. They did this by tasking three well-known individuals in the SQL community with giving away 10 each by whatever means they saw fit to use. I’ve met them all at SQL PASS and they deliver a lot to the community. Each came up with his own question for comment and picked the winners from the list of entries. I read through a lot of the entries and realized I was picking up new ideas for ways to use an iPad (I don’t actually have one) or other portable devices. The contest was a way to show the usefulness of SQL Monitor in particular on the iPad. I thought I would compile my observations of how people might use these portable products.

Grant Fritchey blog

He is the Scary DBA. I’ve met him and he’s not all that scary. Actually, he’s a nice guy. I had a chat with him when he happened to be standing next to me when I picked up a book he co-authored. He gave me some good ideas for source control and confirmed some things we were already doing at my company. Grant posed the question, “What do you think the most common cause of server outages is, why, and how would being able to monitor your systems remotely help solve this issue, thereby improving the quality of your life?” His goal was to see how people could achieve a life/work balance. Here’s what I found.

Causes

  • All those inforeseen, festering issues that monitoring software could find
  • Poor application code
  • Disk space (or lack thereof) and other hardware failures
  • Undertraining of developers and DBAs
  • The DBA did it, in the server room, with the $200 gaming mouse
  • Poor planning
  • Bad Communications
  • Not following best practices

How life is better with your iPad/Monitoring solution

  • Know the problem exists
  • Get to the solution faster
  • Work remotely instead of dragging your body everwhere
  • Monitor databases, watch tv, and play Angry Birds…all at the same time.
  • Increased communications
  • Less shoveling – pick your substance
  • Getting alerts before the problems get too big
  • Time to write haiku

Steve Jones blog

He is the editor of SQL Server Central. I’ve met him a couple of time at SQL PASS. He also has a ranch. For some reason it seems a lot of DBAs are have a farm or ranch. Interesting. Anyway, Steve wanted to know how you could could do your job better, quicker, or in a new way to enhance your work and bring value to your employer. These are a few of the many responses.

  • Consume administrative DBA reports with the ability to send repair requests to Jr. DBAs.
  • Watch training videos
  • Develop a gestures SQL writer
  • Read blogs
  • Deliver presentations
  • Run an IT Career and a farm at the same time
  • Write funny limericks
  • Update information while making rounds in a hospital
  • Have a lighter weight option for quick remote troubleshooting
  • Track casino players on the floor
  • Lots of quick troubleshooting alerted by SQL Monitor

Brent Ozar blog

It seems like he does a lot. It looks like he currently does consulting and helps with SQLSkills.com. He is one of a very few Microsoft Certified Masters of SQL Server. I shook his hand once at SQL PASS. Brent wanted to know where you would go if you didn’t have to worry about what your servers were doing.

  • Cruise
  • Germany
  • Disneyland/Disneyworld
  • Various Hawaiian and Caribbean Islands
  • The Kitchen
  • The Bathroom
  • Home
  • Fantasy lands with unicorns and leprechauns OR Margaritaville
  • North, South, East, or West
  • Bed (not sure whether alone or not)

The lists are certainly not all from the 300+ comments across the three blogs, but it gives an idea of what people are thinking.

Transferring Multiple Databases from SQL Server 2005 to 2008 R2

Recently I needed to transfer about 50 smallish databases from one machine running SQL Server 2005 to another machine running SQL Server 2008 R2. These were all production databases that had the potential to be in use 24/7 so the transfer needed to take as little time as possible. I built several scripts that in turn built other scripts that helped me accomplish this task. I ended up breaking the process down into four scripts. The first script detached all of the databases. The second script was actually a batch file that copied the files from one machine to the other. In my case, on the original server the mdfs and ldfs were all on the same array. The copy script actually split them to put the mdfs on one array and the ldfs on another. The third script attached the databases on the new machine. The fourth one simply changed the compatibility level of the databases to 2008 R2.

First, I had to build the commands for each of the databases. I used a script to build everything and copied the result to a file for each of the steps. There were a few items to keep in mind. Before using any of the scripts to detach databases, the databases must not be in use. You can either verify this ahead of time or put the server into single-user mode. Something I ran into with the detach script is that I needed to run it as ‘sa’ or else the Windows permissions got jacked and copying the files in later steps didn’t work. I’m sure it’s something simple but I didn’t research it at the time. I bet someone has the answer why. But back to the scripts.

Create a script to detach the databases from the current server.

SELECT 'USE [master]
GO'
SELECT 'EXEC master.dbo.sp_detach_db @dbname = N''' + D.name + '''
GO '
FROM sys.databases D
WHERE D.database_id > 4
ORDER BY D.name

The script simply uses the sys.databases table and constructs an EXEC statement to call sp_detach_db for each user database on the server. Notice that I limit the results to database_id greater than 4. Typically the first four databases are system databases. You can tweak the number if this doesn’t hold true on your server. Run the script to generate all of the detach statements. You can run the results to Text and do a copy/paste into another file. You can also run the results directly to a file.

Next, create a script to copy the files from the old server to the new server.

SELECT 'robocopy \\OLDSRV\D$\MSSQL\MSSQL.1\MSSQL\Data \\NEWSRV\E$\ ' + RTRIM(substring(right(DbFile.physical_name,charindex('\',reverse(DbFile.physical_name))),2,100))
FROM master.sys.databases D
JOIN master.sys.master_files DbFile ON DbFile.database_id = D.database_id
WHERE D.database_id > 4
ORDER BY D.name

This script creates a line for each file to use robocopy for transfering the file from the old server to the new server. I copied this into a batch file. Another option would be to use this within a Powershell script. With this I copy the databases from one directory on the old server to a different directory on the new server. This was my need but there are all kinds of ways to use this. I happened to also have all of the databases in the same directory. This may not be true in all cases and the script may need to make use of the full path in physical_name of sys.databases.

The third script creates the SQL to attach the databases on the new server.

SELECT 'CREATE DATABASE [' + D.name + '] ON ( FILENAME = N''E:\' + RTRIM(substring(right(DbFile.physical_name,charindex('\',reverse(DbFile.physical_name))),2,100)) + '''),(FILENAME = N''E:\' + RTRIM(substring(right(LogFile.physical_name,charindex('\',reverse(LogFile.physical_name))),2,100)) + ''' ) FOR ATTACH
GO '
FROM sys.databases D
JOIN sys.master_files DbFile ON
D.database_id = DbFile.database_id
AND DbFile.type = 0
JOIN sys.master_files LogFile ON
D.database_id = LogFile.database_id
AND LogFile.type = 1
WHERE D.database_id > 4
ORDER BY D.name

SELECT 'CREATE DATABASE [' + D.name + '] ON ( FILENAME = N''' + DbFile.physical_name + ''' ),( FILENAME = N''' + LogFile.physical_name + ''' ) FOR ATTACH
GO '
FROM sys.databases D
JOIN sys.master_files DbFile ON
D.database_id = DbFile.database_id
AND DbFile.type = 0
JOIN sys.master_files LogFile ON
D.database_id = LogFile.database_id
AND LogFile.type = 1
WHERE D.database_id > 4
ORDER BY D.name

There are two creation scripts here. The first one creates the script to attach the databases for the new server. It attaches the files from their new locations used during the copy step. The second script creates the script to attach the databases back on the old server from their original locations. This is in case you still want the old server or if something went wrong during the transfer process.

The last script is used to update the compatibility mode of the newly attached databases on the SQL Server 2008 R2 server.

SELECT 'ALTER DATABASE [' + D.name + '] SET COMPATIBILITY_LEVEL = 100
GO'
FROM sys.databases D
WHERE D.database_id > 4
ORDER BY D.name

When you restore a 2005 database to a 2008 R2 server the database gets upgraded automatically. However, the database is set to compatibility mode 90, which is SQL Server 2005. This script will update the compatibility mode to 100, which is SQL Server 2008 R2.

After following these steps you can have your SQL Server 2005 databases moved to a SQL Server 2008 R2 machine in no time.

I want to give a reference to Aaron Nelson (@sqlvariant). He had a blog post that got me started down this path.

My Little Tidbits

As I mentioned in my first post on this site, I have dabbled in writing in the past. Way back in 1997 I even self-published a book on HTML. It was written initially for a general technology class my mother was teaching at the high school level. Since it was their textbook I named it HTML: A Textbook Guide. I actually wrote each chapter about a week before they needed it and by the end of the first semester I had a book. I then edited it; created things like a table of contents, index, references, and acknowledgements; applied for ISBNs; sent the paper version to a printer; and created an electronic version of the entire book. I managed to sell only a handful of copies since marketing was virtually nonexistent.

A few years later in 2002 I co-wrote a book called Managing the Gifted Mind in a Technological World. It had nothing to do with programming. It was about an educational philosophy that my co-author, who happens to also be my mother, came up with. I, again, did a lot of editing and self publishing.

Jump forward again to 2009 and I decided to try my hand at writing some technical articles for SQL Server Central. I decided on a two part series about something my company came up with for a particular issue with one client. We built a function to help us with an accounting method the client used to keep track of claim losses. The articles, 4-4-5 Calendar Functions Part 1 and 4-4-5 Calendar Functions Part 2 were published in the later part of 2009. I knew we had made some less than desirable implementation choices, but I couldn’t really find any other articles about the topic and thought I’d just get something out there. There were a lot of good comments. If you want to read the articles you’ll need a free login with SQL Server Central.

Now here I am in 2010 seeing if I can stick with writing a little more frequently. I think it will work if I can keep the topics a little shorter than a book. I’m thinking I’ll start off posting simple solutions to issues at work and see where it goes from there.

And So It Begins

Well, here I am. I try over and over again to write about things. I’ve started writing in a journal (that’s the guy name for diary) several times in my life. There is still just the one book sitting in my bedside stand; huge gaps in dates from page to page. Nevertheless, I’m now attempting to start a blog of my own. I hear the words of Yoda, “Do or do not, there is no try.” Thankfully, I am no jedi.

The plan right now is to mostly write about SQL Server related topics. As things evolve I may also write about programming with .NET or Powershell or whatever I happen to be using at the time. I may occasionally write about goings-on in my life. And so it begins.