31.3.14

SQL 2014 Release Date, April 1st

I'm not a fan of anything releasing on April Fool’s Day, but that's not for me to decide. I personally cannot wait to upgrade to 2014.  I want to see the full power behind PCI-e based SSDs pushing extended ram. I want to have the security of having a server go down and it not take out both sides of the Availability Group. If you haven't heard, when one goes off, it shuts them both down. I want to rebuild my partitioned indexes online. I have lots of wants, but how long will I have to wait to install?

I'm torn here. An old theory was to wait for Service Pack 1 before installing anything by Microsoft. Did we replace Service Packs with new versions? How long will we have to wait for the next "version"?  I hope I'm wrong here. I do love new features. I love upgrades. I do not like upgrading every year. 

I have this feeling that we're about to see releases every other year at most. We may quite possibly see a release per year. I do not want to see this happen. larger agencies stay on older tech longer. I think that coupled with the increased prices for SQL Server with the core licensing may in fact push people off of the Microsoft stack. They may not lose enough to hurt them bad enough, but they will lose some. 

What other Database platforms would you all consider? I personally hope we get back to seeing service packs and keep this stack strong. We have too strong of a community to see it split. 


This is a quote from their SQL Saturday posting, "We are encouraging participation in our food drive to benefit the Westside Cares food pantry located at the facility where the SQL Saturday event will take place.  Toward the end of the event, we plan to give people additional chances to win SWAG based on the amount of items each person brings for donation to the food pantry."

I really hope we can we can get enough people to bring food to make a large difference. 

If you disagree, please let me know. I'd love to hear other opinions.

24.3.14

SQL Server Enterprise is Cheaper than Standard

Free Month of Plural Site

I'm interested in seeing your responses. Any relevant response will be accepted and a free month of Plural Site will be given away. You do not have to agree with me to be relevant. I want to hear what you think.

Now this may seem a bit far-fetched, hear me out. 

This is a bit long winded for me.

First we will address the minimum requirements. We are comparing core licensing only. You must purchase at least 4 cores. Enterprise Edition costs $6,874 per core. That's a starting price of $27,496. Standard Edition costs $1,793 per core. That's a starting price of $7,172.

Now I know what you're thinking, a $20,324 difference seems pretty open and shut against this. This is where I ask that you hear me out. It gets a bit tricky here. Let's start comparing the benefits of upgrading.

Limits Standard Enterprise
RAM
64 Unlimited
Indexing Offline Online
Table Compression No Yes
Fast Recovery No Yes
Table Partitioning No Yes
Resource Governor No Yes
I know there are a lot more difference relating to BI, AS, RS and many other aspects. Let's just get enough out there to prove the point. I just told you that a $20,000 dollar cost was a savings yes? How can we save $20,000 by spending it? 

How much do you make a year? 

How about your other DBA, or the JR you're about to hire? How many Developers do you have on staff? How many of them are over worked trying to keep your old Standard server running? Look at your database closely. Let's answer some questions; we'll address this question last.

Do you have the maximum ram that your server can support in it? 

That may be 192, 384, any other number. Unless your server is older, it should support more than the 64GB of RAM that Standard does. I know I know, Windows Server Standard only supports 32GB of RAM, but that changed in Server 2012. 2TB is the RAM limit now. I know SQL 2014 allows for 128GB of RAM standard, but that still means more room to grow.

Do you have processor cores just going idle most times? 

Just because you have 12 cores doesn't mean you need to license 12. You can set SQL to use the limit of what you license it to use. Only license what you need.

Do you have SLA's to meet that have been difficult due to maintenance windows? 

Online indexing allows you to rebuild tables just about any time. You do still get a minor lock at the start and end of an online operation, but that's far better than during the whole process.

Do you have issues with archiving those massive tables?
 
Is their performance falling behind? Partitioning can help you swap parts of the table in and out while being minimally intrusive. You can even address fragmentation per partition instead of hitting that 10 billion row table all at once. In 2014 you can even do that operation online now!

Do you have multiple databases on the same server fighting for resources?

Well now you can split them up logically instead of having that same conversation about splitting them up physically. 

Are you fighting for more space or even considering moving to an Enterprise SAN?

Page compression is a beautiful thing. It should pose no problems on archived tables. If CPU is not a bottle neck currently, you can expand the window of what you compress. Heavily used tables may not benefit as much... but here's where partitioning can work with this. Page Compression saves quite a bit of space. This may be just enough of a space saver to allow you to request those SSDs you've been wanting.

Let's add this all together.

Yes there is a cost up front, but now you no longer need to hire a 3rd full time DBA or Developer. If you get that system on SSD's since we're using page compression to keep our sizes small and partitioning to keep our archived data on slow disks, our response time is faster. We were previously running to the edge of our RAM at all times, now we have cached static data from a month ago. These disks are only being accessed to present changes. Our DBAs are getting more sleep now that their fragmentation jobs aren't blocking all night long. Our maintenance windows are getting shorter and our SLAs have more room to breathe. 

This view will not fit all organizations. As always, the phrase "It depends" will fit in this scenario as well. Think carefully about all these issues and the time you've burned fixing them. You could be working on that next project to make your company even more money.

I mentioned Developers in this post. I'll explain now why. With compression, partitioning, more caching, faster access to those tables... you can hide a lot of "quick" codding with that much faster power. I'm not saying that we should code poorly because we can. I'm saying that we can code how we need because we can. Once it's up and working, you can then go back and fine tune. 

Enterprise Edition has a higher cost. Asking for more SSD's and more RAM has an additional cost. Not having your talented knowledgeable DBAs and Developers quit due to long hours, continually fighting uphill battles and being denied tools or extra personnel will cost you a lot more in the end. Training your next DBA alone may make up this cost. Not losing your customers due to the inability to meet SLA requirements has a large cost monetarily and to your reputation. 

Let's make the world happier, one Database shop at a time.

17.3.14

My Favorite Free Scripts


I'll start off with Ola Hallengren's scripts. I'm not a fan of reinventing the wheel. If someone makes gold, I tend to want to use it. Ola's scripts are fantastic. They're even already updated for 2014! You do have to contact them for the CTP version of the scripts.

These scripts cover your backups, index maintenance, verifies the integrity of your databases and logs the results of all of this for you. You can set up one part or all. This covers all of your 101 DBA requirements to keep your servers running in a safe fashion.  They even give examples of what you might want to throw into the script! It doesn't get much easier than this.


Adam Machanic wrote a great script called "Who is Active". He even has a 30 part blog series on the ins and outs of this procedure found here. Just about anything you want to know can be found there. It's a fantastic script. I would familiarize yourself with it prior to relying on it for day to day operations. 


Kendra Little has a great video and some sample scripts to run to view what indexes your server is wanting. As she will stress, do not just put them all into your database. Too much of a good thing can be really really bad. This is where we look for all that beautiful low hanging fruit.


On the same site as the Missing Indexes video by Kendra Little, there is a great script called SP_BlitzIndex. This was written by the same group. It's the step beyond just looking for missing indexes. Take the time to look through this when you have some time. This is not a place I'd look with a fire to put out... At first anyways. Learn about it in detail before assuming too much and jumping into it.


SP_Blitz was written by the Brent Ozar group and it helps you identify many pain points really quickly and even includes a link to what those mean and suggestions on how to handle it. Honestly, how nice is it these exist for us?!

What Can We Do?

So here we have 5 fantastic scripts. What will these accomplish? 
1) We start off with Ola's script and get our backups and index fragmentation under control. 
2) We find out from our users if anything in particular is slow or below SLA requirements. 
3) We run the SP_Blitz and see what shows up as a major issue.
4) We get a quick break down of what the Missing Indexes are suggesting.
5) We put all this together in a solid actionable list. 
6) We present our findings and come up with a solution to work on.

Yes I use the word "We" a lot. I do this on purpose. Correcting this many problems isn't a one person operation. You want the other product heads involved. You need to know what this could break or even if there's a problem you're currently trying to fix. If they need a report to respond in under a minute, spending a week trying to get it from 45 seconds to 5 seconds isn't where the focus should be unless everything else is working perfectly. 

There Is More Out There!

These are just 5 of the many many scripts out there for free that have been provided just to make your job easier. I've said it before, I'll say it again... MS SQL Server has a fantastic community around it. Few come even close. These 5 scripts alone can help a DBA sustain a workshop with minimal effort. Use this not as a crutch, but as a starting point to make everything even better. 

If you all know any other great scripts that can be added to this collection, I would love to hear it!

3.3.14

Issues Installing SQL 2008 R2 on Server 2012 R2 Clustering

Oh so the nightmare begins. 

At the bottom I've included a set of links where I found my answers. What I'm doing here is just giving a comprised list of what I had to do to get it to work. I ran into a few issues almost immediately.

I got an error showing that a 2003 patch was not installed.
Windows Server 2003 FILESTREAM Hotfix Check      failed

This required a work around. You need to slipstream install SP1 or SP2. I did not write this step by step. I used what I found here written by Peter Saddow.

1. Copy your original SQL Server 2008 R2 source media to C:\SQLServer2008R2_SP1
2. Download the SQL Server 2008 R2 SP1 packages from here. You need to download all Service Pack 1 architecture packages:
•SQLServer2008R2SP1-KB2528583-IA64-ENU.exe
•SQLServer2008R2SP1-KB2528583-x64-ENU.exe
•SQLServer2008R2SP1-KB2528583-x86-ENU.exe
3. Extract each of the SQL Server 2008 SP1 packages to C:\SQLServer2008R2_SP1\SP as follows:
•SQLServer2008R2SP1-KB2528583-IA64-ENU.exe /x:C:\SQLServer2008R2_SP1\SP
•SQLServer2008R2SP1-KB2528583-x64-ENU.exe /x:C:\SQLServer2008R2_SP1\SP
•SQLServer2008R2SP1-KB2528583-x86-ENU.exe /x:C:\SQLServer2008R2_SP1\SP
Ensure you complete this step for all architectures to ensure the original media is updated correctly.
4. Copy Setup.exe from the SP extracted location to the original source media location. Here is the robocopy command:
•robocopy C:\SQLServer2008R2_SP1\SP C:\SQLServer2008R2_SP1 Setup.exe
6. Copy all files not the folders, except the Microsoft.SQL.Chainer.PackageData.dll, in C:\SQLServer2008R2_SP1\SP\ to C:\SQLServer2008R2_SP1\ to update the original files. Here is the robocopy command:
•robocopy C:\SQLServer2008R2_SP1\SP\x86 C:\SQLServer2008R2_SP1\x86 /XF Microsoft.SQL.Chainer.PackageData.dll
•robocopy C:\SQLServer2008R2_SP1\SP\x64 C:\SQLServer2008R2_SP1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll
•robocopy C:\SQLServer2008R2_SP1\SP\ia64 C:\SQLServer2008R2_SP1\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll
7. Determine if you have a DefaultSetup.INI at the following locations:
•C:\SQLServer2008R2_SP1\x86
•C:\SQLServer2008R2_SP1\x64
•C:\SQLServer2008R2_SP1\ia64
If you have a DefaultSetup.INI at the above locations, add the following lines to each DefaultSetup.INI:
PCUSOURCE=".\SP"

If you do NOT have a DefaultSetup.INI, create one with the following content:
;SQLSERVER2008 R2 Configuration File
[SQLSERVER2008]
PCUSOURCE=".\SP"
and copy to the following locations
•C:\SQLServer2008R2_SP1\x86
•C:\SQLServer2008R2_SP1\x64
•C:\SQLServer2008R2_SP1\ia64
This file will tell the setup program where to locate the SP source media that you previously extracted.
8. Run setup.exe as you normally would.

To add again, this post is just to give a good central location for how to get 2008 R2 installed on server 2012 R2. The original post was here.

There was a second error showing that the cluster service verification failed. You can fix this via the GUI, but it is much easier to open a Powershell window and just copy and paste, “add-windowsfeature RSAT-Clustering-AutomationServer” This was written by Emilson Barbosa Bispo on this page.

All in all it was a good learning experience. I know that it’s normally best to upgrade your OS when you upgrade SQL…. But those licenses are expensive. This goes double if it’s an enterprise license and you have a few servers that don’t need to be up to date just yet.

I really hope this is helpful for someone. I know finding these saved me a lot of time. What’s the worst thing you’ve had to work on? I’d love to hear other stories of interesting fixes.If you run into any other errors while doing this, let me know. I may have come across it and have an easy answer for you.



How to Slip Stream