SQL Server: How to tame the beast!

One of the benefits of virtualization is that you can segregate your SQL Servers from your other workloads.  Why?  If not then Microsoft SQL Server will hoard every last bit of resources on your machine, leaving scant crumbs for other workloads. 

image

Seriously… when you start the Microsoft SQL Server you will immediately see your memory usage jump through… or more accurately, to the roof.  That is because SQL Server is actually designed to take up all of your system’s memory.  Actually that is not entirely true… out of the box, Microsoft SQL Server is designed to take up 2TB of RAM, which means that in all likelihood a lot more memory than your computer actually has.

So assuming you have been listening to me for all of these years, you are not installing anything else on the same computer as your SQL Server.  You are also making sure that the virtual machine that your SQL Server is installed on (remember I told you to make sure to virtualize all of your workloads?) has its memory capped (Hyper-V sets the default Maximum RAM to 64GB).  You are doing everything right… so why is SQL performing slowly?

It’s simple really… Your computer does not have 2TB of RAM to give SQL Server… and if it did have 2TB of RAM, the operating system (remember Windows?) still needs some of that.  So the fact that SQL wants more than it can have can make it a little… grumpy.  Imagine a cranky child throwing a tantrum because he can’t have deserts or whatever.

Fortunately there is an easy fix to this one (unlike the cranky child).  What we are going to do is limit the amount of RAM that SQL actually thinks it wants… and when it has everything that it wants, it will stop misbehaving.

1) Determine how much RAM the server on which SQL Server is installed has.

2) Open Microsoft SQL Server Management Studio with administrative credentials.

3) Connect to the database (If you have multiple SQL databases on the same server see the note below)

4) In the navigation pane right-click on the actual SQL Server (the topmost item) and click Properties

5) In the Server Properties page navigate to Memory

6) Figure out how much 90% of your server’s RAM would be (in megabytes).  Use the following equation:

1GB = 1024*.90=921.6

8GB = 1024*8 (8192)*.90=7373

7) In the Maximum server memory (in MB) field type that number, then click OK.

That’s it!

**Note: The math we are using here allocates 90% of the total RAM to the SQL Server.  In the event that you have multiple SQL Server databases running on the same box you will have to do a bit of calculating to determine how much each database should use… and that can be a challenge.

If you only have the one database engine on your box, you should immediately notice marked improvements.  This breathing room does not mean that it is now time to pour more workloads onto the server… only that your SQL Server should be running a little better!

Keep Up: How to configure SCOM to monitor the running state of services and restart them when they stop

Windows runs on services.  Don’t believe me?  Open your Services console and count just how many are running at any given time.  Of course, some of them are more important than others… especially when you are talking about servers that are critical to your organization.

A new customer recently called me for a DEAR Call (emergency visit) because their business critical application was not working, and they couldn’t figure it out.  I logged into the server, and at first glance there didn’t appear to be anything wrong on the application server.  However I knew that the application used SQL Server, and I did not see any SQL instances on the machine.  A quick investigation revealed that there was an external SQL Server running on another server, and it only took a few seconds to see why the application was failing.

image

Very simply put, the service was not started. I selected it, clicked Start the service, and in a few seconds the state changed:

image

A quick look showed that their business critical application (in this case SharePoint 2010) was working properly again.

My customer, who was thrilled to be back in business, was also angry with me.  ‘We spent tens of thousands of dollars on System Center Operations Manager so that we could monitor our environment, and what good does it do me?  I have to call you in when things stop working!’

Yell as much as you like I told him, but please remember the old truism… if you think it is expensive hiring professionals, try hiring amateurs.  After he had learned about the benefits of implementing a proper monitoring solution he told his IT guy to install it… and that is exactly what he did.

System Center Operations Manager (SCOM) is a monitoring framework, and really quite a good one.  In fact, if Microsoft included the tools within the product itself to monitor every component that it is capable of monitoring, it would have to come in a much bigger box.  Instead, what it gives you is the ability to import or create Management Packs (MPs) to monitor aspects of your IT environment.  It is up to you to then implement those MPs so that SCOM can monitor the many components of your infrastructure… and take the appropriate action when things go wrong.

Of course, there are much more in-depth MPs for monitoring Microsoft SQL Server, but for those IT generalists who do not need the in-depth knowledge of what their SQL is doing, simply knowing that the services are running is often good enough… and monitoring those services is the exact same step you would take to monitor the DNS Server service.

Although it is long, following these relatively simple steps will do exactly what you need.

1) Open the Operations Manager console.

2) In the Operations Manager console open the Authoring context.

3) In the navigation pane expand Management Pack Objects and click on Monitors.

image

4) Rick-click on Monitors and select Create a Monitor – Unit Monitor…

5) At the bottom of the Create a unit monitor window select the Management Pack you are going to save this to.  I never save to the default management packs – create your own, it is safer (and easier to recover when you hork something up).

6) In the Select the type of monitor to create section of the screen expand Windows Services and select Basic Service Monitor.  Click Next.

SNAGHTML33d5f150

7) In the General Properties window name your monitor.  Make sure you name it something that you will recognize and remember easily.

8) Under Monitor target click Select… From the list select the target that corresponds to the service you will be monitoring.  Click OK.

9) Back in the General Properties window uncheck the Monitor is enabled checkbox.  Leaving this enabled will try to monitor this service on every server, not just the one where it resides.  Click Next.

10) In the Service Details window click the ellipsis button () next to Service Name.

11) In the Select Windows Service window either type the name of the target server, or click the ellipsis button and select the computer from the list.  Then select the service you wish to monitor from the list under Select service.  Click OK.

SNAGHTML33e455ae

12) Back in the Service Details window the Service name window should be populated.  Click Next.

13) In the Map monitor conditions to health states window accept the defaults… unless of course you want to make sure that a service is NEVER started, at which point you can change that here.  Click Next.

SNAGHTML33e6ea20

14) In the Alert settings window select the Generate alerts for this monitor checkbox.  You can also put in a useful description of the alert in the appropriate box.  Click Create.

The saving process may take a minute or two, but when it is done search for it in the Monitors list.

14) Right-click on your custom monitor.  select Overrides – Override the Monitor – For a specific object of class: <Name of the product group>

image

15) In the Select Object window select the service you are monitoring and click OK

16) In the Override Properties window, under the Override-controlled parameters list, scroll for the parameter named Enabled and make the following changes:

a) Select the Override checkbox.

b) Change the Override Value to True.

c) Click Apply

d) Click Show Monitor Properties…

17) In the Monitor Properties window click the Diagnostic and Recovery tab.

18) Under Configure recovery tasks click Add… and when it appears click Recovery for critical health state.

image

19) Under the Create Recovery Task Wizard click Run Command and click Next.

20) In the Recovery Task Name and Description window

a) enter a Recovery name (Re-Start Service works for me!).

b) Select the checkbox Recalculate monitor state after recovery finishes.

c) Click Next.

21) In the Configure Command Line Execution Settings window enter the following information:

Full path to file: %windir%\System32\Net.exe

Parameters: start <service name>

Working directory: %windir%

Timeout (in seconds): 120

22) Click Create.

23) Close the Monitor Properties window.

24) In the Override Properties window click Apply then OK.

The doing is done, but before you pat yourself on the back, you have to test it.  I always recommend running these tests during off-hours for non-redundant servers.

1) Open the services.msc console.

2) Right-click on Services (Local) and click Connect to another computer…

3) Connect to the server where your monitored service is running.

4) Right-click on the service and click Stop Service.

It may take a couple of minutes, but if you get up and go for a walk, maybe make a cup of coffee or tea… by the time you get back, the service should be restarted.

There seems to be a reality in the world of IT that the more expensive something costs, the less it is likely to do out of the box.  It is great to have a monitoring infrastructure in place, but without configuring it to properly monitor the systems you have it can be a dangerous tool, because you will have a false sense that your systems are protected when they really aren’t.  Make sure that the solution you have is properly configured and tested, so that when something does go wrong you will know about it immediately… otherwise it will just end up costing you more.

Microsoft’s 2012 Products: Why can’t they all just work together?

I have heard a lot of comments, groans, and snide remarks about all of the 2012 products not working together – specifically Windows Server 2012, System Center 2012, and SQL Server 2012.  While I share your pain, it is important to know a few things.

Each of these products is developed by different product teams, each working on very strict NDAs and very strict deadlines… none of which are the same.  SQL Server released first, followed by System Center 2012, and finally Windows Server 2012.  These were not in quick succession you understand… they were over a course of seven or eight months.

Imagine telling the product team for System Center: ‘The new product that you are releasing in March… make sure that it is fully compatible with a product that will be released in September, which the product team cannot properly share with you until July, for which features and security have not been completely decided.’

That scenario does not seem realistic to me.  However let’s try something else:

‘Hey guys, the product that you are releasing in March… we would like for it to be fully compatible with another product that another team will be releasing in September… Once that product is released, we are giving you 90 days to release a service pack that will make the two products fully compatible.’

I think the second scenario was a lot more reasonable, and while there were a few snags along the way (the RTM of the service pack came within 105 days, not 90 days of the RTM of Windows Server 2012) I think they did a great job.

As for SQL Server, some of its components do require a feature (NetFx3) that the Windows Server team did not include in the final install…(See article: Installing NetFx3 on Windows Server 2012) it is a simple workaround, and one that is documented quite well in SQL circles (in which I do not travel much).  However as far as stumbling blocks go, this is a fairly minor one, with a very easy solution.

Would it be too much to expect that all teams at Microsoft were on the same page?  Actually knowing (or at least suspecting) how many people are involved, I think it would be.  Each one of these teams is larger than many companies.  So if you think of each of these teams as a separate company, if you were to purchase each from the individual companies you would be thrilled that these were the most serious stumbling blocks out there.  The reason we complain about them is because Microsoft is actually one company.

Do I wish things were easier for us as IT Pros? Not at all.  I remember what things were like five and fifteen years ago, and I expect that we are aeons ahead of where we were.  In fact, if things got a whole lot easier, some of us would be out of a job Smile

Of course I do not speak for Microsoft on this, but I do appreciate the reasons behind the issues.  Hopefully now that 2012 is behind us, all of the products labeled 2012 will work together (they do!) and we can spend the next few years working with these tools in harmony!

Installing NetFx3 on Windows Server 2012

image

Okay… I am installing SQL Server 2012 on a Windows Server 2012 box, there shouldn’t be any problems.  Everything is proceeding normally until I get this message:

Error while enabling Windows feature : NetFx3, Error Code : –2146498298 , Please try enabling Windows Feature : NetFx3 from Windows management tools and then run setup again.

No problem… I know how to install Windows Features; I start the Add roles and Features Wizard and go looking for NetFx3… it’s not there.

Problem.

It turns out that Windows Server 2012 does not include NetFx3 when it is installing.  It doesn’t mean that it is gone, but it does have to be installed separately.  Here’s what you do:

1) Insert your Windows Server 2012 media.  As I was installing SQL Server in a Hyper-V VM I ejected the SQL media and attached my Windows Server 2012 ISO.  I then checked to see what drive letter it was (D:).

2) I opened a Command Prompt with administrative credentials.  From the Start Screen I typed CMD but instead of clicking on it or pressing ENTER I right-clicked, and at the bottom clicked on Run As Administrator.

3) From the Command Prompt I typed the following command:

dism /online /enable-feature /featurename:netfx3 /all /source:d:\sources\sxs

image

The Deployment Image Servicing and Management tool is one of the easiest ways to install features in Windows when the GUI fails you. 

Note: Unfortunately, if you encounter this error you will have to restart your installation of SQL Server.  That doesn’t mean you should cancel it out at this point… what I did was I left the error message on the screen while I resolved the NetFx3 issue, and then let it resume.  The SQL Server installation succeeded, with several failures.  I then went back and re-installed SQL on top of the old, with the features that I needed.  It worked just fine for me, and it should for you.