35
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, but I'll give a few reasons that come to mind. Including one very big reason we just learned about with SQL Server 2016 SP1's release in November of 2016. ...
30
I hacked the problem by running (as root):
mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> \
/usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl
Run pg_upgrade as intended, then undo the hack:
mv -f /usr/bin/pg_ctl{-orig,}
The problem is that ...
26
This is how I solved my problem.
Upgrade Postgresql 8.4 to 9.4 in Centos
1. Yum Install PG9.4
2. wget http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm
3. yum install pgdg-redhat94-9.4-1.noarch.rpm
4. yum install postgresql94-server
5. service postgresql-9.4 initdb
6. chkconfig postgresql-9.4 on
Backup Data
7. su - ...
17
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
14
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 very expensive mistake and if the work you are doing is on any company asset please contact the company's DBA. That's what they are there for.
That said, let ...
13
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 in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access ...
12
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
12
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\instance_name otherwise the agent will still error out and shutdown.
Also, the Agent was disabled in sp_configure
sp_configure 'Agent XPs', 1
go
reconfigure ...
11
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 option on the secondary to Resume Data Movement. I resumed the first database, and immediately the In Recovery status message was removed. A minute later it ...
11
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 Database Compatibility Level and use the Query Store
If you have a lot of plan regressions you can keep using the older cardinality estimator at the higher ...
answered May 29 '20 at 23:33
David Browne - Microsoft
34.4k3 gold badges36 silver badges76 bronze badges
10
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 links to further information such as
Deprecated SQL Server Features
Discontinued SQL Server Features
Breaking Changes
Behavior Changes to SQL Server Features
...
10
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 reported by it before migrating.
Refer to my answer for an extensive list of pre and post upgrade steps.
SQL server which needs to be upgraded from version 2005 ...
9
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 syncing without any issues but the DAGs were in a Not Synchronzing / Not Healthy state
If the individual databases and AGs underlying the distributed ag say ...
answered Mar 15 '18 at 0:04
Sean Gallardy - Retired User
20.8k2 gold badges34 silver badges64 bronze badges
8
On Windows, simply stopping the postgresql service, then running postgresql-9.3.1-1-windows-x64.exe on top of the existing 9.3.0 works. No uninstall necessary. Of course, a backup is recommended.
Clear, explicit documentation for the update procedure on Windows is absent. Note that the documentation link provided by @dezso has been moved in the current ...
8
This answer applies to modifying the PostgreSQL source code by applying a "diff" or "patch". It's not about installing minor version updates; to do that, just download and run the installer.
To alter the PostgreSQL server its self or its procedural language runtimes, you will generally need to recompile PostgreSQL from scratch.
On Windows that's a bit of a ...
8
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 I'm old. But for those needing to read this from MS documentation on their web site instead of peers answering questions here, here is one official source:
...
7
I have completed the migration with no problems.
Creating the dump is easy:
sudo -u postgres pg_dump --verbose --no-tablespaces --format=directory --file=/backup/path old_database_name
Restoring on a new instance: first, create a new tablespace, and a target database in that tablespace. Then import your dump like this:
sudo -u postgres pg_restore --...
7
The problem was that our new RDS instance was being throttled on writeIOPS, we could see queued disk operations (DiskQueueDepth of 10 or greater) and we could see writeIOPS averaging 300, whereas Amazon only provide 100 reliable iOPS (source).
The solution was to fork out approx $200 per month for "provisioned iOPS". 1000 is the smallest you could buy (...
7
PROLOGUE
Someone asked the same thing of me in my organization because everyone was using MySQL 5.5. All DB servers was upgraded over the past 8 months to MySQL 5.6. Some client applications were being affected by sql_mode change as well.
ROOT CAUSE
I just found out why what you did does not work and the workaround is very simple.
According to MySQL 5.5 ...
7
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. That said I haven't tried this, just going on how log shipping/mirroring work.
If it works it is only supported doing this as a means to upgrade, leaving 2014 ...
7
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 that is required.
This would preferably be a VM that you could snapshot, so if you encounter and fix a problem you can revert to the snapshot and start again ...
7
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 (which_group_id, submitted_on DESC) should serve this query much better.
Query
Formatted query without noise and with table aliases for better readability:
...
6
I've got the same problem. I was upgrading from Fedora Repo's 9.2.4 to PGDG 9.3. The source of the problem is that Fedora backports changes of parameter unix_socket_directory to unix_socket_directories (see https://bugzilla.redhat.com/show_bug.cgi?id=853353).
My solution is to rebuild the pg_upgrade from sources, with update to file contrib/pg_upgrade/...
6
ISSUE #1 : Upgrade Path
Your grant tables can get screwed up because you are leaping two versions instead of one.
I just helped someone a week ago because they did just that (MySQL service stops after trying to grant privileges to a user). The solution for that question was to manually fix the mysql.user. Rather than going through that heavy-handed route, ...
6
As mentioned in the comments, it is not recommended to install any MySQL version (or anything else) that is not 'Generally Available (GA)' in production.
But whether you end up upgrading when it is GA depends on your product and your environment. At the current time, there is this summary of features and improvements introduced in 5.7. In general, if your ...
6
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 SSMS only, or an incompatible Edition).
Other helpful things to check, from comments originally left on the question:
You may have multiple instances installed, ...
6
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 restore to 2014.
There a lot of features from SQL Server 2000 that have been discontinued in SQL Server 2014. See this list for the details about what ...
6
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 his answer on a separate question.
If you really need to do an in-place upgrade, the only real way to determine the amount of time required is to do a dry run ...
6
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 doing.
My recommendation is to have some sort of "sandbox" server and restore one of the databases and change the compatibility level to the latest level. Then ...
6
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 version upgrade when using media from a different SQL Server version.
Will your licensing agreement cover this? That wholly depends on your licensing (though ...
Only top voted, non community-wiki answers of a minimum length are eligible
