| Author |
 Topic  |
|
|
Kristen
Test
United Kingdom
22191 Posts |
 Posted - 03/06/2007 : 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 |
Edited by - Kristen on 03/24/2010 05:47:49
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 03/06/2007 : 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 |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 03/07/2007 : 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
USA
35007 Posts |
Posted - 03/07/2007 : 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 |
Edited by - tkizer on 03/07/2007 02:12:20 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 03/07/2007 : 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
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 03/08/2007 : 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... |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 03/09/2007 : 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
United Kingdom
22191 Posts |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 05/26/2007 : 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 |
|
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 05/27/2007 : 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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 05/27/2007 : 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 |
|
|
| |
Topic |
|
Migrating to SQL 2005 Hints and Tips
