SQL Server with Mr. Denny


June 20, 2014  8:12 PM

Recommended reading from mrdenny for June 20, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Cloud Computing, Managers, Recommended reading, SQL Server, Storage, Storage virtualization

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: DBA_ANDY also known as Andy Galbraith

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny

June 18, 2014  4:00 PM

Getting Replication Distribution Back After a MASSIVE Failure

Denny Cherry Denny Cherry Profile: Denny Cherry
failed, SQL Server, SQL Server 2008 R2, SQL Server 2014, SQL Server replication, SQL Upgrades
Distribution

Image from Shutterstock

Recently I had to upgrade a SQL 2008 R2 server to SQL Server 2014 on the fly, because the server in question was the SQL Server replication distributor and a couple of databases which were being replicated were being moved to a SQL Server 2014 instance. The Distributor’s upgrade didn’t go well.

After I got SQL up and running and happy with SQL 2014 CU1 it was time to get replication running again. The first thing I wanted to deal with was getting the SQL Agent jobs back up and running. SQL Server had decided that it was going to put the database files into E:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA. The first thing that I did was backup the folder as I was going to be playing in here, and I needed a backout plan in case things went horribly wrong.

Backing up the folder is easy. You stop the SQL Server services (SQL Server and the Agent) and copy the folder. I then grabbed the msdb database files from the SQL 2008 R2 folder and dropped them into the SQL 2014 database folder and restarted SQL. To my surprise this actually worked. All the jobs were listed as expected.

Next step, getting the distribution database up and running and getting SQL Server to think that replication has been running perfectly for years. The first step of this was to make a backup of the replication database files. If something went wrong, I needed a copy of these to restore from as the only backups that I had were missing transactions as those transactions weren’t backed up as the entire upgrade process was supposed to be smooth.

Attaching it was pretty straight forward. I just needed to run through the distribution setup wizard and get the T-SQL for it, then change the database name to match the old I’d been using.

Some permissions needed to be granted, and most importantly the linked servers needed to be added in the correct order. Once you’ve got them in there you can’t take them back out easily. I ended up with them in the wrong order so I had to switch the ID numbers around by putting the instance into single user mode from the command prompt and editing the sys.servers table manually via T-SQL.

Once that was all straightened out, the replication agents all came online and everything pretty much started working.

Denny


June 13, 2014  5:05 PM

Recommended reading from mrdenny for June 13, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Internet, IT salary, Microsoft MVP, Recommended reading, salary, VMware vSphere, VMware vSphere 5.5, XBOX

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: sqlpass also known as PASS

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


June 11, 2014  4:00 PM

Any Little Thing Can Kill a SQL Server Upgrade

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server 2008 R2, SQL Server 2014, SQL Upgrades
Fail

Image from Shutterstock

So I was going along upgrading a SQL Server 2008 R2 RTM server to SQL Server 2014. I had to install SQL 2008 R2 SP2 or newer before I could upgrade. That’s fine, I can live with that.

Then I get to the SQL 2014 installer. I get all the way to the end, when it fails. All because the folder E:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Jobs isn’t there (SQL is installed on the E drive). All the other folders were there, just this one wasn’t. Now honestly I have no idea why this folder even exists. To the best of my knowledge and remembering there’s never been anything inside of it in the 15 years that I’ve been working with SQL Server. God forbid the installer just create this folder and move on, or I don’t know check to see if it exists during one of the three rules checks that it does during the install process.

So fine, I created the folder and reran the installer. Only to be created with this annoying screen.

upgrade

According to this annoying little screen I’ve got two components to upgrade, but it says that there’s no components available to upgrade. So that’s awesome. Way to be consistent Microsoft.

 

Next step way to try and force the upgrade via the command line. Pretty straight forward upgrading via the command line. Only takes a few switches.

cmdline

Once that was done the install was totally hosed. SQL wouldn’t start. It would just give me a warning about a corrupt installation. Attempting to start it from the command line wouldn’t help either. Hell the command line upgrade process installed the bits and removed the 2008 R2 bits, but didn’t bother to change the service to point to the new files, which apparently is fine because the rest of the SQL install is hosed now.

Repairing the installation did no good, because the installer couldn’t find a valid installer on the server.

The upgrade process couldn’t find anything to work with either.

The next thing that I tried was to add the features that I needed (the SQL Database Engine) through the normal install wizard so add features to an existing installation. At this point the SQL installer didn’t even see an instance on the server, so there was up add features option. I was simply prompted to do a normal install of SQL Server 2014. When I went to install it stopped me from using my Instance ID which was MSSQLSERVER because it was apparently already in use by the SQL Server instance “MSSQLSERVER.INACTIVE”, what ever the hell that is.

Fixing this required using this blog post which talks about how to get rid of this instance manually. If you read the instructors here and think “damn that’s crazy”, yep you’re right it is.

So once that was done I went through the installer yet again. That got me through the installer. I was even able to give the installer the data folder of the old SQL 2008 R2 folder so that it would be able to pick up all the databases when it came online.

The database installed successfully. However it didn’t pick up the databases and attach them. The database that I was really hoping that it would pickup was the distribution database for replication so that I wouldn’t need to recreate all the replication by hand. How I deal with that is for another blog post.

The moral to this post is that apparently you need to make sure that every single folder is where it should be before you try and upgrade SQL Server from one version to another. Otherwise a 45 minute process could easily take you hours to get through.

Denny


June 6, 2014  5:02 PM

Recommended reading from mrdenny for June 06, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
NUMA, Recommended reading, SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: RechousaPT also known as Pedro Martins

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


June 4, 2014  7:00 PM

SQL Saturday Houston Slides Posts

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server, TechEd

The weekend before TechEd North America I had the pleasure of speaking at the Houston SQL Saturday. I present two different sessions at SQL Saturday Houston.

Table Indexing for the .NET Developer
Storage For the DBA

You can download the slides for both sessions from the links provided above. Sorry they took so long to get posted.

Denny


June 4, 2014  4:00 PM

Hekaton and Your Transaction Log

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL performance, SQL Server, SQL Server 2014, SQL Server transaction logs, Transaction Log

Hekaton (aka. In Memory OLTP Tables) is awesome. Things run very fast through the system, pushing you to 98x the speed of normal tables.

But be careful. You’ll need to know well in advance how much transaction log space you are going to need. You see there’s a little gotcha that I ran into recently (thankfully in a lab and not a production server). If the transaction log fills because say the hard drive of the server runs out of drive space, the transactions don’t fail to write, the database goes suspect.

Neat trick hugh.

So you’ll want to make VERY sure that you’ve got PLENTY of disk space for the transaction log and that you are backing that bad boy up often otherwise some really bad things are going to happen.

You’ll need to be even more careful if you are using Availability Groups as the Availability Groups feature queues up the data which needs to be replicated within the transaction log. So if a replica goes offline for a while you’ll need to have enough space on all the servers for the transaction log to grow until someone can get to the Availability Group and take the failed replica out of the configuration.

Denny


May 30, 2014  1:50 PM

Recommended reading from mrdenny for May 23, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Database Administrator, Database deadlock, Isolation level, Mobile security, Recommended reading, SQL Server, SQL Server Agent, SQL Server Agent Jobs, VMware, VMWare certification

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: PASS_RM_Canada also known as PASS RM Canada

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


May 28, 2014  3:53 PM

SQL Server 2014 Licensing

Denny Cherry Denny Cherry Profile: Denny Cherry
Microsoft licensing, SQL, SQL Server, SQL Server 2012, SQL Server 2014, SQL Server licensing

I’ve gotten a lot of questions about SQL Server 2014 licensing. The good news is that nothing has really changed since SQL Server 2012. There’s some minor wording changes to make it more clear what you have to purchase to get free secondary servers when using Clustering or AlwaysOn Availability groups. But other than that it’s all the same. With no further ado, here’s a lovely reblog of my SQL 2012 licensing blog post with some tweaks for SQL 2014.

So you may have heard earlier today about the license changes that are coming out for SQL Server 2012. I know that the official announcement can be a little hard to get through and the changes can be a little confusing at first (or even second or third) glance. The SQL Server licensing team was kind enough to spend some time sitting down with me to try and work through how the license changes will be effecting customers. Please note that any prices that I quote in here are list (retail) prices and are shown in US dollars. If you have an Enterprise Agreement with Microsoft you will probably be paying less.

Editions

First lets start with the edition lists. SQL Server is reducing the number of editions from 6 down to 4 (I’m excluding all the different appliances here). Those editions will be Express, Standard, BI, and Enterprise Edition. The express edition will still be a free edition with limited functionality. I wouldn’t expect the features that are available in the express edition to change. As for the features which will be available, the specific features which will be in each edition haven’t yet been announced. Look for this sort of feature matrix to be available sometime between now and when SQL Server 2012 goes GA in 2012. Until that feature matrix is released any specific feature to edition information that you here is just a rumor.

Web Edition

The web edition will still exist even though I didn’t list it above. The reason that I didn’t list it above is because the web edition will no longer be available for purchase via enterprise license agreements or retail channels. The only way that you will be able to get the web edition of SQL Server 2012 will be through a hosting provider like Amazon EC2, RackSpace, Dream Host, Genesis Hosting Solutions, etc. These hosting providers will be able to provide you with the web edition of SQL Server 2012 through their hosting provider license.

Standard Edition

The standard edition is still around and kicking. Standard edition will be available in both a Server+CAL license model or a CPU Core license model. The costs for a server license or a CPU Core license remain the same as they were under SQL Server 2008 R2 (I’ll explain the core licenses later on). However if you are using the Server+CAL license model the CALs are going up in price by 27% from $164 (US) to $208.28 (US).

There are some things to note about Standard Edition which are changing from prior editions. First there is a hard limit of 16 cores for Standard Edition. This is hard set and is a license limitation so SQL Server will only use the first 16 cores in the server if there are more than 16 cores in the server.

A big change to the Standard Edition high availability story that has been announced is that Database Mirroring in Standard Edition remains but has been marked as deprecated. This means that it is support available, and supported but will be removed from the product in a future release, typically 2-3 major releases from now.

As for the support for other features in the standard edition of SQL Server 2012 not a lot of the details have been made available yet. For the complete support matrix you’ll have to wait until we get closer to the release of the product.

The features that I do know will NOT be available in the standard edition are:

  • Data Quality Services
  • Master Data Services
  • Power View
  • PowerPivot for SharePoint Services
  • Semantic Model
  • Advanced Auditing
  • Transparent Data Encryption
  • Column Store Index
  • Data Compression
  • Table/Index Partitioning
  • AlwaysOn Availability Groups

BI Edition

The BI Edition will include all the features of the Standard edition plus some extras. The licensing for the BI edition will only be available in a Server+CAL model. There is no CPU core license available for the BI Edition. If you need a CPU license for the BI Edition you’ll need to look to the Enterprise Edition (as it includes all the BI edition features). Now the BI Edition doesn’t include only the BI stack. It includes the database engine as well with a slightly higher cap on the number of cores that are supported. While the standard edition supports 16 cores for the database engine, the BI edition supports up to 20 cores for the database engine. All of the BI services (SSAS, SSIS, SSRS) will support up to the OS maximum number of cores.

The BI edition will include the following features:

  • Data Quality Services
  • Master Data Services
  • Power View
  • Power Pivot for SharePoint Services
  • Semantic Model
  • Advanced Analytics

The BI Edition will not include the following features:

  • Advanced Auditing
  • Transparent Data Encryption
  • Column Store Index
  • Data Compression
  • Table/Index Partitioning
  • AlwaysOn Availability Groups

The BI Edition is listed as having “Basic” High Availability.

The cost for the BI Edition server license will be the same as the SQL Server 2008 R2 Enterprise Edition server license which is $8,592.

If you need an internet facing SSAS cube you can not use a BI Edition license, you will then need to look at purchasing an Enterprise Edition license for that server.

Enterprise Edition

The Enterprise Edition is still around and is the big daddy of the SQL Server stack again as the Data Center Edition of the product is being removed. This means that the Enterprise Edition will hold all the new features that are being introduced. When it comes to licensing Enterprise Edition there is no longer a Server+CAL model available for new purchases. You will only be able to purchase CPU Core licenses. If you are upgrading an existing server that has Server+CAL using your Software Assurance or Enterprise Agreement you can continue to use the Server+CAL license model for as long as you continue to pay for Software Assurance. If you terminate your Software Assurance license you’ll then need to purchase the correct number of CPU Core licenses for that server (again I’ll be talking about the CPU Core licenses further down).

The Enterprise Edition of SQL Server 2012/2014 includes all the features of the BI Edition as well as the Standard Edition.

CPU Core Licenses

By now you’ve probably noticed that I’ve mentioned CPU Core licenses and not CPU licenses. That’s right, Microsoft has decided to follow the unholy beast (aka. Oracle) into CPU core licensing hell. It’s actually not that bad, and for most of your servers the costs should stay the same.

The CPU Core licenses (available only for Standard and Enterprise edition) are sold in two core “packs” (I don’t know the actual term for them yet, so I’ve decided to call them “packs”). If you have quad core CPUs you need two of these packs per socket. These license packs will cost half of what a SQL Server 2008 R2 CPU license cost. The catch here is that you have to purchase at least 4 cores per CPU. The reason that Microsoft decided not to just sell the licenses in 4 core “packs” is because the people with 6 core CPUs would then be screwed having to buy more licenses than they actually needed.

If you have two sockets with 2 cores each, you need 4 license “packs” (8 core licenses)

If you have two sockets with 4 cores each, you need 4 license “packs” (8 core licenses)

If you have two sockets with 6 cores each, you need 6 license “packs” (12 core licenses)

If you have two sockets with 8 cores each, you need 8 license “packs” (16 core licenses)

SQL Server in a VM

When you are running SQL Server within a virtual machine if you are using CPU Core licenses then you will need to license at least 4 cores for the VM. If you have more than 4 vCPUs on the VM you’ll need to have a CPU Core license for each vCPU that you have assigned to the VM.

Host Based Licensing

SQL Server 2012 will still include host based licensing as on option for those customers who have Software Assurance and an Enterprise Agreement. The host based licensing works just like it did before, you purchase enough Enterprise Edition CPU Core licenses for the host and you can run as many virtual machines running SQL Server as you would like to.

Upgrading using your Software Assurance Rights

This is probably the section that you were looking forward to (or dreading) the most.

When you upgrade from SQL Server 2008 R2 (or below) to SQL Server 2012 using your Software Assurance rights you can continue to use your existing license model until the end of your Software Assurance cycle. This means that if you have CPU licenses under SQL Server 2008 R2 you can continue to use those CPU licenses under SQL Server 2012 until your Software Assurance expires. Once it expires you will need to true up on the number of CPU Cores. When this true up happens each CPU license that you had before will count towards a minimum of 4 CPU Cores (if you have Data Center Edition CPU licenses of SQL Server 2008 R2 you will be credited a minimum of 8 CPU Cores for each CPU license). If you have a large server that needs upgrading talk to you Microsoft Account Manager or Microsoft Partner about the core credit that you’ll get for your legacy CPU licenses. You’ll notice the magical word “minimum” shown here. This is because the Microsoft Account Managers and Microsoft Partners have the ability to give you more credit that these minimums.

If you have a SQL Server today with two sockets and you upgrade it to SQL Server 2012, then you need to add two more sockets to the server you can still do that. Contact your Microsoft Account Manager (if you have a Software Assurance or an Enterprise Agreement then you have a Microsoft Account Manager, your boss probably knows how to contact them) or Microsoft Partner and they will be able to see you CPU licenses for existing servers only. The SKU won’t be published, but it will be available internally for them to get to you.

Server Licenses and Upgrading

If you have a Standard Edition server that uses Server+CAL licensing that you are upgrading to SQL Server 2012 do keep in mind that hard limit of 16 cores for Standard Edition.

If you have an Enterprise Edition server that uses Server+CAL licensing that you are upgrading to SQL Server 2012 don’t freak out. There is an upgrade path for you even though there is no more Server+CAL license available for SQL Server 2012, provided that you have Software Assurance for the SQL Server license. If you do then you can upgrade that server continuing to use the Server+CAL model (don’t forget that your CALs need upgrading as well if they don’t have Software Assurance) until your Software Assurance expires at which point you would need to downgrade to the BI edition or the Standard Edition; or upgrade to the CPU Core license model to remain on Enterprise Edition (look at the features you need before making this decision). Now the catch here is that using the SQL Server 2012 Enterprise Edition with a Server+CAL license option (that you’ve upgraded into as you can not purchase this new) is ONLY available for servers that have 20 cores or less. If you have a SQL Server with more than 20 cores running Enterprise Edition using a Server+CAL license model you’ll need to talk to your Microsoft Account Manager or Microsoft Partner to see what options are available to you.

Costs For Buying New

For Standard Edition when using the Server+CALs license the cost will remain the same as SQL Server 2008 R2 ($898). When using the CPU Core license model if you have 4 cores or less per CPU socket the cost will remain the same as a CPU license for SQL Server 2008 R2 ($7,171 US). If you have more cores than 4 per socket the costs will go up depending on the number of cores that you have. For every two cores in the server you’ll need a CPU Core “pack” which will cost $3,585.50.

For the BI Edition your only option is Server+CALs. The SQL Server 2012 BI Edition server license will be the same as the SQL Server 2008 R2 Enterprise server license cost ($8,592).

For the Enterprise Edition your only option is the CPU Core license model. For a server with 4 cores or less per CPU the cost will remain the same as a CPU license for SQL Server 2008 R2 ($27,495). If you have more cores than 4 per socket the costs will go up depending on the number of cores that you have. For every two cores in the server you’ll need a CPU Core “pack” which will cost $13,747.50.

If you have an Enterprise Agreement check with your Microsoft Account Manager or Microsoft Partner for actual pricing information. Customers with an Enterprise Agreement should be paying less than these numbers show. Of course Software Assurance will made the numbers go up but that includes its own set of benefits.

Questions?

I hope that this answers your questions about the SQL Server 2012/2014 licensing model that will be coming out. If I didn’t please post your questions here or on Twitter and I’ll either answer them or get them to someone who can answer them if I can’t and get the answer published either in the Q&A here or as a new blog post.

My Opinion

Now you may think from this rather long post that I like everything that I’ve talked about here, which isn’t the case. This blog post is to simply explain what is coming in as clear a way as possible given that the presentations on this topic that I’ve seen to date have left me with a lot more questions than answers. After all I did spend the better part of a day on the phone with two people at Microsoft to get all of this explained in such a way that I understood it. As not everyone out there will get that chance I put together this blog post to explain what I learned. Personally I don’t think that killing off the Workgroup edition was not a good idea. Lots of people were using it, including everyone running SBS 2008 R2 (or what ever it is called).

I’m not a big fan of the core based licensing model that we have coming out with SQL Server 2012, but I do understand why Microsoft is going to this model. I think we were pretty lucky to have gotten away with CPU based licenses for as long as we did. Personally I really think that moving away from a Server+CAL license model for Enterprise Edition is a bad plan given that thinks like the Utility Control Point require Enterprise Edition (I’m assuming this will still require EE). If you want to manage a large company with UCP that requires a pretty beefy Enterprise Edition server which is dedicated to UCP, yet this server now will cost A LOT compared to just buying an Enterprise Edition Server+CAL license for your UCP server.

I think that a lot more information is going to be needed before any real decision can be made on how much I like or don’t like these changes. When the full feature to edition matrix is released that will be a big deciding factor in how well some of these changes will go over.

Obviously the knee jerk reaction of SQL Server 2012/2014 is going to cost me more money so the chance sucks is an easy one to take, hell that’s where I started from (you should have seen the first emails I sent to Microsoft). But when you look at some of the variables that they have put into getting people upgraded, specifically the ability to convert a CPU license to at least 4 core licenses (or at least 8 if coming from Data Center edition) gives some flexibility. This is when it is going to be very important to have a good working relationship with your Microsoft Account Manager or Microsoft Partner as the ability to upgrade for as little as possible will be sitting squarely in their hands. I would reach out to this person now, get to know them. Bake them some cookies, send them a bottle of booze, what ever it takes to get on their good side.

Denny


May 23, 2014  6:21 PM

Recommended reading from mrdenny for May 23, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Recommended reading, SQL Server, SQL Server 2012, SQL Server 2014

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: BuffaloPASS also known as Buffalo PASS

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: