Category Archives: SQL Server

All SQL Server posts that don’t fit in other categories

T-SQL Tuesday #50: Automation for LazyDBAs!

T-SQL Tuesday is a monthly blog party hosted by a different blogger each month.

T-SQL Tuesday 50 - Automation

T-SQL Tuesday 50 – Automation

This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month. This month, SQLChow blessed us with a topic I am very passionate about. This months T-SQL Tuesday topic is automation.

I have a confession to share. I am a Lazy DBA. Those who know me won’t be shocked by reading this. Those who don’t know me. Trust me, I mean this in a good way.  My lazyness over the years has actually motivated me to be a better DBA and data professional.  I learned early on in my career that in order to be productive I must automate. No longer can we do manual daily checklists. We lose several hours that could have been spent on tasks that show our value not just hold the status quo. Automation allows us to end the cycle of repeating tasks and allows me to spend that time doing things that provide value, save the company money and make us happy.

Early Stages of Automation

Once in my career I was blessed with an opportunity to be a Database Administrator overseeing thousands of production databases. Quickly, I noticed there was no automated process for a daily checklist.  How did we know if a database backup failed due to low disk space? Hopefully, we got an email from the SQL Agent. Hopefully, someone remembered to setup an SQL Agent notification. I knew this wasn’t the answer. One of my first tasks was automating this whole process so we knew which databases passed and failed an automated daily checklist. I was able to leverage Policy Based Management (PBM) and Central Management Server (CMS) with Powershell to get this done. Little did I know it at the time, but this basic move changed my DBA Career. I got to speak at the PASS Summit in 2011 on how I evaluated my automated daily checklist against 1000+ servers during my morning coffee break. Starting to focus on performance I noticed a better way to pull this information without PBM. I build an automated framework using Powershell and CMS to automate the process to get my failed backups quicker. Still today meet DBA’s today who didn’t know you can automate your daily checklist only using native tools built into SQL Server.

Current Stages of Automation

Today, I am much more focused on performance and proactive monitoring. Learning from my past I knew I wanted to automate as much as possible. This didn’t change even though my core skills were changing.  In the past year I built some nice automated solutions that help me with performance tuning.  When I am in charge of a new instance I automate the process of monitor disk latency, proactively automate the process to monitor wait statistics. Once I have a good automated baseline I can drill deeper as needed. For example, I can find out which queries are causing my waits. It has gotten me to the point where most of the time I can find the root cause to SQL Server performance problems in ten minutes. The automated benchmark process does the heavy lifting for me so I can respond and stay as proactive as possible and provide value instead of running processes that should be automated.

 Future State of Automation

I see more things being automated.  More and more parts of the current “Production DBA” role as we know it today will be automated. This is going to open us up to doing amazing things. One day, an end to end performance tuning process will be automated.  I look forward to seeing things that we thought were not possible be possible and automated. For example, automating server procurement and deployment once fell into this realm. Now, it’s already here. It’s known as “the cloud”. I will be honest, I was shocked to see how easy and quick it is to deploy an Windows Azure Server.

What are your thoughts about automation? Where do you see it going in the future?

#TSQL2Day – A Day in the Life of A Service DBA

Today I am excited to join in with my #sqlfamily and participate in this months #tsql2day throw down. This month Erin Stellato is the king pin and her topic is “A day in the Life.” This gives me a great opportunity to explain a day in the life of a DBA who works for a company who provides DBA services remotely. A typical day has me working on several projects or problems with several clients. One of the things I enjoy about my job is that each day has a new set of challenges.  The following are the highlights of my work day on Wednesday, July 11th.

Backups instead of Coffee..

You might start your morning with a cup of coffee but I got to start my day with working with a new client going over an very important issue. We noticed holes in a clients backup maintenance plan so I got to work with them to correct those holes.  I know this isn’t the sexiest task out there for DBA’s but making sure that you can recover is one of the most important tasks  for a DBA.

Configuring A Cluster to improve High Availability.

Implementing a cluster usually gives you higher availability for your databases but you might not be getting the highest availability possible from how you configured your cluster.  Today, I reviewed a four node cluster and found a few flaws that limit the availability of the cluster. To summarize here are some of my findings. The cluster was running in node majority instead of node and disk majority. Instances of SQL Server were not added on all the nodes. The preferred nodes and possible owners were not configured correctly. I look forward to doing another blog post at a later date going over each of these settings.

Troubleshooting Version Store

Today,  I got to work with another team mate to help troubleshoot why the version store was filling the tempdb database and causing slowness with the clients application. This gave me an opportunity to go learn more about what all is used by the version store. Noticing that the client used RCSI for their main database we were able to use the Performance Data Warehouse to find a massive update statement that did more writes than expected which caused the growth problems with tempdb.

Assessment review with Client

One thing I enjoy about my job is going over assessments with a client. This usually gives me an opportunity to share some knowledge about how the database engine works. I also gave some recommendations that can be used to improve their performance and how to bring their server up to speed with our best practices.

Favorite Part of the Day!

Every day I get to eat lunch.  I try to make sure I have a lunch date with #babybeluga Gregory my one year old son. This is by far my favorite part of the day and it reminds me why I love working from home.

Well, there you have it. I hope you enjoyed my blog post on A Day in the Life. I look forward to seeing the recap of how everyone else in the SQL Community spent there day.

Stress Testing Stored Procedures with Free Tools

I currently have a presentation named Performance Tuning for Pirates. This presentation goes over using several free tools to help you with your performance tuning process. Today, I am happy to share my first video going over these tools.

Using SQLQueryStress to stress test Stored Procedures

SQLQueryStress by Adam Machanic (blog | twitter)  is one of my favorite tools for doing a quick stress test against a user object like a stored procedure. This tool can actually be used to do stress testing on T-SQL queries but I usually use it for compiled objects like stored procedures.  I love this tool because it allows you to save and load configurations to quickly redo tests. It also allows you to use a dataset for input parameters and allows you to add additional stress with configuring iterations and threads.

Note: It also comes in handy when you need to mock up some stress for demos.

I also written a tip for www.mssqltips.com that goes over a step by step guide on how to use SQLQueryStress.

Free Training: Performance Tuning with Free Tools!

This week I have two presentations on my schedule. I get to give my Performance Tuning for Pirates presentation twice this week.

Pittsburgh SQL User Group Recap

On Tuesday, I presented my Performance Tuning for Pirates presentation at the Pittsburgh SQL Server User Group. Actually, I had a little tweak that went well. One of my friends who does a lot of tuning has alway been interested in doing a presentation so I had him jump on stage and do the presentation with me. I think it was a great success and I think we will be seeing some really cool presentations from him in the future.

Performance Virtual Chapter

Today at 2PM (EST), I am also giving my Performance Tuning for Pirates presentation at the SQLPASS Virtual Performance Chapter. This will be done via LiveMeeting and is free for all PASS Members. If your not a member, I have good news for you. PASS Membership is free so signup and join in on the fun. Also, if you are not able to make it today make sure you come back to the virtual chapter as this session should be recorded for replay.

If you are looking for the resources, tools used in the Performance Tuning for Pirates presentation you can find them here.

24 Hours of PASS – Get your free downloads!

I am speaking at 24 Hours of PASS

I am speaking at 24 Hours of PASS

Thank you to everyone who attended my #24HOP session on Performance Tuning for Pirates! You can now download my slide deck, t-sql queries, and view my reference links.  If you have any questions about the tools presented feel free to contact me and I will  try to help you out or point you in the right direction.

 

8 steps to moving database users from SQL 2005 to SQL 2008

Have you ever had to complete an upgrade where you had to uninstall SQL Server 2005 and install SQL Server 2008 on the same box? If so, how do you make sure the new install had the same security objects?  For me, the answer was lets be safe instead of being sorry.  I would script out all the existing server and database security objects so they could be verified and implemented if needed.

Not only is this a good practice for migrating your servers but the following post could be useful for a security audit.   I also want to mention if you are allowed to install tools on the box you can download SQLPermissions its a free tool provided by Idera to generate a script to move security objects during a migration.
[Update 1/28/2012 – USER_NAME() was replaced with SCHEMA_NAME() in the Explicit Permissions section]
** Run scripts at your own risk. I don’t assume any responsibility for these scripts on being ran in your environment. As always, I recommend testing in development before running in production 🙂 **

Logins

The very first step to connecting to an instant of SQL Server is to connect or login. In order to connect you must have a login. Therefore, our first step will be to actually copy the logins. I know, you might already be wondering how do I get the passwords moved over for SQL authenticated logins? Microsoft has a KB article (918922) that walks you through this process. Make sure you checkout the remarks section before running the code on a production box.

Server Level Security

Logins are nice but actually useless unless they have access to connect. Therefore, we might want to allow these logins to connect. You might also want to make sure a subset of the accounts  have access to view the DMV’s. This and other server level permissions can be scripted out with the following code below.


SELECT	sp.state_desc,
	sp.permission_name,
	principal_name = QUOTENAME(spl.name),
spl.type_desc,
	sp.state_desc + N' ' + sp.permission_name + N' TO ' + cast(QUOTENAME(spl.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) AS "T-SQL Script"
FROM sys.server_permissions sp
inner join sys.server_principals spl on (sp.grantee_principal_id = spl.principal_id)
where spl.name not like '##%' -- skip PBM accounts
and spl.name not in ('dbo', 'sa', 'public')
order by sp.permission_name, spl.name

Server Level Roles

Now that we can login we might want to take this to the next step. We may actually like to perform some actions. Well, if your instance includes third party application databases there could be a good chance that it depends on its login having sysadmin access. Yes, in case you were wondering this is poor security design but the point is your logins may depend on server level roles to have access to the database objects.  Here is a script you can use to script out server level roles.

SELECT  DISTINCT
QUOTENAME(sp.name) AS "ServerRoleName",
sp.type_desc AS "RoleDescription",
QUOTENAME(m.name) AS "PrincipalName",
m.type_desc AS "LoginDescription",
'EXEC master..sp_addsrvrolemember @loginame = N''' + m.name + ''', @rolename = N''' + sp.name + '''' AS "T-SQL Script"
FROM sys.server_role_members AS srm
inner join sys.server_principals sp on (srm.role_principal_id = sp.principal_id)
inner join sys.server_principals m on (srm.member_principal_id = m.principal_id)
where sp.is_disabled = 0
and m.is_disabled = 0
and m.name not in ('dbo', 'sa', 'public')
and m.name <> 'NT AUTHORITY\SYSTEM'

Database Level Security

Now that your logins can connect you need to make sure that they can connect to the databases you migrated over.  Remember those third party apps that skipped the process of doing a  security design?  They will use sysadmin access so database level security will be included.  Hopefully, your applications are not one of those. Just like server level security you can also have database level security. Make sure you run the script below and save your results.

SELECT  dp.state_desc,
dp.permission_name,
QUOTENAME(dpl.name)  AS 'principal_name',
 dpl.type_desc,
 dp.state_desc + N' ' + dp.permission_name + N' TO ' + cast(QUOTENAME(dpl.name COLLATE DATABASE_DEFAULT) as nvarchar(500))  AS "T-SQL Script"
FROM sys.database_permissions AS dp
INNER JOIN sys.database_principals AS dpl ON (dp.grantee_principal_id = dpl.principal_id)
WHERE dp.major_id = 0
and dpl.name not like '##%' -- excluds PBM accounts
and dpl.name not in ('dbo', 'sa', 'public')
ORDER BY dp.permission_name ASC, dp.state_desc ASC

Database Level Roles

Just like the SQL Server instance your database also has roles.  Many times I will see applications that don’t require sysadmin on the instance but do require db_owner access in the database.   This is also a bad security design practice as db_owner can do anything against that database. Hopefully, your application has object level explicit permissions as needed. Once again, I would recommend pulling this information in case its needed after your migration.

SELECT DISTINCT
QUOTENAME(drole.name) as "DatabaseRoleName",
drole.type_desc,
QUOTENAME(dp.name) as "PrincipalName",
dp.type_desc,
'EXEC sp_addrolemember @membername = N''' + dp.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + drole.name + '''' AS "T-SQL Script"
FROM sys.database_role_members AS drm
inner join sys.database_principals drole on (drm.role_principal_id = drole.principal_id)
inner join sys.database_principals dp on (drm.member_principal_id = dp.principal_id)
where dp.name not in ('dbo', 'sa', 'public')

Database Level Explicit Permissions

Finally, we will get to the area of security where I hope to see many entries. If someone, tasked me with designing their the security model for their application I would make sure stored procedures controlled the data access and that application users could only execute the stored procedures. This way there would be very minimal risk of data loss as the users wouldn’t have direct access to the tables. They would only access data needed by the business logic provided in the database objects. You can pull this information from the code below.

SELECT    dp.state_desc AS "StateDescription" ,
dp.permission_name AS "PermissionName" ,
SCHEMA_NAME(obj.schema_id) AS [Schema Name],
obj.NAME AS [Object Name],
QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)    + CASE WHEN col.column_id IS NULL THEN SPACE(0)           ELSE '(' + QUOTENAME(col.name COLLATE DATABASE_DEFAULT) + ')'      END AS "ObjectName" ,
QUOTENAME(dpl.name COLLATE database_default) AS "UserName" ,
dpl.type_Desc AS "UserRoleType" ,
obj.type_desc AS "ObjectType" ,
dp.state_desc + N' ' + dp.permission_name + N' ON '    + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)    + N' TO ' + QUOTENAME(dpl.name COLLATE database_default) AS "T-SQL Script"
FROM    sys.database_permissions AS dp
INNER JOIN sys.objects AS obj ON ( dp.major_id = obj.[object_id] )
INNER JOIN sys.database_principals AS dpl ON ( dp.grantee_principal_id = dpl.principal_id )
LEFT JOIN sys.columns AS col ON ( col.column_id = dp.minor_id  AND col.[object_id] = dp.major_id)
WHERE    obj.name NOT LIKE 'dt%'
AND obj.is_ms_shipped = 0
AND dpl.name NOT IN ( 'dbo', 'sa', 'public' )
ORDER BY    dp.permission_name ASC ,    dp.state_desc ASC

Orphaned users

If logins are not brought over correctly you may notice that they are not mapped correctly with the user account that exists in the database security (Logins section above should catch this).  The following is an explination that comes from BOL.

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be anorphaned user of the database on that server instance.

You can find and fix orphaned users by following the instructions provided here.

Validate User Accounts

Finally, yes when possible lets make sure the user accounts work.  I know this might be common sense but you don’t want to cut something over with out testing and verifying connectivity.

2011 was a crazy year!

Now that we are finishing up the last few days of the year its time to take a few minutes and reflect on my accomplishments for the year. To be honest, looking back at the blog posts I have no idea how I completed so many mile stones.  Last year I privately established some long term goals and I knocked several items off my three year and five year plan in the first year.

Major Accomplishments in 2011

PASS Member Summit 2011 Ribbons

Vendor said, "Wow... it looks like you had a very big year. Would you like to attend our vendor party?"

My first child was born in February.  Typically having a child would slow you down but somehow I still completed several milestones. I have to give huge props to my wife for all her help and support.

This year I gave presentations at the first SQL Rally; the SQL PASS Member Summit; SQL Saturday in Houston, TX (presented multiple sessions); the Pittsburgh SQL User Group and several session at the West Virginia SQL Server User Group.

One of my good friends and I co-founded the first SQL Server User Group in West Virginia. I also helped relaunch the Pittsburgh SQL Server User Group and was truly honored to  speak at the relaunch event. I also organized the 2nd  Annual SQL Saturday in Wheeling, WV. Its was truly an honor to bring my peers and friends to my hometown for some free SQL Training (photos). Towards the end of the year I also became a PASS Regional Mentor for the Mid-Atlantic Region.

Personally, I also had the following accomplishments.  I was included in a local

Reading with my baby

It's never to early to get started on your professional development.

magazine article on “Digital Generation.” Finally, I changed jobs and became a SQL Server Consultant.  I am truly blessed to say that many of these milestones couldn’t happen without my #sqlfamily and  my tech giants mentors .

My 11 Favorite Blog Posts in 2011

The following are my favorite blog posts and tips written by me in 2011.

I look forward to sharing some goals next week as we break in the new year!


PASS Member Summit 2011 Speaker Evaluations

Session evaluations from the 2011 PASS Summit have been officially released to speakers. I am proud to share my feedback as it gives me room for improvement. I look forward to making it back in 2012.

The evaluation format is as follows. Each question is rated on a scale of 1-5 (1 = Very Poor, 2 = Poor, 3 = Average, 4 = Good, 5 = Excellent) with room for additional comments if the attendee  felt compelled to add more information about why they gave a particular score.

Here’s how I fared:

Topic: Evaluate your Daily Checklist Against 100+ instances of SQL Server while you get a cup of coffee
Attendance: 138
Total Responses: 68
Average Rating: 4.4 out of 5.0

How would you rate the Speaker’s presentation skills? 4.19

  • very good clear speaker good pacing
  • it seemed like he did a lot of reading from the notes. he’s off to a good start though
  • alittle more zoomit. overall very good & easy to follow
  • He seemed pretty nervous, but he soldiered on.
    He also used ZOOMIT when appropriate and diligently repeated the questions for the recording.
  • good use of demos

How would you rate the Speaker’s knowledge of the subject? 4.51

  • great command of content
  • sounds like he has set PBM/CMS up a few times at least…knows the stuff
  • Another 10-15 minutes would have allowed for a little deeper explanation/understanding of the material, but it’s Friday and my brain is almost completely fried. (You have to love the honesty in this one! )

How would you rate the accuracy of the session title, description and experience level to the actual session? 4.49

  • I didn’t see any incorrect information
  • can’t wait to suss out some of the code snippets
  • demos clearly showed topic & idea

How would you rate the quality of the presentation materials? 4.35

  • demos clearly showed topic & idea
  • too simple…i would rather go over good primalry mangt. the tool i can learn myself..maybe less demo more presentation
  • wish we had more time on this subject
  • can’t wait to suss out some of the code snippets

Did you learn what you expected to learn? 4.47

  • thank you for the kick start into cms
  • i had no idea what PBM was now i know and want to apply it.
  • currently only rely on failure emails…no know of a better way
  • demos clearly showed topic & idea

What will you take away from this session?

  • Some good stuff. I’ll be beefing up my PBM when I get back.
  • Great info!!
  • I will be implementing what we learned
  • Implement PBM and CMS
  • hoping to implement some checklists on our newly built comps and streamline our “care and feeding” process
  • go back to office and try it out
  • make a list, check it, automate, central management, server
    policy based management
  • impliment idea. a way to automate checklist & protect my environment practically
  • cant wait to try!
  • how to use PBM & CMS. great session!!
  • a daily checklist can be automated and easily analyzed with a few simple tools

What would you change to improve the overall quality of this session?

  • Provide handouts
  • Presenter could have managed off topic questions from audience better.
  • speaker peppered with quite a few “in the weeds” questions, but managed them well.
  • could use alitte humor
  • change this to a 300 level based
    leave questions until end too many people aske the same questions over again, over something that was covered

Conclusion:
I think I did a good job for presenting my first full session at the PASS Member Summit.  Several people mentioned in the comments that they were going to try PBM and CMS.  This completed my mission for giving the presentation.  I also received some great feedback that I will implement to make my presentation better. I think this was a great experience for me and I look forward to doing it again next year if I get selected.

Speaking at SQL PASS Member Summit 2011

Last year I attended my first PASS Member Summit in 2010.  After attending the conference I made it a goal to make sure I return.  You see, not only was this the first time I attended but also the first time I was a presenter.  Last year I gave a lighting talk on how to use profiler and a chalk talk on SQL Server 2008 for Developers.

Two weeks ago I got a very cool email.  It asked if I would be interested in being an alternate speaker.  The answer was simple.  Hell yea! I would like to speak at the biggest and best conference for SQL Server Professionals.

My topic is Evaluate Your Daily Checklist against 100+ Instances While You Get A Cup Of Coffee. I will also give a lightning talk on Hosting A SQL Saturday.

Reciently, I downloaded screen flow. I hope to give everyone a teaser video to my session next week.

Speaking at WV SQL User Group on August 25th

I look forward to giving my Evaluate your Daily Checklist against your SQL Enterprise with PBM and CMS presentation at the WV SQL Server User Group in Wheeling, WV on August 25th at the Orrick Building.
You can still sign up and registration is free.  In fact, everyone is a winner because there will be free pizza and every user group attendees knows there is no better prize than free pizza!

Abstract

You will walk away from this session with an understanding of how to use the Policy Based Management and Central Management Server to complete a daily checklist against your whole SQL Server farm. We will cover the basics to evaluate, monitor and apply best practice policies, which are included out of the box with SQL Server 2008. We will also make custom policies to cover additional best practices to evaluate daily checklist items. Finally, we will combine Policy-Based Management and Central Management Server with the Enterprise Policy Management Framework on codeplex to automate the monitoring process and create manager friendly reports to monitor your checklist items against the whole SQL Server farm.

Session goals include:
  • You will be able to install, configure and run scripts against the Central Management Server
  • You will be able to use Central Management Server and/or Policy-Based Management to complete a daily checklist
  • You will be able to evaluate and build custom policies.

Bio:
John Sterrett is a database administrator at Remote DBA Experts and a member of the Integrations team. John has been active within the SQL Server community, including hosting the first and second annual SQL Saturday in West Virginia. John also presented a lightning talk and chalk-talk at the SQL Server 2010 Member Summit, presented sessions at three SQL Saturday’s, and presented to the Pittsburgh SQL Server User Group. John is a syndicated blogger at SQLServerPedia and contributes tips at MSSQLTIPS. You can find his blog at www.johnsterrett.com