Skip to main content
The 2024 Developer Survey results are live! See the results
35 votes

What are Objective Reasons to Prefer SQL Server 2016 over Earlier Versions?

There are a lot of reasons to consider an upgrade to SQL Server 2016. Most of the time, these reasons vary depending on the version of SQL Server one is coming from. This won't be an exhaustive list, ...
Mike Walsh's user avatar
  • 18.2k
16 votes
Accepted

Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4

Here is a query what will generate all the necessary update statements. It temporarily disables foreign key checks so that the update statements succeed. It updates the default character set for each ...
Stephen Ostermiller's user avatar
14 votes
Accepted

Can I update my Microsoft Enterprise SQL Server?

Given that you work for a company that is using Enterprise Edition I'm going to assume you aren't touching one of their servers. If you are please don't. As mentioned in the comments this could be a ...
Kenneth Fisher's user avatar
14 votes
Accepted

Inability to query underlying table of a schema-bound view

From the documentation: A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. If the query contains references to columns ...
Paul White's user avatar
  • 87.1k
12 votes

After Upgrade to 2016, SQL Server Agent can't connect to Instance

Problem resolved by adding an alias to the instance in the SQL Server Agent Configuration. The alias was defined using the fully qualified domain name of the server. Example:servername.domain.biz\...
Craig Efrein's user avatar
  • 9,660
12 votes
Accepted

SQL Server 2019 performance worse than 2012... am I missing something?

I believe you have just discovered why the recommended upgrade process is to to upgrade your database, enable the Query Store, and test before increasing the database compatibility level. Change the ...
David Browne - Microsoft's user avatar
9 votes
Accepted

Optimizing slow performance of simple SELECT query

Suspected main issues (synopsis) You need to run ANALYZE after a major version upgrade with pg_upgrade. Table statistics are not copied. Possibly tune autovacuum settings, too. A multicolumn index on ...
Erwin Brandstetter's user avatar
9 votes
Accepted

SQL Server Distributed Availability Group databases not syncing after a server reboot

Please note, this is not a definitive answer but it's the best answer after chatting with Taryn. However, the primary was showing a very different story. It was reporting that the separate AG was ...
Sean Gallardy's user avatar
9 votes
Accepted

Door Number 1, 2, or 3?...Security change, bug, or failed upgrade with an in-place upgrade from SQL Server 2016 to SQL Server 2019?

Door Number 1, 2, or 3: 2, with a dash of 3. An ownership chain should prevent permission checks on a scalar UDF referenced from a view, so a user with SELECT permission on the view should not also ...
David Browne - Microsoft's user avatar
8 votes
Accepted

SQL Server update from SP1 to SP3

Yes, you can upgrade directly to Service Pack 3. Service packs are cumulative; SP3 contains everything in SP2 and SP1. This has always been true for every version of SQL Server I’ve worked with, and ...
Aaron Bertrand's user avatar
8 votes

Eliminating OIDs while upgrading PostgreSQL from 9.4 to 12

Here is a query to help find all tables that have the WITH OIDS set. It also generates the DDL to remove. SELECT 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET ...
andres101's user avatar
8 votes
Accepted

After upgrade to SQL Server 2019 some stored procedures fail

This looks to be a problem with UDF inlining. As @ScottHodgin suggested, the first step is to install the latest CU to see if the problem persists. If that doesn't resolve the problem, your options ...
Dan Guzman's user avatar
  • 28.3k
7 votes
Accepted

Recommended ways to upgrade AlwaysOn AG from 2014 to 2016

The short answer is yes, much the same as when you mirror from a lower version to a higher version. Make sure you do not have the 2016 as readable so it's not trying to upgrade the database version. ...
Mat's user avatar
  • 544
7 votes

How far to go when Validating a SQL Service Pack install?

The ideal situation is to have a server that, in terms of OS and other software, exactly matches the production environment, upon which you can perform the update first. That way you know everything ...
David Spillett's user avatar
7 votes
Accepted

Eliminating OIDs while upgrading PostgreSQL from 9.4 to 12

If the oids of the tables are referenced in the code, you cannot simply get rid of them. Probably the old code relies on oid as an auto-generated identifier. Are there any primary keys or other ...
Laurenz Albe's user avatar
  • 54.1k
7 votes

Is it OK to have nodes with mixed major versions running for months?

We do not recommended running a cluster in "mixed mode", specifically where nodes are running different major versions of Cassandra for longer than 1 or 2 days. There is a long list of ...
Erick Ramirez's user avatar
6 votes
Accepted

Upgrade a database from SQL Server 2000 to SQL Server 2014

You cannot restore a SQL Server 2000 database onto a SQL Server 2014 instance. You need to restore to SQL Server 2008 or 2008 R2 instance first, then take a backup of that database which you can then ...
Hannah Vernon's user avatar
  • 70.2k
6 votes

Set sql_mode “blank” after upgrading to MySQL 5.6

With 5.7.13 (and possibly other 5.7 variants), the following trick works: in my.cnf: sql_mode=ALLOW_INVALID_DATES and restart the server. If you do not want to restart, you can just do: set global ...
Sasha Pachev's user avatar
6 votes

SQL Server 2016 in-place upgrade time for large databases

I'd really recommend not doing an in-place upgrade; certainly not for a very large system like yours. The reasons for this recommendation are many, and have been covered extensively by Mike Walsh in ...
Hannah Vernon's user avatar
  • 70.2k
6 votes

Will SQL 2008 SSIS package files work with SQL 2016 databases after database upgrades?

At our location, we have SSIS running on SQL Server 2012. Our databases are on SQL Server 2016. We haven't had any issues with any of them, but then again, it really depends on what your packages are ...
John's user avatar
  • 471
6 votes
Accepted

Postgres minor version upgrade

Just do a dist-upgrade or the like on Linux, or install the newer versions on Windows/OSX. Minor versions ensure total compatibility with the data directory (heap, and index). Stands to reason though,...
Evan Carroll's user avatar
  • 63.9k
6 votes

SQL Server 2008 R2 Edition Upgrade Process

You will need to use SQL Server 2008 R2 Enterprise Edition media to perform the in-place edition upgrade. I am unaware of any option available that allows you to perform an edition upgrade without a ...
John Eisbrener's user avatar
6 votes
Accepted

Is the Query Store's Overall Resource Consumption report telling me my database is doing terribly or are the numbers just whacked?

My workstation can do about 100K-150K LIO/CPU Second. A Logical IOs (LIO) is reading a single 8KB page from page cache. Ie when running a big parallel scan of a cached table with a trivial query plan ...
David Browne - Microsoft's user avatar
5 votes
Accepted

Ingredients for SQL Server Version decision

The is some information directly from MS that you might find useful: Breaking Changes to Database Engine Features in SQL Server 2016 and Discontinued Database Engine Functionality in SQL Server 2016, ...
David Spillett's user avatar
5 votes
Accepted

Upgrade SQL Server 2016 R Service (in-database)

I faced the very same problem (when trying to follow the steps described in Use sqlBindR.exe to Upgrade an Instance of R Services). I could not find any SQLBindR.exe. A friend of mine pointed out to ...
MEhrenmueller's user avatar
5 votes

After mysql_upgrade to 5.7, optimizer not using index on large IN clause

You may not be aware of this, but MySQL optimizer has a different setting between versions For MySQL 5.6, optimizer_switch looks like this: mysql> SELECT @@optimizer_switch\G *********************...
RolandoMySQLDBA's user avatar
5 votes

postgres pg_upgrade 9.2 to 9.5 problem

The issue repeated for me on a legacy PostgreSQL 9.2 to 11 upgrade. The pg_upgrade would fail with: FATAL: unrecognized configuration parameter "unix_socket_directory" Blog entry at https://...
David Ramirez's user avatar
5 votes

SQL server migration - Server object listing/scripting tool

We have servers across different firewalls, powershell sometimes does not work. thanks for the suggestion. Use the awesome dbatools. Why ? dbatools now has Export-DbaInstance to export as below (...
Kin Shah's user avatar
  • 62.4k
5 votes

AWS RDS (PostgreSQL) - Master user lost all privileges after upgrading from v9.6 to v10.6-R1

So running these two statements resolved my issue (being unable to access anything in the public schema): master_prod=> ALTER SCHEMA public OWNER to freetiersuperuser; ALTER SCHEMA master_prod=>...
m10i's user avatar
  • 91
5 votes

Is SQL Server 2016 already in a good shape to be used?

Its support ends in 7/9/2024 right? Extended support ends 7/9/2024. Mainstream support ends 5 years before that, or about a month ago. This means it's now in extended so no more bug fixes, only ...
Sean Gallardy's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible