0

I don't know if this is a stupid question, but I'm upgrading my test instances to SQL Server 2014. I've installed SP1, after I will install Cumulative Updates.

It's working fine, but is there a way to test it? Like some queries or procedures that would fail with a bad upgrade?

From sqlserverbuilds, I can see that I have the last SP1 ( the only one for SQL2014 ).

enter image description here

Paul White
  • 94,921
  • 30
  • 437
  • 687
Racer SQL
  • 7,546
  • 16
  • 77
  • 140

2 Answers2

1

As TheGameiswar has mentioned, the upgrade adviser is incredibly helpful for pointing out issues that can arise with the upgrade. However, it will only check for things IN the database. So if you have ad-hoc queries being ran, or worse yet sql files scattered all over the place, those will not be tested.

When I upgraded our SQL 2000 servers to 2014, we had ad-hoc queries and sql files all over the place that were being executed and we needed to test to make sure they would not break as best we could. So what I ended up doing was copying our production databases and upgrade them to 2014. After that, I started doing replay traces on production for a week to capture all queries being ran. This allowed me to replay those traces on the 2014 servers to see if there would be any issues. It helped me find a number of issues that needed to be fixed.

One very important thing to note when doing replay traces is you need to make sure the database IDs match between the production and dev environments as that's how the traces know what database to run each query on.

Eric S
  • 503
  • 1
  • 6
  • 12
1

I'm not 100% certain if you are asking for advice on your upgrade to SQL 2014, or if you are worried about applying a CU to your SQL 2014 SP1 instance.

Let's assume you are asking about the latter.

Here's what you need to consider when it comes to SQL Server patching. This applies to all Microsoft patches - SQL SPs and CUs and Windows OS patches:

  • You've probably heard this one before: Make sure that you install the particular patches on a non-production system first, before you install them on your production servers. And leave about 1-2 weeks of time between installing in non-production and installing in production.
  • If your non-production environment is not getting load-tested and doesn't 100% match your production environment, you can't be sure that you won't hit some weird bugs only after you've installed the patches in production.
  • For the case that you don't load-test your non-production environment, the best thing to do is to listen to the SQL DBA community on Twitter, just in case that Microsoft has made a 'boo-boo' and released some major bugs that seriously breaks things.
  • One more thing to consider is TF 4199 (query optimizer hotfix traceflag). If you have this flag enabled, this will enable query optimizer fixes that are included in SQL Server patches after you've installed a patch that includes a query optimizer hotfix.

Note: TF 4199 kinda,sorta has been replaced with COMPATLEVEL settings in SQL 2014 though, but then Microsoft changed their minds and starting using 4199 again in 2014SP1. Now Microsoft is trying to get rid of 4199 again in SQL2016 - https://support.microsoft.com/en-us/kb/974006

Christoph Wegener
  • 371
  • 2
  • 6