Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Migrating to SQL 2005 Hints and Tips

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2007-03-06 : 18:09:13
Edit: There is a newer thread about migrating to SQL 2008, which has more up to date information than this thread, much of which is relevant to migrating to SQL 2005 too.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230
That post is more thoroughly researched than this one, so worth considering carefully - e.g. the issues related to using CHECKSUM and READ_COMMITTED_SNAPSHOT which are not discussed in this thread.

Well I don't have any, yet!, but I would sure like to hear of what you wish you had known when you upgrade to SQL 2005.

I'll take a guess at a few:

Set the compatibility level to SQL2005 (not SQL2000)

Search your code for references to [sysobjects], [syscolumns] etc. and rework them.

This may not work as expected:

SELECT @foo = COALESCE(@foo+',', '') + MyColumn
FROM MyTable
ORDER BY MyColumn

Pros and Cons of Upgrade-over-2000 v. Fresh-install-of-SQL2005 and restore-DBs-from-SQL2000 ?

Any URLs you would recommend that offer advice on migration?

http://searchsystemschannel.techtarget.com/tip/0,289483,sid99_gci1229039,00.html

Use SQL 2005 "standard" to leverage the additional memory utilisation available from the O/S - should I upgrade the O/S at the same time to allow more memory? (Much cheaper than SQL Enterprise license!)

Configuration options that you only discovered with the benefit of Experience - and hindsight!

Thanks,

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-06 : 18:15:20
http://www.sqlteam.com/item.asp?ItemID=26788

Have you run the Upgrade Advisor tool?

I've only done fresh installs.

We are running SQL Server 2005 Enterprise Edition on Windows 2003. I've got 11 instances deployed to production in a 4 node cluster. The instances are being used by many applications.


Tara Kizer
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-03-07 : 01:58:04
Thanks Tara, very helpful.

How did you decide how many instances to have? - or maybe I should be asking: "How did you decide which application gets which instance"?

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-07 : 02:11:17
Each "application" got 2 instances, one for production and another one for beta/staging. Our applications are more like programs in that they have multiple applications that comprise a single product, plus most of them have multiple databases. We decided to have each product have its own production instance for "load-balancing". We can tune down the memory used by the less active instances plus move the instances around on the cluster.

Here's what our environment looks like (generalized):

SQL00 (default instance used for tasks that aren't product specific such as the distributor for replication)
SQL01 (product1 prod)
SQL02 (product1 beta\stage)
SQL03 (product2 prod)
SQL04 (product2 beta\stage)
SQL05 (product3 prod)
SQL06 (product3 beta\stage)
SQL07 (product4 prod)
SQL08 (product4 beta\stage)
SQL09 (product5 prod (beta\stage instance not required))
SQL10 (future use, not currently in use)

Tara Kizer
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-03-07 : 02:36:22
We've only got one application (eCommerce), but a number of client databases. Some have dedicated servers, so that's easy!

I wonder if we should have one instance for the minnows - that take one or two orders a day - and then maybe one instance per database that take 50-100 orders a day (above that they get a dedicated server)?

I'm sorta wondering whether I want to worry about that though ... its kinda make-work, and these folk are paying minimum-$ for shared hosting ... and if they pay for a dedicated server then there is only the one database on the server ...

Separate instances for Staging is a good idea though. Less likelihood of connecting to a Production database by accident, plus we can tighten "Who" has access to production instances.

Kristen
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-08 : 16:42:49
usually, decision on which instance/db does an application goes into is based on hardware resource, location and customer prioritization (dollar value, i guess)

we did some migrations aka moved DBs (just to put everything on 2005, then do the work later)...

from 2000, create the db on 2005 with compatibility 80, do a restore

it would have been nice if the migration work really took place, not just moving databases, this time becomes your chance to make improvements if any

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-03-09 : 04:31:18
"from 2000, create the db on 2005 with compatibility 80, do a restore"

I did the restore, which created the database. Setting the Compatibility to "SQL 2005" after that is OK presumably - it wasn't important that the database compatibility was set before the restore was it? (I can just envisage some "migration trick" that could be hidden behind that!!)

"it would have been nice if the migration work really took place"

That's kinda what we are on, but circumstances overtook our carefully made plans!

We have installed SQL2005 on a new server, have Restored our QA database onto it, and are in the process of running all our QA tests.

(I also ran the Upgrade Advisor that Tara suggested; that has showed up 3 Sprocs that reference system tables, and one that references a @@Variable that has increased from INT to BIGINT - not too bad - so far!)

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-05-26 : 07:50:55
Summary of the list of migration actions I've accumulated:

Microsoft's "What's New" List
http://msdn2.microsoft.com/en-us/library/ms170363(SQL.90).aspx

Make sure you have 1.5 to 2 GB of memory on your desktop system for running SQL Server 2005 Management Studio

Use Database Upgrade Advisor to check for incompatibilities
http://www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en

SQL Server 2005 upgrade handbook:
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx

DBCC CHECKDB all databases (inc. system) before you start migration testing, and again before any final cut-over

If 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=en

When installing SQL 2005 make sure you set the appropriate folder for installation. SQL Install generates a folder below this, and then a number of sub sub folders - thus choosing to install to the ROOT of a drive is probably sufficient

After migrating of a database:

Change the compatibility level to 90
Reindex ALL the tables
Update Statistics on ALL tables with full scan.

Make a full set of Regression and Performance tests before going Live

Gotchas:

You cannot upgrade from SQL 2000 Enterprise Edition to SQL 2005 Standard Edition

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-26 : 09:27:41
> Make sure you have 1.5 to 2 GB of memory on your desktop system for running SQL Server 2005 Management Studio

this is a bit silly if you ask me.

I've never gone over half gig.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

pootle_flump

1064 Posts

Posted - 2007-05-27 : 04:34:10
Run DBCC UPDATEUSAGE.
This is a useful checklist:
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-27 : 15:30:38
quote:
Originally posted by spirit1

> Make sure you have 1.5 to 2 GB of memory on your desktop system for running SQL Server 2005 Management Studio

this is a bit silly if you ask me.

I've never gone over half gig.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp


I have been having a lot of trouble with low memory on my desktop with half a GB of memory.

I am usually running Outlook, Enterprise Manager, Query Analyzer, and Management Studio, and well as having SQL 2005 Developer Edition running. Probably a few other things like Word and Excel on occasion. The system slows way down when it gets short of memory, and I also get low memory warnings in the application event log.

I can use it, but when it takes a couple of minutes to open Management Studio, I find it very irritating, especially if when think I didn't start it and start it again.

If you’re also running Visual Studio you're really going to need it.



CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-27 : 15:39:45
well my system has 1 gig RAM and my usuall work day involves these apps:
Total Commander
WinAmp
2x Visual Studio (sometimes 3)
SSMS
Maxthon (cool browser that i prefer over FF or IE)

with all this my mem consumption is usually 800 Mb... so the system is stilll pretty fast.
i am thinking about a new disk though.

so what i was saying that there's no way only SSMS would take half gig of RAM which is
how i understood the post.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
   

- Advertisement -