SQL server version problems 1

[btacy]

hi,
a little background to give you a better picture of what is happening.

i inherited a couple of databases across on two machines running different versions of SQL server.
Machine A runs SQL server 2000
Machine B runs SQL server 2005

one of our vendors needs to fix a problem with an application and thinks it has to do with the machine it sits on (in this case A) and so requested that the whole thing be relocated to/replicated on Machine B to confirm or rebuff their suspicions.

However i have found it impossible to import into Machine B any export taken from Machine A (understandably because they are different versions) and i have had suggestions on how to work around it.(i haven't suceeded at it yet)

i discovered that there is an SQL 2005 installation on Machine A and out of curiousity, called up it's management studio and connected to it. much to my surprise i could see the same databases present on the 2000 intact.

so, here are my questions:
is it safe to have two installations (and different versions at that)on the same machine? any idea how it works such that they can both use the same databases? are there any potential risks? I'm toying with the idea of exporting from the 2005 installation on Machine A for the purpose of importing to Machine B, what do you think?

I apologise that this post is quite lengthy.

 

[gmmastros]

I think you may be a bit confused, so I'll try to explain.

First, you need to understand the distinction between a database engine and client tools.

It sounds like you are dealing with 2 different database engines on 2 different computers. Additionally, you have the client tools installed on both computers.

So, the DB engines you have are SQL2000 and SQL2005.

The available client tools are: Enterprise Managers, Query Analyzer, and SQL Server Management Studio. Enterprise Manager and Query Analyzer are client tools distributed on the SQL 2000 disk. SQL Server Management Studio is distributed on the SQL 2005 disk and is meant to be a replacement for both Enterprise Manager and Query Analyzer.

Now, here's where it gets weird (and potentially confusing)...

You can use Query Analyzer to connect to a 2005 database engine. You can use SQL Server Management Studio to connect to a 2000 database engine. You cannot use Enterprise Manager to connect to a 2005 database engine though.

And more confusion.... You can have the database engine installed on one computer and the client tools on another.

And even more confusion.... You can have multiple instances of each database engine installed on the same computer.

Now, to directly answer your questions....

[blue]>> is it safe to have two installations (and different versions at that)on the same machine? [/blue]

Yes. On my desktop, I have one instance of SQL2000 and 2 instances of SQL2005.

[blue]>> any idea how it works such that they can both use the same databases? are there any potential risks?[/blue]

No. Each database that you have can only be connected to a single database engine instance.

[blue]>> I'm toying with the idea of exporting from the 2005 installation on Machine A for the purpose of importing to Machine B, what do you think?[/blue]

You can easily move a database from one computer to another, but you need to be careful. If you export a 2000 database and attach it to a 2005 instance, SQL Server will automatically upgrade the database to 2005. There is no way to 'downgrade' a 2005 database to 2000. The only way to get a 2005 database back to 2000 is to create a new 2000 database and then copy the tables, procedures, views, triggers, stored procedures, functions, etc.... to it. This is a messy process and should be avoided at all costs.

The best way to determine the database engines installed on a computer are....

Click Start->Run
Type Services.msc
click OK

Scroll down to MSSQLSERVER.

If you see a service named MSSQLSERVER then you have a SQL 2000 database instance installed on the computer. This is considered a 'default' instance.

If you see a service named MSSQLSERVER$[!]InstanceName[/!], then you have a SQL 2000 named instance (the name is whatever follows the $ sign).

If you have a service named 'SQL Server' then you have the SQL 2005 database engine installed. This would be considered a default instance.

If you have a service named 'SQL Server ([!]InstanceName[/!])' then you have a sql 2005 named instance.

Even though a database shows up in SQL Server Management Studio, it does not necessarily mean that it's a 2005 database. The best way to determine the database version is to open a query window and run....

[tt][blue]Select @@Version[/blue][/tt]

You will see a result like this...

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

or this...

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Finally...

[blue]>> one of our vendors needs to fix a problem with an application and thinks it has to do with the machine it sits on (in this case A) and so requested that the whole thing be relocated to/replicated on Machine B to confirm or rebuff their suspicions.[/blue]

My suggestion would be to install a named instance of sql server on the other computer. Install whichever version your vendor recommends. Before doing this, find out if your vendor supports named instances. If they do, then install a named instance on the other computer. Then, detach the database from the first computer, copy it to the other computer, and then attach it.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[btacy]

Thank you so much George,
you have greatly improved my knowledge of MS SQL Server.

i have checked for the database engines installed on each machine.
Machine A:
MSSQLSERVER
SQLSERVERAGENT


Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


Machine B:
SQL SERVER(MSSQLSERVER)
SQL SERVER AGENT(MSSQLSERVER)
+ others

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)


from what i now understand, this means, A has a 2000 database instance installed and B has a 2005 database instance installed.


>>My suggestion would be to install a named instance of sql server on the other computer. Install whichever version your vendor recommends. Before doing this, find out if your vendor supports named instances. If they do, then install a named instance on the other computer. Then, detach the database from the first computer, copy it to the other computer, and then attach it.

Make sense?

yeah, it does make sense. i'll set about that. thanks for you help i really appreciate it.

 

[juliane26]

Hi Btacy,

I moved quite some databases from a SQL 2000 Server to a SQL 2005 server simply by taking a backup and restoring it on the other machine.
Just adjust the path for data and log files according to your new system when restoring.
If needed (ask the vendor) adjust the compatibily level of the database to 90 (80 is 2000, 90 is 2005).

Worked always fine for me.

If you are worried about database objects using DDL which is not 2005 compatible: Microsoft offers a Update Advisor, which will check the database for you.
But for packaged software that is usually not your problem but the vendors.

Juliane