SQL Server Sarah

Upcoming Events

2012-01-20T21:28:00.001-05:00

I am excited to announce that I will be speaking this Thursday at the Louisville SQL Server User Group meeting on the New Features of SQL Server 2012. The User Group meets at 5:30pm at the Duthie Center for Engineering at the University of Louisville.

I also have 2 upcoming SSWUG events

The 4-Hour DBA: Automate, Audit and Relax on Feb 15.

I will also be a part of a Security Expo on Feb 17th. I don't have a link yet, but when I do I will let you know

Basic Troubleshooting

2012-01-12T13:15:00.001-05:00

I have been a DBA for a long time and I guess that there are just some things that have become second nature for me and I forget that when people are new to SQL Server they often are overwhelmed, when there is an outage and waste time troubleshooting the wrong issue.

Recently, I was called about a SQL Server outage. Here were the facts:

3 servers were impacted
The servers could not be pinged
The servers would not allow users to login with domain credentials directly via the console.
All the servers were on the same subnet in the same rack behind a firewall
Other servers in the same rack were up and responding to requests
No changes have been made for months to any of these servers

An hour of time was was spent trying to troubleshoot this as a SQL Server issue, when it was clearly not. Do you know what the issue was? The answer is in the comments.

Build Your Own SQL Server Cloud

2011-10-19T12:52:00.000-05:00

On Wednesday, October 26 at 11am PST, SSWUG will running my webcast Build Your Own SQL Server Cloud presentation.

This presentation focuses on building a private cloud using Windows 2008 R2 and SQL Server 2008, but the concepts that I discuss can be applied to any virtualization platform. This presentation covers what is a private cloud (as well as what is a cloud) and how to achieve the benefits of cloud technologies using the builtin-features of SQL Server. I will also discuss some of the challenges I have encountered building my own cloud.

This week I have started designing the next generation SQL Server private cloud for SQL Server 2012 and this is a topic I will be blogging about more over the upcoming year.

I really excited about this webcast and look forward to talking to attendees about their ideas about the private cloud.

SQL Saturday Atanta

2011-09-17T21:24:00.001-05:00

I had a great time in Atlanta at SQL Saturday and think that the audience enjoyed it as well. I have received some emails that people are having trouble downloading my presentation. I tried to down the PowerPoint from the SQL Saturday site as well and had some difficulty. Here is a link to my presentation for those that have trouble.

Audit for New Login without using SQL Audit

2011-09-14T15:06:00.001-05:00

Because SQL Audit is only anEnterprise Edition feature, I have been thinking about the best to audit forthe creation of a login without it. I donot need to know real time when a login is created, a daily report willsuffice. This is not as simple as youmay think. I am not going to give youthe whole script, but you should be able to work it out for yourself from here.

First lets create a login

USE [master]

CREATE LOGIN [Test_Login] WITHPASSWORD=N'TestLoginPASSWORD', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

A query of syslogins willtell you that a new user has been created since the last time you checked

SELECT createdate, name, dbname, isntuser

FROM sys.syslogins

WHERE createdate > @LastDate

ORDER BY createdate DESC

However, this information does not tell me what Ireally want to know. Who created thislogin? Since I do not want to write anelaborate thing to capture this information, I want to query an exisitingsource: The defeault trace.

This comes with a problem ofits own. The default trace rolls overand only 5 are kept. The limit of 5 isnot so much of a problem in my world, but not knowing when the rollover happensis. You can query the::fn_trace_getinfo to get metadata about these traces, but we need to knowwhich trace contains the information we need.

SELECT * from ::fn_trace_getinfo(0) Where traceid = 1

This returns the following

traceid	property	value
1	1	2
1	2	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_5.trc
1	3	20
1	4	NULL
1	5	1

The property values are

1= Trace options.
2 = File name
3 = Max size
4 = Stop time
5 = Current trace status. 0 = stopped. 1= running.

The stop time would help us,but I have yet to run this query on a server or a log and not get a nullresult. In order to find out if the tracecontains the data we are looking for we must query the file. You can use the meta data collected in the lastquery to get the file path.

SELECT MAX(StartTime) as EndTIme , MIN(startTime) as StartTime

FROM fn_trace_gettable('C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_5.trc', default)

This will let us know if ourcreate date from syslogins in within this trace file. If it is we can then query the trace to getinfromation on the person who created the login. Event class 104 is the event for the creationof a new login.

SELECT StartTime, LoginName, Hostname, TargetLoginName

FROM fn_trace_gettable('C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_5.trc', default)

WHERE EventClass = 104

Another approach is to querythe default trace only, you will still have to do some date management to makesure you don’t miss any new logins between trace files.

One of the Boys, Maybe Not

2011-08-25T13:51:00.001-05:00

At the WIT Luncheon at SQL Saturday in Indianapolis, I was asked about challenges I have to overcome as a woman in technology. I have been fortunate that I have not had external obstacles, like a sexist boss or woman hating co-worker. My obstacles have been of my own making, a belief that I have to be someone I am not in order to fit in with my male co-workers. I am not one of the guys.

I started my career during the dot com boom in Austin. It was a time and place that encouraged creativity and companies were desperate for anyone who wanted to contribute. This was a great time for women in technology. All of my friends were developers, admins, and developers. When the dot com boom went bust, many of us looked for work in other places. Side note: Ten years later, I am the only one of my friends that is still in IT.

It was when I moved to Louisville that I realized what it really was like to be a woman in technology. It is lonely. I worked in a large IT organization, 3000 or more people and there were a few other women but in different departments or buildings, but I was the only women DBA. Because I was new in town, it was important to me to be part of the team and fit in with group. I wanted to go to lunch with the rest of the team and enjoy the water cooler banter. These men shared common interests that I did not even have the slightest interest, but I wanted to be included. I have no desire to go fishing, know nothing about youth athletics, nor is Hooters my favorite restaurant.

I tried so hard to be part of that boys club, but I was miserable. My male coworkers respected my work and I did enjoy friendships on an individual basis with many of these co-workers. One day I realized that my coworkers were sneaking out to have lunch without me. Of course, my feelings were hurt and no one likes to think that people are sneaking around rather than having to hang out with you. However, after that incident I stop trying to be a part of the group and eventually realized that wanting to be a part of group that I have nothing in common is ridiculous. If anything not being part of the group improved my work and career and I found my individual friendships with coworkers strengthened.

I have since moved on from this company and while I have more in common with my coworker now, I will never be one of the boys. I am girl who has girly interests and hobbies. I will still occasionally still feel a twinge when my coworkers engage in some masculine bonding over sports or cars or some other thing that I don’t care about, but it is more important to me now to be myself.

Querying SQL Audit Files

2011-06-27T14:50:00.001-05:00

I love SQL Audit, mainly because I can use it avoid login auditing going to the SQL Server error log, but that is besides the point. However, an audit is only useful if you can use the information within the audit easily. This is one negative on SQL Audit, I do not find it easy to query, but here is how you can do it.

I created an audit to log failed logins

USE [master]
GO

CREATE SERVER AUDIT [Login_Audit]
TO FILE
( FILEPATH = N'C:\Audit\'
,MAXSIZE = 1024 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'd60fca2d-14a6-4cc5-bb6c-78a9249acae6'
)
GO

CREATE SERVER AUDIT SPECIFICATION [Login_Audit]
FOR SERVER AUDIT [Login_Audit]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

What I want to know is how many failed logins happen in a 24 hour period and what logins are failing.

SELECT server_principal_name, COUNT(*) as FailedLoginCount
FROM sys.fn_get_audit_file ('C:\Audit\*',default,default)
WHERE event_time > GETDATE()-1 AND action_id = 'LGIF'
GROUP BY server_principal_name
ORDER BY FailedLoginCount Desc

Not the most elegant method for getting the information I want, but it is a starting place.

SQL Saturday Columbus

2011-05-25T12:24:00.001-05:00

Wohoo! I will be giving 3 sessions at SQL Saturday in Columbus and participating on the WIT panel. I am sure I will be tired by the end of the day. My sessions are:

Build Your Own SQL Server Cloud
Oh, no Disaster Strikes
The 4-Hour DBA

Please come to see me

Somedays it is easier to blame the users

2011-05-17T15:59:00.000-05:00

I believe that part of a DBA's job is to educate their users on proper SQL Server practices. Today, I had a situation occur where a developer rolled out a database that nearly crashed its SQL Server, because every query had a where clause that contained a wild card at the beginning of the search string.

WHERE product like '%widget%'

This meant that every call that used this pattern had to perform a table scan and caused all sorts of locking and crappy performance. I was not happy.

I started to write a "You are an idiot" email to the developer screaming about how stupid they were and why did they not use a full-text index. I started to look through my SQL Server training materials for the section where I covered full-text indexes and realized that I had never written about this. Now, should an experienced developer know that using a wildcard at the beginning of a search string is a bad idea? I think so, but I also had not done my job fully and trained the developer. This is what I believe is called a teachable moment.

Understanding database performance is not easy or everyone's databases would be super fast. Before blaming someone else make sure that you have communicated what you expect of them first. Then the next time they do something stupid, you are justified in flying off the handle.

Upcoming Speaking Events

2011-05-09T15:06:00.000-05:00

I have submitted sessions for the upcoming SQL Saturday events in Columbus, OH and Indianapolis. I am looking forward to attending both events and hopefully will have the opportunity get back into speaking about my favorite topic SQL Server again.

Public Not Granted Server Permission

2009-06-12T13:12:00.005-05:00

I have been setting up policies on my 2008 servers. I thought a good place to start would be with the suggested policies Microsoft provides with their samples. You can download the sample databases from codeplex.

I can came across a sample policy named “Public Not Granted Server Permission”, now this sounds like something a person would want to implement. I imported the policy and ran it on my test system and discovered my system failed, but I was not really sure why. The policy explains that the server role public has been granted server level permissions. OK, but what does that mean? What server level permissions? This policy does not provide an option to force compliance, so the dba must correct the issue manually, but how and if you correct what permissions will be revoked?

I started by trying to investigate the @PublicServerRoleIsGrantedPermissions facet, but that provided no greater information. Then I found this great blog post by Randy Dyess who delves into the guts of the public role. He provides a script to identify the permissions that the public role has been granted. Using this script I determined that a fresh install of SQL Server by default grants the public role the following permissions:

Name	Permission	Class	Securable	State
public	CONNECT	ENDPOINT	NAMED_PIPES	GRANT
public	CONNECT	ENDPOINT	SHARED_MEMORY	GRANT
public	CONNECT	ENDPOINT	TCP	GRANT
public	CONNECT	ENDPOINT	VIA	GRANT
public	VIEW ANY DATABASE	SERVER	Server	GRANT

Of these permissions only VIEW ANY DATABASE is the only server lever permission. I am familiar with this permission. If you DENY VIEW ANY DATABASE to PUBLIC a user will only be able to see the master, msdb and databases in which they own. This would prevent a user from seeing the other databases on the server listed in management studio. The problem with this is that I don’t make any user dbo. If a developer wanted to connect via Studio Manager they would not see database which they have been granted permission within. They can still connect to the database, but can’t see it in Management Studio which kind of defeats the point.

For my testing I went ahead and issued the following:

DENY VIEW ANY DATABASE to PUBLIC.

I re-evaluated the policy and it still failed. So on the advice of Brian Kelley, I issued the denied all connect permissions to the Endpoints.

DENY CONNECT ON ENDPOINT::[TSQL Named Pipes] to public

DENY CONNECT ON ENDPOINT::[TSQL Local Machine] to public

DENY CONNECT ON ENDPOINT::[TSQL Default TCP] to public

DENY CONNECT ON ENDPOINT::[TSQL Default VIA] to public

This worked and the policy passed. However, this is not a policy that I will be implementing.

Speaking at Louisville PASS Chapter

2009-06-02T13:20:00.000-05:00

On Thursday June 11th from noon to 1pm I will be speaking at the Louisville PASS chapter meeting. The meeting is being held at Kindred Healthcare, 680 S. Fourth Street. The topic is "Defensive SQL Server Security." I believe that Idera will be providing Pizza.

Set Number of Error Logs via code

2009-04-24T13:04:00.000-05:00

In my ongoing effort to automate everything. I wanted to have a script that would change the default number of error logs. After some looking around in help and on the internet, I realized I was going to have come up with a solution on my own. I was complaining to Ryan about having to do some real work, when he suggested that I just run a trace when I make the change via Management Studio.

Here is the result:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10

I am sure using this is not recommended, but then MS should have provided me a better method to accomplish this.

32-bit to 64-bit SQL 2008 Upgrades

2009-04-06T13:10:00.000-05:00

I am not sure if it is a symptom of my advanced age, but I am forgetting more and more. I know that you can't do an inplace upgrade from a 32-bit version of SQL 2005 to a 64-bit version, but I did indeed forget. I am running into this issue more frequently on SQL 2008 upgrades than I did with previous version, becuase SQL 2008 offers Workgroup Edition 64-bit for the first time. Many of the customers running Workgroup Edition SQL 2005 in a WOW configuration are looking to upgrade.

The only workaround that I knowis to:

Script out logins, jobs, and any objects that are stored in MSDB and Master
Detach user databases
Uninstall 32-bit version of SQL Server
Reboot Server, just to be sure
Install 64-bit version of SQL Server
Create logins, jobs and other server wide objects
Re-attach user databases

New MVP

2009-04-02T13:10:00.000-05:00

Congratulations to my friend Chris Shaw who was finally award an MVP for SQL Server. Chris has been working for years supporting the SQL Server Community and I am very happy for him.

ps. Chris is a non-douchebag MVP

Getting Back to Basics

2009-03-31T13:06:00.000-05:00

Johnny has been on vacation this week ( I hope he is having a good time, because he will never get to go on another one ever again, he does too much around here) and I have been covering much of his responbilities. Configuring SQL Server instances, fixing failed jobs, answering support questions, etc, etc. I will be honest, I do not much of this work any more. While some of it is tedious, I am getting the chance to remember why I like being a DBA so much. It is the daily problem solving. Some jobs you can work on one problem for years, while that is fine for some, I prefer many small victories. A problem arises and I can fix it move on to another problem.

When I worked at a big corporation, problems rarely happened. Which is a good thing, but it is boring. Now I work for a hosting company and I have the chance to work with a lot on database novices and new DBAs. Everyday I am challenged to fix something that has gone wrong for someone else. Personally, I get a lot of satisfaction of teaching others new things about database and solving their problems.

Disaster Recovery Horror Stories

2009-03-19T12:58:00.000-05:00

Brent Ozar is hosting a webcast today about disaster recovery horror stories and I thought back to my “favorite” horror story and how much I have learned since then. I have been working with SQL Server since 1997, but I was not a real dba until I took a job at large insurance company in 2000. At that time, a lot of large companies only trusted SQL Server to be the back end to their websites, but did not trust it to run their actual business.

This company was like many their website kind of grew up without any real structure or plan. However, the company realized that they needed add structure to their site and I was the dba on site to help roll out the changes to the database. The updates were expected to take 3 hours to complete and we got started at 10 AM. Now I was not given these updates before hand, so I did not know that updates used cursors instead of set based queries to update hundreds of tables with millions of rows.

Let me stop here and give you some background, at this time had two very large dogs. A super friendly German Shepherd mix and a pit bull / mastiff mix named Truck who weighed about 100 pounds and was very protective of me and our house. I was obsessed with these dogs, they were my life. Truck passed away 2 years ago and I still talk about him constantly.

A 3 hour update took closer to 9 hours, but then the problems started. The website no longer served pages, every page was giving a timeout errors. Of course, today I would immediately know what to do and what the problem was, but then was a different story. A conference call was created with every vp at the company on the line, web developers, hardware guys, network guys and me and the lead dba. The lead dba had less experience then I did. Everyone was off checking their own thing and no one could figure out what was happening. By about 10 hours in, I was in a panic, my dogs needed to go out and be feed. I was new to the city and did not have any friends or family to go check on my boys. The VP of my division would not let me leave, but because I was so upset he said that he would go let my dogs out. I told him that he did not understand that Truck was a very scary dog. I continued to work while the VP went to let my dogs out.

I finally got the bright idea to run a trace on the database and I noticed that most queries took 10 ms to execute but this one took 10 minutes to run and it seemed to be called all the time. Duh! I was young and stupid. This was the early days before change control made their way to sql server, so I just added a couple of indexes to this table and poof, the site loaded. The site came up, but that was about it. It was performance nightmare. About this time, is when the VP called from outside of my house to say he was not going in, but found a neighbor kid who did yard for me to brave it. Truck knew the neighbor kid and let him in, but hated that VP.

So I worked for 16 hours running traces, adding indexes until the site worked. After that I was the human performance tuning wizard.

The post script to this story is the actual worse disaster I had, but is far from my favorite story. I had scheduled to apply SP2 on one of our large SQL 2005 one Friday night. We had tested SP2 and applied on about 15 servers prior to this install I did not anticipate any issues. Because we have international customers our maintenance window is Saturday morning at 2 AM, a time I hate. Sleepy DBAs make bad decisions, but it is part of the job. Before I started I took a full system backup, and then ran the SP2 installer. Some of you may have encountered the issue with SP2 where it failed due to a previous installation. So I went out found the Windows installation cleanup tool and removed anything I thought may be the problem. I reran the SP2 installation and it installed without issue. I thought I was finished and just rebooted the server. But the server would not come back up, the master database was corrupt. How , Why, I am not sure. So I try to restore from my backup, but I am unable to because I was getting an error that the backup was not readable. I knew then that I was in for a long night.

Another back story, Truck, my beloved dog had cancer. He was undergoing chemo and I had hoped that he was doing better. I knew there would be no cure, but still I hoped. The day of the upgrade I noticed that he was a little lethargic, but that sometimes happened after chemo.

Truthfully, I don’t remember exactly what I did, but I was able to get the server backup. I think I restored the master database backup on another server as regular database and then made a backup of the restored database. This new backup worked, but then I found out there were problems with MSDB. At this point, I had been up for 24 hours and was exhausted. So I made the call to go home get some sleep and then deal with the MSDB problem the next day.

The next day, Truck was still lethargic, but truthfully I was so consumed with my broken server that I did not pay much attention. I continued troubleshooting the msdb issue, but made the decision to scrap the entire install and reinstall SQL Server on new hardware, it was just screwed up. I scheduled this to happen Sunday night at 10PM.

Thankfully, the re-build went off without incident and the server was backup and running. The next morning, a new DBA (Ryan) was starting so I would have help and not have to deal with this kind of thing alone in the future. I went home and discovered that Truck had taken a turn for the worse. I rushed him to the emergency vet and stayed most of the night up with him. I called our CTO and asked him to welcome Ryan and get him settled, because I had to take Truck to cancer vet first thing in the morning.

The vet told me that he would need to run some tests on Truck and that I should go into work. So I went to work welcome Ryan and started to deal with the fallout of the weekend. A few hours later the vet called and told me that Truck was bleeding internally and that it was time. I left work to say goodbye to Truck and then went home where I planned to stay for the next few days mourning the worldest greatest dog. However, the life of dba is not always convenient.

On the day my beloved Truck died, I had to go back into work to help a customer who had encrypted all their data using the server master key, which we do not support and do not backup. When the server was rebuilt, a new master key was generated. I am no encryption expert and I was in no mood to deal with this situation, but Ryan had been on the job less than 6 hours and I was the only dba, so I called up Microsoft to help me try to recover the master key from the old server which was not easy. On the worse day of my relatively young life, I spent on the phone with pss.

When things wrong, it is rarely convenient.

The MVP Summit

2009-03-09T12:57:00.001-05:00

I love twitter. I enjoy reading the tweets of my follow SQL Server professional expert and the regular guys. I learn a lot and enjoy commisserating with my fellow professionals on the pains of the dba profession. However, this week twitter is annoying me. Microsoft awards many of the top SQL Server Experts with the title of MVP, which entitles them with all sorts of benefits and perks. I will admit that I would like to be MVP and have access to all sorts of information and resources, but I do pretty well now with the resources us regular folks have access. This week is the MVP summit, where all the MVP's go up Redmond and learn new secret things before the rest of us. I knew this event happened in the past, but now thanks toTwitter it is being thrown in my face. It is kind of a douchebag move to twitter "I am learning all this really cool stuff that I can't share with you because you aren't an mvp"

Performance Advisor Saves the Day

2009-02-23T13:02:00.000-05:00

One of the most annoying questions i get is "My application went down last Tuesday aroun 9 AM, what happened?" I often want to reply "How should I know? How about calling me when it happens." But I don't, because I am not a complete jerk. Now I can answer that question because I have Performance Adivsor.

What happens is that customer will notice a problem with their application and check their error logs, the error log will give the dreaded "SQL Server Timeout Error" and logically the customer will think there was a problem with the SQL Server and contact me. In reality, the problem is almost never a SQL Server problem, but an application issue. Proving this another matter.

First of all the SQL Server does not timeout, the client connecting to the SQL Server is actually timing out, as I do not set a maximum timeout on the sql server. In the past, I was limited to error logs and the default logs to try to determine what was happening on the server at the time of their time out. Not much help. If there was a major issue it will appear there, but if there was a major issue I would already know about it. I could not tell them what was happening specifically with their database, perhaps their was a lock that caused the query to take longer than normal?

When SQL Sentry told me about their new product Performance Advisor, I was very excited. I bugged them for months to get my hands on it. Now the product can do all sort of cool real-time monitoring, but in reality I can't keep up with that as I just have too many servers. I use it to tell me about what happended in the past and about trends. I love being able to provide our customers with real information to help get to the root of the timeout. I can say at that time of your time out the server was using X% of CPU and your database executed the following statements and it took X ms to complete. It makes me look better and I can actually answer the what happened question.

Truthfully, I don't find many third party products useful, but this one I do like.

Backup Resources

2008-04-01T19:14:00.005-05:00

I get lots of questions from customers on how to setup backups on their servers. I have put together a short list of backup strategies for the beginner

Back at it again

2008-03-10T13:10:00.003-05:00

I am going to try the blogging thing again. I used to be so good at it, but I am going to try to make it a habit. We will see.