Upgrading SQL Server 2016 Release Candidate to General Availability

I have a client that wants to run on the latest and greatest version of SQL Server.  We have been testing on RCs (Release Candidate), and we are in the process of upgrading non-production environments to RTM GA (General Availability) as it was released today on June 1st, 2016 . When SQL Server 2014 RTM’ed (is that a word?) upgrades from the latest RC was supported.  I expect the same with SQL Server 2016. Currently, this isn’t specified in the SQL 2016 Release Notes.  I will update the post when it’s confirmed.  I am not a SQL Server MVP so I have one sending a message to their message board to see if we can get confirmation on if this upgrade will be supported.  Obviously, I wouldn’t recommend doing this with your live production instances of SQL Server until it is confirmed. Even, if this isn’t supported, I have a automated install process so it wouldn’t take us much time to start fresh.

Read The Release Notes

Please read the release notes. There actually might be a few things you need to complete before upgrading to GA especially if you are using Stretch Databases.

Everyone, should validate if they need to apply KB 3138367. msvcr120.dll should be version 12.0.40649.5 or higher.

Here are screen shots of validating that even Windows 2012 R2 Server with SQL 2016 RC3 needs the .NET update.

preKB3138367

Here I am applying KB 3138367.

KB3138367

KB3138367finished

This is restart number one for anyone who is counting at home.

validateKB3138367

This validates that msvcp120.dll is on the recommended version 12.00.40649.5 by the SQL Server 2016 Release Notes (Seriously, please read these..)

 

Questions You Should Ask Before Upgrading?

Does the business need to or wants to upgrade? Will this upgrade even be possible? Just because it was allowed in SQL 2014 doesn’t mean it will be possible with SQL Server 2016.  Will it be supported? Can I change editions from Evaluation to Developer (Now free in SQL 2016) or Enterprise, Standard?

Upgrading SQL Server 2016 RC3 to GA

The following are the screen shots. I will add some more detailed text in here tonight per step.

Upgrade1

Upgrade2

I am using the developer edition for testing here. It’s cool that I can flip from evaluation to developer mode.

Upgrade3

Upgrade4

Upgrade5

Upgrade6

Upgrade7

Here we can see that it is possible to upgrade RC3 as its detected as an existing upgradable version of SQL Server for SQL 2016 GA.

Upgrade8

Upgrade9

The instance ID changes even though we are able to keep the same named instance. This gets up a separate root folder for the SQL 2016 GA install.

Upgrade10_NotExpected

The server configuration caught be off guard as I expected seeing the database engine and sql agent services listed as well.

Upgrade11

Upgrade12

Upgrade12b

Upgrade13

 

Upgrade14_Finished
Upgrade14_SSMS

Finally, you can see the SQL 2016 RC3 Evaluation Edition was able to be upgraded to SQL Server 2016 GA. I was also able to change this from Evaluation Edition to Developer Edition as well.

My SQL Saturday Houston Story..

SQL Saturday Houston was Awesome!

SQL Saturday Houston was Awesome!

This past weekend I had the privilege of speaking at SQL Saturday Houston. I enjoyed catching up with some friends while I also took the time to make some new ones as well.

The Talks

This year I gave two different sessions.  The first one in the morning was Azure Databases for DBA’s.  The second one was Why Did My Plan Change, Intro to Query Store. 

This was the first time I presented my Azure Databases for DBA’s talk at a conference. I got some great feedback and it was a lot of fun. I ended up using videos for the demos to make sure they would fit within the timeline of the presentation. Running the High Availability and Disaster Recovery Virtual Chapter I am always thinking of backup strategies this even includes my own demos  😉

At the end of the day I gave my “Why Did My Plan Change: Intro to Query Store” presentation. I was able to tell some real-world stories about lessons learned while monitoring plan changes and explain how upgrades can greatly improve performance when you find the few queries that run slower after an upgrade and fix them.

THE CAR THAT WOULDN’T START

Leaving the event I though I was on my way to the after party when I realized my 1990 Volvo wouldn’t start. Some people would be  upset and frustrated but looking back a few days later, I was blessed.  Let me explain. Instead of being stranded by myself Jamey Johnston stayed to help. He made sure I would be okay.  The bonus in this for me was that I was able to chat with Jamey and get to know him better. He is an amazing guy. I wish we had a lot more people like Jamey in our SQL Server Community!

 

Replication for Azure Databases – #TSQL2SDAY #77

If you’re not familiar, T-SQL Tuesday is a blogging party hosted by a different

Transactional Replication for Azure Databases

Transactional Replication for Azure Databases

person each month. It’s a creation of Adam Machanic (b|l|t), and it’s been going on for a long time (77 months to be exact). The host selects a topic, defines the rules (those are almost always the same), and then everyone else blogs about it. Once the deadline is reached, the host will summarize each of the submitted posts on their site/blog.

This month, Jens Vestergaard is the host and he is asking the following question.  What is your favorite SQL Server feature?  I am going to use this opportunity to talk about my favorite new Azure Database Feature that was added this past year.

TRANSACTIONAL REPLICATION TO AZURE DATABASES

That’s right, and this won’t surprise my #SQLFamily because they already know that I love some transactional replication. Yes, as a Database Administrator I love this feature more than Query Store. I also think this will be a game changer.  Here are three reasons why.

The Proof Of Concept Your Boss Will Approve

So you are interested in trying out Azure Database but your boss says, “Yea, that’s not going to happen! Implementation risk is too high.” Go ahead and insert basically any sentence excuse you want. I am sure you could compile your own list.  This is where you can tell your boss, “Do you remember the support calls we get when James in Finance  runs his ad-hoc queries (more on this in a bit) or the that John was using Access again against our database? Wouldn’t it be nice if we can offload that activity and use it as a use case to see if Azure Database can work for us at the same time?” Would it be nice to do this without buying new hardware?  Wouldn’t it be nice if this copy of data was kept in-sync so you didn’t have to refresh it? Wouldn’t it be nice if it worked using normal tools that are included with SQL Server?

That’s right, with transactional replication we can now get those users who we don’t want anywhere near our teir-1 OLTP critical databases in Azure. You can provide them their own data center and let the reporting users be your test case for working with Azure Databases. If it doesn’t work, you can easily bring them back on-prem.

Offloading Real-World Ad-Hoc Reporting

In the field, I see a lot of people using Availability Groups to have a near real-time replica for reporting.  I talked a little bit about this above.  What isn’t mentioned here is you have to maintain a Windows Failover Cluster, Quorum, Active Directory (Unless using Windows 2016 Preview) and more. This gets you a replica that is just a copy of the database. What does this mean? You cannot change database objects like security, indexes, etc. Also, what if you don’t need the whole database(s) for reporting? If not, you can replicate only the data you truly need.

So, let’s recap here.  You only have to replicate the data that you need.  You can have different security and indexes on your reporting subscriber database(s).  The reporting subscriber database can be scaled up or down as needed based on your needs.  The reporting database can now be an Azure Database. Folks, I call this a huge win!

Easy Migration to Azure Database

As databases get bigger they become harder to migrate.  I learned this first hand when I had to migrate and make an 80TB database highly available with Availability Groups.  The larger your database the more downtime is required to deploy your existing database to an Azure Database.  Well this isn’t true anymore if you have primary keys on every table. You could pre-sync the data migration process in advance.  This could greatly cut down your migration time and get you to no downtime during your migration.

In my next blog post, I will walk you through a step by step guide to creating a Transactional Replication Subscriber that uses an Azure Database.

3 Reasons to Attend SQL Saturday Austin on Jan 30th

The Austin SQL Server User Group will host its third SQL Saturday on Saturday,

SQL Saturday Austin on January 30th, 2016

SQL Saturday Austin on January 30th, 2016

January 30th. SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free ($15 for lunch), all costs are covered by donations and sponsorships. This all-day training event includes multiple tracks of SQL Server training from professional trainers, consultants, MCM’s, Microsoft Employees and MVPs.

Here are three reasons why I am excited to attend the SQL Saturday in Austin.

PreCons

While the SQL Saturday is free, there is also two separate all-day classes on Friday, January 29th that are dirt cheap compared to the cost of attending these classes at your local training center.

Have you ever wanted to learn how to make SQL Server go faster?  In a single day, Robert Davis will show you Performance Tuning like a Boss.

Have you wondered how you can keep your data highly available when your servers go bump in the night?  Ryan Adams will be teaching a class on Creating a High Availability and Disaster Recovery Plan.  Having a solid recovery plan can make you a Rockstar DBA and also help keep your company in business.

Sessions

In Austin we are blessed to have some of the best teachers come to town to share their knowledge.  We will have Connor Cunningham from the SQL Server Product team talk about the new features coming in SQL Server 2016.  We will have several MVP’s and MCMs sharing their knowledge.  If you want to learn about SQL Server there is not a better venue to do so than a local SQL Saturday.

Networking

Are you the only DBA or data professional working at your company?  If not, are you interested in meeting people who are as passionate as you are about data? If so, SQL Saturday is a great place to meet and network with some of the best data professionals.  I will never forget my first SQL Saturday. I found some vendors that had tools that made my job easier.  I also built some friendships that have helped me thought out my career.

My PASS 2015 Vote

Today I got my annual email from PASS HQ to vote for the Board of Director elections.  I always like to vote for who I think can add value to my favorite IT organization.

Today, I am happy and proud to vote for a new candidate who I know will be a great leader for PASS.  I have known Ryan Adams for years.  I remember meeting him for the first time at the 1st BI SQL Saturday in Dallas.  It’s amazing how much stuff Ryan has accomplished since that day as a volunteer for PASS.

The reason why I am voting for Ryan is because he has already done the work I would expect from a PASS Board of Director.  He is the example for what a Regional Mentor should be.  I would know as I was his co-RM. Ryan took  loose requirements where very little is required and make sure his region is successful.  Ryan also manages the PASS Performance Virtual Chapter and created the Performance Palooza that keeps growing every year. In his free time, he also helps out with the Dallas SQL Saturdays and is also on the board of directors for the Dallas User Group.

I look forward to seeing Ryan on the Board of Directors. It’s amazing how much he gives to the community without ever expecting anything back!

Adding a VLDB database to an SQL Server Availability Group

Recently, I was tasked with making an 60TB database highly available by adding it to an availability group. The following, is how I successfully completed adding the very large database (VLDB) to an Availability Group that had two replicas in the primary data center and a third replica in another data center with no downtime. We wanted to utilize the least amount of time during the initial synchronization process so we leverage log shipping to prepare the database on each replica.

We will call the three servers DC1Server1, DC1Server2, and DC2Server3.  We will have a pre-created AG called SQL2014AG1. We will say that the 60TB database is named VLDB.

Previously we did a side by side upgrade to upgrade VLDB from running on SQL Server 2012 to SQL Server 2014 on new hardware.  Previously, we also leveraged the new hardware to upgrade a subset of the data and build out the Availability Group for testing and training purposes.

  1. Create Availability Group. This was created in advance to deliver a proof of concept and to provide training for understanding how to use and manage an Availability Group. Therefore, there was no reason to break this and recreate it. If needed, you could obviously create the AG as part of this deployment where we just Add the VLDB into the AG.  Creating an Availability Group will not be covered here.
  2. Configure Log Shipping. The primary database in log shipping should be on the instance that is also the read/write replica for the availability group. In this case, VLDB already exists and is configured as primary on DC1Server1. This is also the primary replica for the SQL2014AG1 availability group. If you need examples on how to configure log shipping checkout my article on MSSQLTips.com.
  3. Identify the Log Shipping backup Job. This job usually starts with LSBackup_ for your database. This will be on DC1Server1.
    1. Execute the SQL Agent Job to take a log transactional log backup
    2. Disable the SQL Agent Job when the backup completes.
  4. On the secondary servers (DC1Server2 and DC2Server3) identify the LSCopy_ job for your database named VLDB.
    1. Execute the SQL Agent Job to copy the last log backup that you just created in step three.
    2. Disable the SQL Agent Job when copy backup job completes.
  5. On the secondary servers (DC1Server2 and DC2Server3) identify the LSRestore_ job for your database named VLDB.
    1. Execute the SQL Agent Job to restore the backups pending to be restored. This should include your backup taken in Step four.
    2. Disable the SQL Agent Job when the restore completes.
  6. Validate that the last log backups were copied and restored. This can be done using sp_help_log_shipping_monitor stored procedure on each instance. We would do this on DC1Server1 to validate the last log backup that was completed. On DC1Server2 and DC2Server3 we can validate that the backup was copied and also restored.
  7. On the primary server DC1Server1 add the VLDB to the existing availability group named SQL2014AG1. You can do this via the GUI.
    1. In SSMS under Availability Groups expand “SQL2014AG1” Availability Group and right click on Availability Databases and select “Add Database” to add VLDB.
    2. Select the “VLDB” database in the checkbox
    3. Select Join Only to start data synchronization as we used Log shipping to keep the VLDB in sync across all servers.
    4. Connect to all existing secondary servers (DC1Server2, and DC2Server3)
    5. Validate adding the Database to the Availability Group
    6. Add the Availability Group to your Availability Group named SQL2014AG1
  8.  Validate that Adding VLDB was successful to SQL2014AG1
    1. Remove log shipping on DC1Server1 for the VLDB database.
    2. Enable your log backup process for all three servers. This would be used to replace log shipping for taking transactional log backups.

 

 

8 Hours of SQL Server Performance Training This Week!

Check out the annual Performance Palooza hosted by the Performance Virtual Chapter on July 23rd, 2015.  This is a virtual online event open to the public and it’s absolutely free.  We’ll have 8 back to back sessions with start times from 10am to 5pm Central Time.

I am honored to be selected to speak and kick off the event.  I think you will love the other sessions too. Check them all out below.

 

Central Time Speaker Topic
10am John Sterrett Make Your SQL Server Queries Go Faster
11am Warner Chaves Building High Performance SQL Servers Virtual Machines on AWS and Azure
12pm Andy Galbraith Performing a SQL Server Health Check
1pm Matan Yungman Columstore Indexes – Questions and Answers
2pm Neil Hambly Performance Troubleshooting Using DMVs
3pm Tim Mitchell Maximizing SSIS Package Performance
4pm Trayce Jordan Troubleshooting Seconday Replica Latency
5pm Mike Fal SQL Server Benchmarking: The Powershell Speedometer

SQL Saturday Austin is looking for Pre-con Speakers

In January we the Austin SQL Server User Group had a blast putting on SQL Saturday #361. We had so much fun we wanted to do it again on January 30th 2016.  We are currently looking for both regular speakers and also pre-con speakers.  If you would be interested in speaking the call for abstracts is open until November 15th.

If you are interested in giving an all-day paid session on Friday, January 29th 2016 please send your abstracts and marketing plans to promote the event to sqlsaturday461 @ sqlsaturday.com. We plan to have selected pre-cons announced by the end of July.

Developers Make Your SQL Server Queries Go Faster!

Being that today is my birthday I thought I would share a nice gift to everyone who follows my blog. Below is a free link to a recorded video of one of my latest presentations that goes over multiple SQL Server developer anti-patterns I see repeated over and over again in the field. Most of these are patterns that any IT professionals can identify and fix just by identifying the pattern and changing the pattern to another one shown in the video that is optimizer friendly.  I promise no brain surgery is required 😉

Make Your SQL Server Queries Go Faster

I hope you enjoy the hour long video on making your SQL Server queries go faster!

Bonus Material

If you want to play along feel free to download the sample scripts.  If you want to learn more about being a proactive performance tuner check out my root cause performance analysis blog series which includes my What Is Running Script, Disk LatencyWait Stats monitor processes and more..