Page Life Expectancy – if you don’t know this stuff, don’t call yourself a senior DBA, please….

Hi all,

yesterday I was once again asked by a fellow ‘senior’ DBA I know, about the 300 second guideline for the PLE (Page Life Expectancy) counter. It never really ceases to amaze me how many people in our community are still not aware on how to interpret this value and be able to calculate a proper value for their specific situation. So to help everyone with the basics, the following link points to the blog posting by Paul Randal of SQLSkills.

In this posting he explains what this counter is really about and how it should be interpreted. This includes the info on how it relates to NUMA nodes. This is pretty essential info, so don’t skip this part and make sure you understand it.

Oowww and don’t forget, NUMA is something that will bite you in the ass (performance-wise) if you don’t know how it plays a part in your environment. Especially if you are running SQL in a virtual setup, talk to your virtualization admins and know how your servers (hosts) and VM (guests) are configured. But I will get some more info about that and what to look for, in a future posting.

Page Life Expectancy – if you don’t know this stuff, don’t call yourself a senior DBA, please….

SQL Server Service Accounts

So much has been said about service accounts for SQL Server, by so many people. So I figured I might chip in with my 2 cents.

Lets start with some background. First, what is a service account. It’s simple, it is the account a Service is running under. Basically the user that starts the software. If you log on to a desktop and start calculator, the service account for CALC.EXE (the process of the calculator) is running under the account you logged onto the system to. The makes it a service account in essence. However when you close the calculator this process stops and you it is gone. With SQL Server you do not want to process to stop when you close your screen or log off from the server. to help with this Windows uses so called services. These are processes that run in the background (as a service) and can start automatically when de system (e.g. a server) starts up. So far the basics… Easy right? So how does this relate to SQL Server.

SQL Server uses multiple services. The database engine uses SQLServ.exe, SQL Server Agent uses the SQLAGENT.exe and various other processes depending on the features of SQL you have installed. These can be seen in your taskmanager under the processes tab. But a process is not a service you might say. That is correct. Services are abstraction that might call upon multiple processes. So if you look on the Services tab in taskmanager, you will also find a service called ‘MSSQLSERVER’. This is the calling service of the process called SQLSERV.EXE. On the process tab you will also see a column called user. In this column you will find the account under which that process is currently being run and if this process has been called by a service, the account listed here is the service account. So now you know what it is and where you can find it… All fine and dandy, but why are service accounts important?

Well, since a service account starts a process it needs certain rights on your server to do that. And because SQL Server processes do stuff with files that exist in Windows it will not only need to be able to start a it also needs access to those files and be able to modify then (adding rows and such…). So it needs access to the filesystem to write stuff in a file (datafiles) and it needs to be able to create new files when you create a new database, etcetera, etcetera…. This service account needs a rights in your Windows environment and that means we need to start looking at security. So one solution is to run all the SQL Services under the a local windows account. Which is possible but not very manageable. Server the need to access data on other server, need to be given rights and that can get messy and expensive to maintain. If you have a few server it is not a huge problem but when you are managing 30 instances of a few 100’s, that it gets really annoying to maintain al those access paths. Also there is a risk that the local service account has too many rights on that server (and it does!). This will potentially expose your data to other processes on the same server that might be running under the same account (local system for instance). So you can imagine that using properly managed service accounts can help a lot with reducing risk for dataloss, reduce management overhead, reduce downtime and in general reduce the attack surface to your SQL Server and more importantly your data. So now we know what they are, where they can be found and why using proper service accounts for SQL Server is so important. Now lets bring it all together and talk about some Best-Practices for SQL Server Service Accounts and what can happen if you do not follow then (yes, the fun horror stories will be told here).

First up, Microsoft publishes a lot of information about service accounts for SQL Server. They do not do this because they like to publish, but because it is important (and shows off their knowledge). At the bottom of this article I will list a few links to relevant info. Do yourself a favour and RTFM on this topic because if you get this wrong you could end up on the front page of a newspaper and subsequently in the line of window 4 at the unemployment office.
Secondly, security is every bodies business in the modern office. Do NOT wait for someone else to address things when you notice something that could have potentially catastrophic consequences. Raise hell if you need to because if the business goes under due to a massive dataleak or loss, you will be out of a job too!

Now for my recommendations on service accounts.

  • Read the Microsoft Best Practices for SQL Server Service Accounts. They build the product, they know the product, they are likely more knowledgeable about the product then you and I put together! So please read what they have to say about the topic. (LINK)
  • Use accounts with only those rights that it needs and no more, so-called minimum rights. (LINK)
  • Do NOT use the same account for multiple services or on multiple servers. For each service of each instance on each server, use dedicated accounts! (LINK)
  • Use Active Directory based accounts (Managed Service Accounts) whenever possible. Central management of rights and passwords, anyone? 🙂

Now for the fun part, the horror story.

Too much rights for a service account and no separation between servers
(true story of personal experience).

I was once at a client where they used 1 service account for all services related to SQL Server. This service account was ‘sort of managed’ as in, it was in the AD. HOWEVER, what I regularly encounter is that service account for SQL Server are given WAY too much control…. In this case the administrators gave this service account a membership on the group that has LOCAL ADMIN privileges on ALL servers. Not just SQL Server, but ALL servers in their entire enterprise. This same group was then given a membership on the System Administrators role within all SQL servers. The combination of these 2 basically means I I wanted to do harm, I could do whatever I like to whatever server I want and no-one can stop me. I noticed this and as a good responsible consultant should, I informed the manager of the risks involved with this choice. He informed me that this was a common practice in the SQL Server community (at which point I wanted to run headfirst into the nearest concrete wall but did not…). I remained professional and explained it was not a common practice and it exposes his entire IT landscape to a direct shutdown of all servers, a reboot of certain servers, the dumping of all his databases or even worse the manipulation of all stored data in said databases. Now things got a little ugly…. He did not believe me (that’s fine, not my servers not my career….) and said I should proof it because I was wrong and no such risks exist…. In the following few minutes thing went from bad to worse and he basically called me a liar, a fraud and if I did not proof this he would not pay my fees and I could stuff the bill in that deep dark place that never sees the sun. By this time I was about done with him so I told him to email me his order to proof it in his production landscape, CC his manager in this and have them approve his order also (I don’t mind taking risks, but he was playing Russian roulette with a .45 automatic so taking out some insurance against his idiocy was warranted). I got the mails and approvals I wanted and I went to work…. Sidenote, who knows how to start xp_cmdshell runtime???  I do…. he did not know!! To make a long story short I used a very small script that uses a combination of xp_cmdshell (access to the OS), osql.exe (get a list of all actively advertised SQL server in the subnet) and some clever logic to combine that list with a shutdown command, and guess what…. within about 5 minutes all his SQL Server were offline.!

Moral of the blog: do your job right, read what you should read and make INFORMED decisions about risks because if you don’t, you will find yourself waiting in line 4 of the unemployment office.

Oowww and in case you are wondering, the manager in question was requested to find employment elsewhere in the weeks after this incident and I was requested to review their deployment methods and service account usage. 🙂

SQL Server Service Accounts

SQL Central Management Server (SQL-CMS)

Ever had to document 20+ SQL instances by yourself?  If so, you will recognize the agony of having to run the same script over and over again just to get the same info from all your instances. I have worked at clients with 200+ instances and little or no documentation. It can be a true headache.

Fortunately the fine folks of the Microsoft have had the same problem (I used to work for Microsoft and believe me their SQL Server landscape is HUGE!!) and someone (Buck Woody, I think) created and implemented a nice solution to cope with the ever increasing numbers of SQL instances. This too little known feature is called the SQL Central Management Server or SQL-CMS for short.

What is it…. Well is it a way to centrally administer the connection strings to instances in your landscape. These connections are (securely) accessible by other DBA’s or developers and will help you get and keep control over your landscape. Is that it… Nope, it is much more…. Once you have set it up and registered and grouped your SQL instances you can actually connect to 1, a group or all SQL instances in your CMS with just 1 click. It will open a new query window in the SSMS but this window is connected to all the servers in the CMS group you connected to. As you might imagine, this will come in very handy when collecting information about your instances, servers, databases, etc…. Oowww is that all. Well no it’s not… you can also do some really nice other stuff. For one you can also use the power of policy based management and evaluate your entire landscape on the correct use of e.g. the auto-close setting for databases. Or you can run all the Best-Practices policies (they come with the Dev and Enterprise editions) against your landscape… Hhhmmmm now things start getting interesting, right….

A few things to think about…. Does it do everything you might want? Nope! The security model to protecting the registered server in the CMS is limited. You either can see them all, or you can’t see any. There is no option of limiting access to a certain group of server in the CMS. Is that unfortunate yes, but it is no problem. Just make sure you have the proper security set up on your instances and you should be fine. Another thing to be mindful of. It only works with Windows Integrated Security. Again not really a big problem in most cases, but you will always run into that one exceptional client where they do not use an AD. Oohhh well, nothing is perfect right? 🙂 Still it has proven it’s usefulness to me on more then one occasion and the best part is… it is free!!! It comes with all versions of SQL, even the Express version. I know, I know, there are other products that do pretty much the same thing but most of those cost money and don’t integrate into the Management Studio.

In my opinion this is one of the most underrated features of the SQL Server product. It is free, useful, easy to setup and flexible enough to tune if you would want to. So why not look at it and play around with it. I promise you it will be worth it!

More info:

SQL Central Management Server (SQL-CMS)

Coming soon…..

Since this is a new blog and I would not mind getting and KEEPING your attention I figured it might be a good idea to give you a small impression of my blogging plans for the coming time.

In the coming weeks you can expect blogs on my a few of my favourite features of SQL Server and some other stuff. The planning so far is:

  • SQL CMS (Central Management Server)
  • SQL Server Reports and DMV’s
  • POSH (PowerShell) for SQL DBA’s
  • SQL Server standardization of deployments
  • SQL Server Service Accounts, what not to do and why not to do it!

These might not all be cutting edge, but they certainly warrant some renewed attention.

I hope these topics will keep you interested and entertained, and if not… too bad. 🙂 However, feel free to message me with any suggestions for blogs. I would be happy to share my thoughts and knowledge.

Coming soon…..

SQL Server Tools and Add-ins

Is it bad form to reblog a reblogged post…. Ooohh well…. Clint reblogged this posting about some useful SQL Server Tools and add-ins. I thought you might like it too.

Clint Huijbers' Blog

I stumbled upon a post from Pat Phelan and I found it worth copy/pasting it here:

(I don’t know these guys btw)

In terms of SSMS (SQL Server Management Studio) add-ins, the only one that I’ve ever used (or even considered) was SSMS Tools Pack which is written and maintained by Mladen Prajdić.

Another tool that runs outside of SSMS, but is probably more useful to me personally is SQL Spec. This tool will document an entire SQL Server from logins and linked servers down to columns, datatypes, and samples of the data! It will also document SSIS package, Access Databases, DB2, Oracle, and just about anything else that you need to understand when doing a database project. I can’t say enough good things about Jesse and the only bad thing that I can say is that he doesn’t come here often enough!

A great tool to have when…

View original post 141 more words

SQL Server Tools and Add-ins

Respected SQL Server Resources

For all you people that wonder where I get scripts and other cool SQL stuff I compiled a lil’ list of links to sites with good SQL Server content. Hope you will enjoy there sites as much as you will enjoy mine (hopefully in time). 🙂

    If you don’t know Brent Ozar you have not been in the game long enough… The Blitz scripts he and his team created are truly kick-ass!! Check out his site and enjoy their wicked sense of humour.
    Paul Randal, Kimberly Tripp, Jonathan Kehayias and a few other great minds do their work here. If you need info about the DBCC and locking and blocking issues, this the site I look at first…
    The worldwide organisation of SQL Server Professionals. If you are not a member but work with SQL anyways, you should become a member TODAY!
    Ever heard of Dave Pinal? He is the expert behind this site and an experienced MVP. He has this great blog with easy to understand explanations and examples.
    The official blog of the SQLCAT (Customer Advisory Team) guys. This is the team that Microsoft lets loose on all the truly high-end customer issues. They build some of the biggest and highest performing SQL setups around and blog about some really low-level stuff. Working with high-performance, VLDB’s or leading edge solutions, check out this blog for tips!
    Managing SQL Server backups and indexes has never been more easy then with his maintenance solution. You will never use native SQL maintenance plans anymore once you learned the power of his award winning maintenance solution. Really, this is a MUST know solution.!!!!

Read everything one these sites, remember it and you will be an instant guru…. To bad it will take you about 10 bazillion years to read everything, but still…. These sites and the people behind them have so much knowledge and experience, I can only be in awe (kuddo’s for the first one to post a good 32bit joke here). Respect for all them and I suggest you all follow their blogs, sites and twitter feeds. It will make you a better DBA, I promise. 🙂

Respected SQL Server Resources

Kick-off for a new blog about SQL and other techie-stuff

Hello World…. I believe that is the traditional first posting on pretty much any blog and even tough I hate traditions in some cases, this one does feel fitting.

Now for the real stuff… Welcome to my blog about SQL and other techie-stuff. My name is Martijn van der Munnik and I live in the Netherlands. I am a self-employed Technology Consultant with a primary focus on SQL Server and all tech that surround and supports it. This blog will be about sharing information (duh!) and opinions on pretty much anything SQL Server related. I will be writing my own blog content but also ‘borrowing’ stuff from other blogs and sites and reposting it. Yeah, I know… ethics and bla bla bla… I will OFCOURSE be giving credits where credit is due and linking back to the sources that I used.

Anyways, again welcome to my blog and I hope you will enjoy it.


Kick-off for a new blog about SQL and other techie-stuff