| 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=138230That 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+',', '') + MyColumnFROM MyTableORDER BY MyColumnPros 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.htmlUse 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=26788Have 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 |
 |
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 restoreit 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... |
|
|
|
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 |
|
|
|
Kristen
Test
22859 Posts |
|
|
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 Studiothis is a bit silly if you ask me.I've never gone over half gig._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
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 |
|
|
|
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 Studiothis is a bit silly if you ask me.I've never gone over half gig._______________________________________________Causing trouble since 1980blog: 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 |
|
|
|
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 CommanderWinAmp2x Visual Studio (sometimes 3)SSMSMaxthon (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 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
Migrating to SQL 2005 Hints and Tips