July 14, 2014 at 9:49 am
Hi,
Current SQL Server Details-
Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
We are planning to upgrade from above SQL Server to SQL Server 2012 Enterprise 64-Bit.
As per below document (link) - it seems we can only upgrade from SQL Server 2005 Standard SP4 onwards. So does this mean
1. We will need to first Upgrade existing Sql Server (Prod DB) from SP2 to SP4 and only post that we can migrate to SQL Server 2012
OR
2. We will be able to upgrade irrespective of existing 2005 Service Pack level
OR
3. Any other approach to achieve this Upgrade
http://msdn.microsoft.com/en-us/library/ms143393(v=sql.110).aspx
Thanks and appreciate your responses,
NV
July 14, 2014 at 1:49 pm
To me it reads your option 1 would be only way to do an upgrade. Upgrade SQL05 from SP2 to SP4, then run the upgrade to 2012.
July 15, 2014 at 7:55 am
nvalia (7/14/2014)
Hi,Current SQL Server Details-
Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
We are planning to upgrade from above SQL Server to SQL Server 2012 Enterprise 64-Bit.
As per below document (link) - it seems we can only upgrade from SQL Server 2005 Standard SP4 onwards. So does this mean
1. We will need to first Upgrade existing Sql Server (Prod DB) from SP2 to SP4 and only post that we can migrate to SQL Server 2012
OR
2. We will be able to upgrade irrespective of existing 2005 Service Pack level
OR
3. Any other approach to achieve this Upgrade
http://msdn.microsoft.com/en-us/library/ms143393(v=sql.110).aspx
Thanks and appreciate your responses,
NV
Firstly, what Operating System version\edition is the Windows server using?
From 2005 SP2 you could upgrade to SQL 2008 R2 then to 2012, but really, just apply SP4 to your instance and upgrade straight to SQL Server 2012. This is a more direct route
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 15, 2014 at 8:05 am
Currently we are on Windows Server 2003 and plan to have SQL Server 2012 on Windows Server 2012
My concern with the direct route is, upgrading a Service Pack in Production (SP2 to SP4)
If we go indirect route, then do we need to get license for SQL Server 2008 R2 first to move from
2005 SP2 to 2008 R2 and then to 2012?
July 15, 2014 at 12:23 pm
what ??? no test server ???
did I understand correctly that you want to do a production upgrade without testing it ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 15, 2014 at 12:35 pm
We do have a Dev Server, not directly in Production!!
But we will still need license for the Intermediate SQL Server 2008, correct?
July 15, 2014 at 12:47 pm
I would just apply 2005 SP4 as a part of the process then go to 2012.
This way you avoid any 'potential' licensing issues.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 15, 2014 at 1:41 pm
Rudyx - the Doctor (7/15/2014)
I would just apply 2005 SP4 as a part of the process then go to 2012.This way you avoid any 'potential' licensing issues.
In between this is a windows server upgrade
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 15, 2014 at 2:32 pm
nvalia (7/15/2014)
Currently we are on Windows Server 2003 and plan to have SQL Server 2012 on Windows Server 2012My concern with the direct route is, upgrading a Service Pack in Production (SP2 to SP4)
If we go indirect route, then do we need to get license for SQL Server 2008 R2 first to move from
2005 SP2 to 2008 R2 and then to 2012?
Do you absolutely have to use the same hardware\server, is it even Windows 2012 compliant, should be your first questions.
If you do need to use the same server then server backups at all stages are a must. If you absolutely have to use the same server then you have the following options
Option 1
take full server backup
Option 2
Both have risks, if it were me i would get a weekend outage then take a server image and then virtualise the current server.
I would then and go along option 2 route
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 15, 2014 at 2:50 pm
Not sure if I mentioned this clearly
Current SQL Server 2005 SP2 is on Windows 2003 Server (Not to be used post Migrating Databases)
SQL Server 2012 is going to be on a Brand New Server with Windows 2012
July 15, 2014 at 3:31 pm
nvalia (7/15/2014)
Not sure if I mentioned this clearlyCurrent SQL Server 2005 SP2 is on Windows 2003 Server (Not to be used post Migrating Databases)
SQL Server 2012 is going to be on a Brand New Server with Windows 2012
No, you didn't mention it and that makes it a whole lot easier. Just migrate all objects across between the old and new instances
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2014 at 8:32 am
Thanks.
But I cannot do a direct migration from SQL Server 2005 Standard SP2 to SQL Server 2012 Standard right?
I have to through 2005 to 2008 and then to 2012...
What is the risk if we directly go from 2005 SP2 to 2012 even though it is not supported as per this link
http://msdn.microsoft.com/en-us/library/ms143393(v=sql.110).aspx
Will it fail straight away or the Migration will be a success but there could be unknown issues like missing objects, data loss or something else...?
NV
July 17, 2014 at 11:09 am
You can attach\restore a sql server 2005 database to a sql server 2012 server without any problems
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2014 at 11:39 am
The "rules and regulations" are specific to performing an in-place upgrade, which you are not doing. With a new server, you have a lot of options.
For starters, you can bring up the new server, configure all of the maintenance, logins, etc. etc.
You can then perform a "dry-run" by restoring the production database, and testing, testing, testing.
And then, you can test again.
When testing is complete, and you are ready to move to the new server, you can then run the backups, detach the databases, copy the .mdf and .ldf files to the new server, and re-attach them.
The backups are an insurance policy, and the old server is still in place should something go haywire.
After the databases are moved, make sure you set the compatibility level to 110, run DBCC CHECKDB, and update statistics.
Some random things to think about:
Did you run the SQL 2012 Upgrade advisor?
How many logins, and are they SQL or windows logins? Re-creating them may be easier than moving them, and it will give you the opportunity to clean up orphans as well as reviewing security on the new server.
Did you review Jonathan Kehayias's article?
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/03/22/sql-server-installation-checklist.aspx
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply