Build Your Own SQL Server Cloud

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

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

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

First lets create a login

USE [master]

GO

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

A query of syslogins will tell 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 I really want to know.   Who created this login?  Since I do not want to write an elaborate thing to capture this information, I want to query an exisiting source: The defeault trace.  

This comes with a problem of its own.  The default trace rolls over and only 5 are kept.  The limit of 5 is not so much of a problem in my world, but not knowing when the rollover happens is.   You can query the ::fn_trace_getinfo to get metadata about these traces, but we need to know which 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 null result.  In order to find out if the trace contains the data we are looking for we must query the file.  You can use the meta data collected in the last query to get the file path.

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

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

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

SELECT StartTime, LoginName, Hostname, TargetLoginName

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

WHERE EventClass = 104

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

One of the Boys, Maybe Not

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

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

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

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

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.