October 14, 2008 at 2:09 pm
We have a production SQL 2000 EE server and we would like to install an evaluation version of 2008 EE on another server and restore backups from our 2000 box to the new 2008 box. I know that this can be done, but what if I want to go back to SQL 2000? Can I take my production databases from the 2008 box and put them back on a 2000 box?
Thanks,
Andy
October 14, 2008 at 2:23 pm
Not directly, no. The database storage format is definitely different, and there is no way for SQL 2000 to know how to read a SQL 2008 formatted DB.
However, assuming that you are not using any non-2000 features, you might be able to script out the 2008 db then re-execute the script on SQL 2000, to get the definitions. Then use BCP to extract all of the data form the 2008 DB and the BCP them back in to SQL 2000.
Seems pretty hairy to me though.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 2:33 pm
The only way to downgrade a database (2008 to 2005, 2008 to 2000, 2005 to 2000) is to script the database and bcp the contents out.
And before anyone asks or suggests it, compatibility mode won't help. The compatibility mode just affects what words are reserved, what SQL statements are valid and other query-processor related options. It does not affect the database file format. A database attached to SQL 2008 is a SQL 2008 database in format, regardless of the compat mode.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2008 at 2:38 pm
How about using a distributed query to copy all the data after adding the 2000 server as a linked server? This is after using the Generate Scripts wizard in SQL 2008 to script the DB and all tables by implementing the 'Script for server version: Sql Server 2000' setting.
_________________________________
seth delconte
http://sqlkeys.com
October 14, 2008 at 2:55 pm
Seth Delconte (10/14/2008)
How about using a distributed query to copy all the data after adding the 2000 server as a linked server? This is after using the Generate Scripts wizard in SQL 2008 to script the DB and all tables by implementing the 'Script for server version: Sql Server 2000' setting.
Possible. But it is probably even harder and definitely slower than BCP.
The 'Script for server version: Sql Server 2000' is a good catch that I missed though. :w00t:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 6:31 pm
It sounds like there's no good way to evaluate SQL 2008 in a production environment with the hopes of going back to SQL 2000. Hmmm. Well, if we take the plunge to 2008 and we still see blocking in our databases, I'm going to have pie on my face. I guess it's upgrade or bust. Thanks guys.
October 14, 2008 at 9:10 pm
Perhaps you should tell us your situation with SQL Server 2000 that you hope will be fixed by SQL Server 2008?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 15, 2008 at 1:15 am
For what it's worth, I saw a massive reduction in blocking going from SQL 2000 to SQL 2005. It may not apply in your specific case.
You'll get other advantages moving up to 2008. In most cases, 2008 is faster than 2000 was. The optimiser is more intelligent, the statement level compiles reduce recompile problems. There are other advantages too.
Please test carefully. Things do break going from 2000 to 2008, even if the compat mode is left at 80.
If you can give a bit of detail about your blocking problem please, perhaps in a new thread? Maybe we can help you solve it before you upgrade, which will make you look like a genius. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 2:17 am
You can try with Import / Export wizard for table & data, and manually compile other scripts.
October 15, 2008 at 7:35 am
"It sounds like there's no good way to evaluate SQL 2008 in a production environment with the hopes of going back to SQL 2000."
That's not a great approach to testing an upgrade. You really need to set up a test environment and apply a load similar to your production load to see if it will solve your issue.
Although blocking may possibly be reduced by the upgraded database engine, blocking is usually a sign of an application or overall design issue, so it is pretty likely that if you are getting constant blocking problems, they will still be there after an upgrade. You may want to post more information about the blocking problem (probably in a new post) to try to get help on the actual issue.
October 15, 2008 at 8:19 am
I'm with Michael, you need to test.
Just going for it is a good way to lose a lot of goodwill at this job.
October 15, 2008 at 9:42 am
Steve Jones - Editor (10/15/2008)
I'm with Michael, you need to test.Just going for it is a good way to lose a lot of goodwill at this job.
Never mind your job, if the upgrade should not go well...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2008 at 9:46 am
Thanks guys and gals for all your responses so far.
Regarding the blocking/performance issues we are currently experiencing, well, 100% of our development is done by consultants in another state (who will remain nameless) and I believe they are Oracle guys on top of that. Me personally, I am a Network Admin database guy not really a database developer kinda guy. So, I don't have access to any source code, and I'm not really sure how I would go about tweaking someone else's queries. I'm 99.9% certain that it is not hardware or server configuration related. We are running SQL 2000 EE on a Dual quad-core Dell 2950 with 16GB of ram SAS drives 10K rpm in the following config
c:\ 136GB raid 1 with OS and SQL binaries
d:\ 400GB raid 5 with all the .mdfs (including tempdb)
e:\136GB raid 1 with the .ldfs
The only thing I can think of that may help performance is moving the tempdb to the c:\ but my company only has about 150 users and maybe 80 are logged in at the same time. We do use a product called rePortal that is basically a web server that serves up Crystal Reports via a web site and it does hit the production databases. We have considered creating a log shipping server for reports only but that is on hold now until I can determine if upgrading is more appropriate.
I know that we are experiencing blocking and it's most definately related to bad sql. Just not sure how to troubleshoot it. I do own a copy of Idera's Sql Manager. Anyone familiar with that?
Andy
October 15, 2008 at 9:59 am
Two questions first
Would you be allowed to make changes to the SQL code?
Would you be allowed to make changes to the indexing?
If not, would you be able to recommend/suggest to the nameless consultants that they fix their code?
Blocking is typically caused by bad queries or bad indexing (or both)
You can use the following query (in query analyser) to see blocking
select spid, hostname, program_name, loginame from sysprocesses where blocked > 0
union
select spid, hostname, program_name, loginame from sysprocesses where spid in (select blocked from sysprocesses where blocked > 0)
You can then use
DBCC INPUTBUFFER(< SPID > )
to see what those sessions are running
If you see certain queries involved in blocking frequently, then post them, the table structure and the indexes here and we can make suggestions.
Just moving to SQL 2008 isn't going to make bad code perform well. In fact, it may make bad code break completely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 11:08 am
First, if your issue is blocking you may wish to consider using with (nolock) where appropriate. It does raise the possibility of certain types of inconsistent reads so it should be used with care, but when used appropriately it can slice right through many blocking problems.
Second, you may find using SSIS more user friendly than recreating the structure and bcping it. You can also look at certain things such as Red Gates SQL Compare and SQL Data Compare used together, especially if you want to be selective about how much is copied over.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply