Hot answers tagged upgrade
42
Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012.
A Longer Answer on SQL Server Upgrades/Migrations
So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said ...
10
30-90 minutes according to Oracle's Best Practices for Upgrading. This is about the closest estimate you will get given all the unknowns in this situation.
The size of the database really matters very little in determining how long the upgrade will take. Here are the main factors effecting the duration (from the Oracle.com upgrade blog):
Number of ...
10
In my experience, the same decision making process should be made as prior. AFAIK there hasn't been any 'world changers' with SQL Server installation, within the MS SQL Server product in itself, and the potential problems you have when rolling out software with millions of lines of code. Something bad could happen and now you're stuck with no 'ROLLBACK' ...
7
Typically the features that you don't hear about from marketing are the ones that don't bring in all the money (e.g. pushed as "enterprise feaures" in order to sell Enterprise Edition). I answered a similar question here that provided a list of my favorite new features in 2012, that also aren't limited to Enterprise Edition:
What are Objective Business ...
7
Given step 9 in the link you provided:
9.Run pg_upgrade ... Obviously, no one should be accessing the clusters during the upgrade. pg_upgrade defaults to running servers
on port 50432 (sic) to avoid unintended client connections. You can
use the same port numbers for both clusters because the old and new
clusters will not be running at the same ...
6
I tend to follow the following rule for existing code-base:
If it's working fine, there is no need to upgrade unless there's a specific feature you are missing.
InnoDB does get some nice performance boosts in 5.5, but if your current use-case is not seeing any issues with your install, why upgrade?
If you ran some performance tests on a development ...
6
If you don't:
it will be unmaintainable at some point because of OS, version, patch, whatever
hardware failure may force an upgrade at the wrong time
What you gain:
older code can be simplified with new constructs (eg ROW_NUMBER)
far better error handling (TRY/CATCH)
engine improvements: most queries will run quicker on a later version
MS don't ...
6
No, it shouldn't prevent the table from being altered. Though, if you drop the underlying table a view depends on, or alter/remove the columns from the table the view uses, the view can become invalid. You can check what views are invalid in your system with the following query:
sql> select name from dba_objects where object_type = 'VIEW' and status = ...
5
Presumably, you have a development and test instance of this database running on similar hardware with a similar data volume and the same database components installed, correct? And, presumably, you will be upgrading these lower environments (and testing that whatever applications use this database still function correctly), correct?
Assuming that is the ...
5
Without any information on the nature of the system (see my comment on the question) or why you're upgrading, it's difficult to offer any specific and/or concise advice.
As a starting point, there are plenty of excellent checklists for building a new server, Brent Ozar and Jonathan Kehayias are two good examples. From the many recommendations in those ...
5
SQL 2008 to SQL 2008 R2 is not a big switch, but if you are going from Windows 2003 to Windows 2008 you will need to address the host based firewall. Other thigns to be concerned about are Agent jobs, permissions in master/msdb/model, migrating logins from one server to another, whether to do a backup restore to new server or in place upgrade, compatibility ...
5
You need to get away from MySQL 5.1 ASAP
MySQL 5.0 Active Support ended on December 31, 2009 - Now in Extended Support Phase
Per the MySQL Support Lifecycle policy, active support for MySQL 5.0 ended on December 31, 2009. MySQL 5.0 is now in the Extended support phase.
Versions have changed rapidly since Oracle stepped in
MySQL 5.1.61 released 2012-01-11
...
5
Yes, according to Supported Version and Edition Upgrades you can upgrade in place from 2005 Standard to 2012 Enterprise, provided SQL Server 2005 is currently on an operating system supported by SQL Server 2012 Enterprise (meaning forget about XP or 2003 RTM) and that the architecture is the same (e.g. 64-bit -> 64-bit).
Though you should do step 1 and 2 ...
5
It's failing when trying to run the old bin files because it's missing a dependency. You're going to need to install its dependencies, install 8.4 on the new server for this task, or spin up a VM with 8.4 installed, copy the files to the VM, do what it takes to start the 8.4 instance on the VM (which means that postgres will need to know where the default ...
5
Advice
Never manually apply database changes; use scripts that are in a version control system (VCS).
After initial deployment, store the "delta" scripts in the VCS. When upgrading, apply all scripts that are missing. Tracking the scripts that have been applied is a standard problem.
Solutions
There are tools to automate this task, including:
Liquibase
...
4
Since 8.4, pg_upgrade is the alternate choice to dump-restore for upgrades between major versions. It is actually linked at the end of the doc page on migration you already mentioned.
As for replaying WAL files from an older version, the doc says we can't:
In general, log shipping between servers running different major
PostgreSQL release levels is ...
4
The postgres database is just the default database that is created during initdb. It has no special meaning.
If you created a different one where all your data is kept and you never created tables in the postgres database, then there is no need to dump it.
I don't know what pg_upgradecluster is. The default tool to upgrade the data directory (apart from ...
3
Your databases will remain untouched (though of course I can't be liable :) ).
The reason are are being asked for a password could be:
Your new Ubuntu version uses a newer version of MySQL (it does). It needs root password so as to run the mysql_upgrade utility, which upgrades internal mysql schema to fit new version
You new installation includes new ...
3
Compatibility level simply changes the language syntax that is supported by the SQL Statements. This will cause all the execution plans to be expired and force them to be recompiled. It has nothing to do with the data storage engine or the way that the data is written to the disk.
Once a database is attached to a SQL 2012 instance, it is in the SQL 2012 ...
3
There's no standard process because every system is different. About the last thing that I would do if just wrap everything in a single transaction. What happens if I need to move 500 Gigs of data around? That's one massive transaction.
Recently I've been using database snapshots as my rollback.
Basically take a snapshot, make the changes. Delete the ...
3
initdb doesn't return until it's finished, so there shouldn't be any pause needed between it and server startup. There have been bugs in PostgreSQL where it completed without flushing everything to disk first though. I don't know of any left right now, but the nature of bugs is that you don't always know about them.
If you use the pg_ctl command to start ...
3
This may produce some false positives (e.g. you may have *= in a comment), but should be a good start:
SELECT
obj = QUOTENAME(SCHEMA_NAME(o.[object_id]))
+ '.' + QUOTENAME(o.name),
o.type_desc
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.[object_id] = o.[object_id]
WHERE m.definition LIKE '%=*%'
OR m.definition LIKE '%*=%';
3
Well one thing you may want to consider doing is validating your application(s) still work on a test instance that contains the same database and upgrading it to the newer service pack. While rare and usually changes are backward compatible, there could be behavior changes to things like the optimizer that would be impossible to guess exactly how they might ...
2
Of the choices, I would update, then convert.
InnoDB received some massive performance boosts in 5.5, so by upgrading first you get some features that 5.5 introduces, and then you can convert to InnoDB to get full innodb benefit.
5.0 InnoDB performance was not great, so you might see some strange issues by converting to InnoDB prior to updating to 5.5
As ...
2
Unfortunately, without knowing your system (inside and out), it's going to be difficult for anyone to tell you how an upgrade is going to impact you.
I'd recommend checking the release notes to verify that the upgrade path won't mess up the application.
Here are a couple changes that I noticed that you might need to know about:
Remove ipcclean utility ...
2
Well, first off, if you are upgrading from 5.0 to 5.5, you really need to do this on a test server prior to making the upgrade in a production environment. This will get you comfortable with the steps of upgrading and let you figure out any trouble areas beforehand, which will dramatically reduce your downtime of upgrading.
So to upgrade, I would upgrade ...
2
I would try the following:
Install an oracle 10g database software that I download from the oracle software download page (i have the appropriate license)
create an empty database with this software
using the exp-tool (classical export, not datapump) to make an export from the database
import the dumpfile (with the classical imp tool) in the empty 10g ...
2
The two constraints that you have specified:
Very low downtime
Limited disk space use
conflict rather seriously.
You have (wisely, IMO) excluded the option of using pg_upgrade with a custom rebuilt Pg, which is the only option I see that'd satisfy both those constraints.
I suspect you'll have to drop the disk space constraint. The only way out of this ...
2
Wow, good question. PG 9.2 has vastly improved pg_upgrade logging, for the exact reason you are seeing here --- that failures just aren't reported well, particularly on Windows where we can't output two streams to the same file. Is there a reason you are going to 9.1 and not 9.2?
It is also odd that the stop failed, while the start worked. Usually the ...
2
Make sure you include the /IAcceptSQLServerLicenseTerms switch, otherwise your installation will fail. I have commonly done silent installs of service packs to using the following syntax:
.\<SQLServicePack>.exe /q /action=patch /instancename="FOOBAR" /IAcceptSQLServerLicenseTerms
Full syntax documented here.
Only top voted, non community-wiki answers of a minimum length are eligible
