Hot answers tagged

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.1k
18 votes
Accepted

MySQL - mysql_upgrade - Error occurred: Cannot setup server variables

I found a workaround! Stop your db. Then start it with: mysqld --skip-grant-tables & And then, I could finally do my upgrade: mysql_upgrade
Pierre Ozoux's user avatar
14 votes
Accepted

SQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database '...' version 782

After poking around in SSMS for a while I noticed that on the secondary replica there was a pause icon next to the Availability Databases. The primary had shown both were "green", but there was an ...
Greg Bray's user avatar
  • 1,003
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

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

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
  • 83k
12 votes
Accepted

Upgrade PostgreSQL 9.4 to 9.5 : LC_ALL unset

After many attempts to fix this problem, this command finally worked for me: export LC_CTYPE=en_US.UTF-8 export LC_ALL=en_US.UTF-8
Erik van de Ven's user avatar
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,630
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
10 votes

Risks involved in upgrading to SQL Server 2008 R2

So my question here is not how, rather will like to know the risk involved or any pre-checks that can be done before planning this upgrade? You should run upgrade advisor and address the issues ...
Kin Shah's user avatar
  • 62.1k
10 votes
Accepted

Risks involved in upgrading to SQL Server 2008 R2

That's a really big question so let's break it up a bit. What can I do in advance? Start with some required reading. 2008 R2 Backwards Compatibility 2008 Backwards Compatibility These links have ...
Kenneth Fisher'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
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
  • 27.8k
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

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

Why is my @@Version unchanged after upgrading?

I found the problem: The installer I had was just for SQL Server Management Studio, as @MarkSinkinson pointed out. If you have this same problem, verify you have the correct installation file (not ...
Mol's user avatar
  • 109
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

How to do a minor upgrade of PostgreSQL on Windows, e.g. 9.3.0 to 9.3.1?

For the record, running the installer for Windows from Enterprise DB (the default, linked from the Postgres site), at least since the current version Postgres 9.4, you don't have to stop the ...
Erwin Brandstetter'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
  • 69.7k
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
  • 69.7k
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

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

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
6 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
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

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