SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Tracking the Change

The Voice of the DBA

Upgrades are Hard

I think the people that run StackOverflow are pretty sharp. They've built a well performing site, they've worked on some useful open source projects, and they think through their projects. They still have issues with upgrades.

Taryn Pratt, from Stack Exchange, has written a few nice posts about her experiences upgrading the Stack Overflow databases. The first one was last year, where their multiple server AG environment upgraded from SQL Server 2012 to SQL Server 2017. Just recently she published another post on their Windows Server upgrade, from 2012 to 2016.

If you've never done a complex upgrade, these posts are worth reading. Don't second guess Taryn, but rather, just read as if you are following along. You have hindsight now, but in the middle of planning this, you will learn about things that might cause you issues. In the Windows upgrade, one of the interesting issues is a VM vs. physical machine issue with drivers. To me, this might be one reason to never bother with anything other than a VM, even if it's the only one on the machine. A lightweight version of Hyper-V or Xen doesn't eat much in the way of resources, but can provide some separation from these issues.

One other thing to note is that you really need your runbook. I constantly see people asking for a checklist for how to upgrade, and there are good general steps to follow, but your environment likely needs to have a custom runbook that covers your situation. The Stack environment is complex, but even I was surprised with 35 pages of steps and notes.

As with most plans, this one had issues when it was finally implemented. I think Mike Tyson sums it up nicely, which is why you practice your move. It's also why we can't necessarily upgrade every year. There's a reason many companies still have old versions in production (Thanks, Brent Ozar).

Plan, practice, test, repeat again, and then be prepared to think on your feet. That's if you upgrade your systems. It's time consuming and expensive, and I can see why a lot of companies have looked at cloud services, like Managed Instances. Reducing the time and cost to change your OS and/or SQL version is something we should all be thinking about.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

Dynamic SQL for beginners in 33 minutes

arthur from SQLServerCentral

Learn some of the basics of using Dynamic SQL in a short series of videos.

Measuring the Right Things

Additional Articles from SimpleTalk

In this weeks Simple Talk editorial Kathi Kellenberger discusses how to measure the performance of a development team and why you should always make sure you are measuring the right thing, not just the easy thing.

How to boost team productivity with SQL Clone 4

Press Release from Redgate

Wednesday August 21 16.00-17.00 BST/ 10.00-11.00 Central - SQL Clone enables dev, test and CI environments to be created and refreshed in seconds, on demand or through self-service, with the latest copy of production data, masked for compliance.

From the SQL Server Central Blogs - Execution Plans: Statistics

Bert Wagner from Bert Wagner

Watch this week’s Statistics video on YouTube. Last week we looked at what execution plans are and how you can view them. This week I want to discuss what data...

From the SQL Server Central Blogs - Information Measurement with SQL Server Part 4.6: The Jaccard Distance and Its Relatives

SQLServerSteve from Multidimensional Mayhem

By Steve Bolton …………Over the course of this inexpert exploration of the distance measures used for such data mining applications as clustering, we’ve gradually introduced a system of classification...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Tracking the Change
My SQL Server 2017 instance is acting strangely. I find that the cost threshold for parallelism has been reset back to 5 sometime in the last day. Where can I look to find out when this value was changed?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Modern Datatime Addition

What happens with this code?

DECLARE
    @MyDate DATE = '2015-08-27'
  , @MyTime TIME = '15:33:21.057';
SELECT MyDateTime1 = CAST(@MyDate AS DATETIME2) + CAST(@MyTime AS DATETIME2);

Answer: Returns an error since we cannot add dates and times

Explanation: This is similar to another question (https://www.sqlservercentral.com/questions/get-the-datetime), but this time an error is returned. Addition is not allowed with the datetime2 type. Ref: datetime2 - https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017

Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration

BAG - how to change disk drive letters for database files - I've set up a few BAGs on the sql cluster instance. When defining the default locations for data, tlogs and temp database files I use another drive for each e.g. D: for data, E: for tlog and F: for tempdb. The problem is that I used the wrong drive letters, meaning I messed up the […]

Log Shipping - multiple time intervals - On SQL Server 2017 I've set up log shipping. My question is, since it seems like it is not possible, is it possible to set up log shipping with 2 different time intervals for backing up and restoring tlogs e.g. every 30 min from 7 AM to 3 PM and after 3 PM backup and […]

SQL Server 2017 - Development

SQL equivalent to NORM.DIST Excell function? - Hi, I was wondering how I would go about calculating the normal distribution for a number of values, specifically the Probability Density Function. I know there is a function in Excell that performs this but I need to do this in SQL (I'm trying to create a bell curve graph in SSRS). […]

What are the most common system tables, views, stored procedures, functions, etc - What are the most commonly used database objects that I should become familiar with? I'm trying to apply the Pareto Principle (80/20 rule) here to save myself time.

SQL Server 2016 - Development and T-SQL

update table all 0 values to null single query - Hi Team, please help on this below update query. My data looks like: create table #a(a int,b int,c int,d int) INSERT INTo #a select 0,1,0,2 union ALL select 0,2,0,3 union ALL select 0,3,0,4 select * from #a a b c d 0 1 0 2 0 2 0 3 0 3 0 4 expected […]

XML file to sql table - Hello Guys, Can anyone help me please? I have a xml file, please see attached pic. When i try to import xml file to a sql table "ReplacedForblog_primaryText" column's value does not translate correctly. All other columns are translated correctly but this one is not. It brings some string in front of actual data. Numbers […]

SSIS and flat file column headings with special characters in them - Thanks in advance for any help! I have a csv file that has a header row in which the header column titles have some special characters in them. For example, the column for a diagnosis code (we are a healthcare organization) might have the heading "Diagnosis: Primary". For reasons I won't get into here, I […]

Group by and a list. - Suppose I want to do a count, grouped by an article and some columns I do not want to group on but I want some examples of the content. Is there a 'simple' solution for this. (Not using FOR XML) Example result. Counted Article Colors […]

Administration - SQL Server 2014

Sessions with no query running, but an open transaction? - This has cropped up on one of the servers I maintain. The end user will report a problem, I'll use a query to check running queries which will show a fair number of blocked queries. The head blocker however, does not show a query. In doing further (and better) research on this today when it […]

SQL 2012 - General

BCP in to SQL DB - Hello, I'm trying to import some data from a .dat plain text file to SQL using the following BCP command. bcp dbname.dbo.tablename in DataFile.dat -f format.fmt -F2 -T -S "servername, ####" > bcp.log After the data imports to SQL. We have a tab at the start of our data for each row in the first […]

How to Query an Excel file with mutiple cells for one single ID - Hi Folks, My first question here. This may be lengthy, sorry in advance:-) I am looking to read an excel file into a SQL Server table. The issue I am having is the data entry in the excel file was done in such a way that a person can have multiple pieces of software listed […]

BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION - I do a lot of ad hoc update, insert, delete statements and was wondering if I should encapsulate using BEGIN... ROLLBACK... COMMIT to give me an out if something goes wrong like below: BEGIN TRANSACTION UPDATE B SET expired = -1 FROM GCDF_DB..BillingThirdNotice B INNER JOIN GCDF_DB..Certs C ON B.PeopleID = C.PeopleID WHERE C.certificationExpireDate < […]

SQL Server 2008 - General

SQL Server Agent Job - Decision Extract Question and Purpose - Hello Support Forum, I've inherited a 2008 R2 server and after scrubbing through the maintenance Plan and Job setup, I came across a scheduled event with the purpose of conducting a "Decision Extract" for an entire Database (that you specify). I've looked at the Query and can follow it no problem but I'm unaware of […]

SQL Azure - Development

RLS and Multi tenancy - Hi All, I am looking at implementing RLS in my multi tenant databases and was trying to think of a way of being able to extend access to a group of tenants rather than a single tenant. One tenant (the owner of the record) would create and maintain group access but I am not sure […]

Reporting Services

Creating bell curve in SSRS - Hi, I have a report that shows how long we have spent on certain matters (I work for a solicitors). I round the amount of time spent on a matter to the nearest hour so we could have 5 matters where we spent 1 hour, 3 matters where we spent 2 hours, 1 matter […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.