| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2007-05-24 : 04:14:33
|
| ...and landing firmly in the twentieth century. After a fairly critical error yesterday it seems that our server is not quite going to make its 7th birthday. Some small consolation after 2 or 3 years of pointing out that it is old and creaky (most video cards these days have more RAM, and in fact probably more processing power). The mahogonay case is going to get it's last polish before it goes to server heaven.So my question is this. it looks as though we will be upgrading from SQL 7 to SQL 2005. I know there is a lot of experience of this out there now so I was wondering if anyone had any tips, advice or gotchas?Apart from a mild interest I haven't really looked at 2k5 too much, though I know there are some significant changes. In our case the application is very simple - mostly tables and sprocs and that's about it, very few views, no udf's (obviously as this is SQL server 7). Access is windows Authentication and a handful of SQL authentication. I suspect at the moment my main concern will be moving the users and the DTS packages over (some of the packages are critical)I'm obviously going to have to play with it to work out the practicalities but what should I look at for actually moving the databases over?As I said, any information, tips or pointers would be gratefully receivedthankssteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-24 : 04:47:21
|
| well i don't have any experience with SS 7.0but i know that a simple backup on the 2k and restore to the 2k5 worked great.so i don't think you should have a problem there...DTS packages can be run in legacy mode, but if you want to full move them to 2k5 you'll have to rewrite them in SSIS.there are some "upgrade" tools, but i have no idea if they're any good._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-24 : 10:02:47
|
| You can restore sql7's backup on sql2k5. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-24 : 11:05:53
|
Worth running the Upgrade Advisor to see what it complains about.After "upgrade" (presumably a Restore of the DB):Change the Compatibility mode to SQL 2005 (i.e. "90")Reindex everythingRebuild all stats with FULL SCAN.Obviously you will need to do this on a trial-run, and then cut-over for real.So you can use the New Machine for regression testing, right? That will be a big help in getting more comfortable that SQL2005 will out-perform SQL7 in all aspects!You might want a short course for you about the things that have changed ...I presume you can cope with the down-time of the cut-over, but if not you can do a Full Backup, and Restore to New machine in Standby mode, then stop SQL Service Service on the old machine, disable all jobs, probably best to put the DB into Read Only / DBO only mode too, take a final DIFF backup 9which will be small), and restore the Diff onto the new machine with Recovery, and then the database will be "oven ready" on the new machine. (You'll need to reset ReadOnly / DBO-only modes on the new machine when you are ready, and you still have to repeat the Compatibility and Reindex/Stats stuff)You will probably want to turn on Torn page detection which is OFF by default on SQL 7 I think (or better still use Checksum).When did all the Collation stuff get introduced? Was that 6'5->7 or 7->2000 ? You certainly want to think very carefully about what collation you install the new machine as - and it most probably wants to match the legacy-collation that represents SQL7.All in all you need some consultancy mate! There's beers in it for you, and no one will know that you are taking bribes  Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-24 : 18:15:28
|
| I don't think you can restore a SQL 7 database backup to even SQL 2000 in STANDBY mode, so I doubt you can do it in 2005. I believe that you can restore with NORECOVERY.Make sure you pick the correct drive location for the SQL Server databases during the install. It doesn't really point out to you the location you are selecting like 7.0 or 2000. If you don't, you will most likely end up with the system databases on your C drive.Make sure that you install the latest version of SQL 2005 SP2 to get you to version 9.00.3050.00If you are going to maintain DTS packages in SQL 2005, you will need to download SQL Server 2000 DTS Designer Components from the SQL Server 2005 Feature pack, and install it on the developer client systems.http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=enI would go through installing the Developer Edition on your PC first just to go through the installation process, and document the process along the way before doing the production system.Make sure you have 1.5 to 2 GB of memory on your desktop system for running SQL Server 2005 Management Studio. It loves to use up your memory.I would make sure I had the SQL Server 2000 Client Tools installed before installing SQL 2005 Client Tools on your client PC. Until you get used to SQL Server 2005 Management Studio, it is very handy to have Query Analyzer available. You can't use SQL 2000 Enterprise Manager with SQL 2005.CODO ERGO SUM |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-05-25 : 03:04:29
|
Many thanks to all for the advice it is greatly appreciated. Kristen, some down time will be acceptable (given that the server was down for a few hours the other day it won't be any worse). It would have been down for less but I was told it was more important that I go trogging off to setup a projector for our illustrious leader  which delayed everything by a good hour quote:
There's beers in it for you, and no one will know that you are taking bribes
We can charge that to the hospitality budget line  steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 03:10:13
|
| "STANDBY ... NORECOVERY"Sorry, I meant NORECOVERY. Thanks for spotting MVJ."It doesn't really point out to you the location you are selecting like 7.0 or 2000"You aren't kidding, its buried so deep in the Install menus no Ordinary Joe is ever going to stumble over it. Daft that ... yet more databases installed in x:\Program Files\... I suppose ...Note also that the Install creates a sub folder, and then a raft of sub sub folders - so you probably just want to point it at the ROOT on the appropriate drive(s)"to setup a projector for our illustrious leader"Glad to hear that the art of Homage is not dead in our public services. I can remind the troops here that "its just the same in the public sector" Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-05-25 : 03:17:47
|
If anyone else is interested in this topic I have found this [url]http://support.microsoft.com/kb/906773[/url] which may help quote:
I can remind the troops here that "its just the same in the public sector"
Hmm subtle difference Kristen - as I like to remind everyone who I think is trying to charge us too much - it's your money that pays for it Damn - mine too - I want a refund!steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 03:33:31
|
| Its a bit "light" ...Useful points from that:"SQL Profiler replay" ... is a useful testing methodRun the DBCC Checkdb command on all the user databases and the system database to make sure that the databases are clean. Some of the others I would gloss over:"Make sure that the computer on which you are installing SQL Server 2005 does not have any hardware errors."Er, yeah ... OK ...Kristen |
|
|
|
|
Leaping Tall buildings