Category: Administration

Randolph West summarizes the new SQL Server patching model:

• Every twelve months after GA, the installation files will be updated to contain all the Cumulative Updates in what is effectively now a service pack, but won’t be called that. This will also become the slipstream update. In other words, you’re more likely to be up to date when installing from scratch, later in the release cycle.

• Customers on the GDR (General Distribution Release) release cycle will only get important security and corruption fixes, as before. You can switch to the standard CU release cadence any time, but once you do, you can’t switch back to GDR.

Brent Ozar thinks CU12 might become the new SP1 in the minds of managers:

So now fast forward to late 2018, early 2019. You’re about to build a new SQL Server for a project, and you have two choices:

• SQL Server 2018 – which is basically the new dev branch, getting monthly updates, or
• SQL Server 2017 (or 2016, or 2014) – which is the stable branch, getting quarterly updates

Once a version has hit CU12, and it only gets updates once a quarter, it might be considered Good Enough For Our Apps. Managers might see 2017/2016/2014 interchangeably at that point – which might be great for the second most recent version’s adoption.

It will be interesting to see how companies adopt this new model.

Paul Randal explains the unlikelihood that you’d run out of bigints in a table:

So with 1 million rows per second, you’ll be generating 1 million x 3,600 (seconds in an hour) x 24 (hours in a day) = 86.4 billion rows per day, so you’ll need about 1.4 terabytes of new storage per day. If you’re using the bigint identity as a cluster key, each row needs new space, so you’ll need almost exactly 0.5 petabytes of new storage every year.

At that rate, actually running out of bigint values AND storing them would take roughly 150 thousand petabytes. This is clearly impractical – especially when you consider that storing *just* a bigint is pretty pointless – you’d be storing a bigint and some other data too – probably doubling the storage necessary, at least.

By contrast, if you have a staging table that flows 10 million rows a day (meaning 10 million leave and 10 million new ones enter), you’ll overflow an int column in less than a year.  It’s worth thinking about data sizes before deciding on the type of a surrogate key.  Bigint is the safest, and if you think you’ll need it, go with it.  But there is that storage overhead.

Derek Colley shows what to do when trying to connect to a named instance without specifying its name:

This is a good question.  There’s a few ways to address it:

• We could configure two different connection strings and on failover, the alternative is used (by any arbitrary application mechanism)
• For mirroring, we can use the Failover Partner= element in the connection string – but this is transactional replication being used for DR, and failover should be manual
• We could set the instance names to be the same – however in this case it was too late since changing the instance name cleanly in SQL Server is practically impossible and breaks all sorts of things

None of these solutions was suitable.  However, on each server, there was only one named instance and no default instance.  What can we do?

Read on for a fourth solution.

Arun Sirpal demonstrates what the Pause option does on a SQL Server:

As stated via official Microsoft documentation “Pausing the Database Engine service prevents new users from connecting to the Database Engine, but users who are already connected can continue to work until their connections are broken. Use pause when you want to wait for users to complete work before you stop the service. This enables them to complete transactions that are in progress”.

Very handy! Let’s see it in action and compare it to a STOP.

Read on for more.

Lonny Niederstadt explains the value of a minimalistic repro:

Often among the hardest of my decisions is whether I should spend more time trying to simplify a given problem by eliminating additional factors, shrinking the data needed for a repro, etc… or just put all that effort into investigation purely aimed at understanding the behavior. I expect that to be a long-term challenge 🙂

I was quite happy with the way this particular one worked out, though. It started as a maze… access violations generated on a SQL Server 2016 instance with dozens of databases. The access violation came from an insert query using a synonym – with the underlying table in another database! (I didn’t know that was possible – or *ever* desirable – until I saw it in this context.) The AV was occurring during a multi-step data transfer process and it was hard to isolate the data flowing into and out of the two databases in question. But after some finagling, I got the problem repro pretty doggone small. Reproduced the AVs on several CUs of SQL Server 2016 and on SQL Server 2017 RC2.

If you think you’re going to enlist the help of someone outside your organization, then you definitely want a minimalistic repro.  That will reduce the risk of red herrings, reduce the burden of assistance, and make it much more likely that some poor sap in support can actually fix your problem if it turns out to be a bug.