Installing Sql 2000 along with SQL 2005 1

[19972003]

Anyone has good recommendation of how to install the SQL 2000 server? I have two sql servers, one is sql2k and the other is 2005; My ultimate goal is to have redundancy of the servers; sql 2005 is in test environment at the moment, can I install instance of the SQl 2000 and just let it sit there? If my sql 2000 server failed I can just quickly get the backup copy of the database and attach to SQL 2005 server where the standby sql 2000 instance sits? Or will it better to have the database to store on SAN and just have the instance of the SQL 2000 points the DB to SAN? What would be the most ideal solution in terms of the disaster recovery?

Many thanks,

 

[mrdenny]

Yes you can install a SQL 2000 instance on the SQL 2005 machine and use it in case the production machine fails.

I'd recommend that you put the databases on the SAN. This way when the machine fails you can simply attach the LUNs to the backup machine.

This shouldn't be called a DR setup as the test machine and produciton machine are probably in the same facility. DR implies that the solution can be used in the event of a facility failure. HA (High Availability) is used to being the server back online after a systems failure.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

Thanks so much for the infor; so here is the approach that I will need:

1. Install the instance of SQL 2000 on the SQL 2005 server, do I have to put the instance to different dir?

2. Is it very straing forward to move the the current database to the SAN or there are a lot of steps that I need to be aware of?

3. Does it mean that I do't have to care about the Server spec at all? say the current sql is Dell pe 1850 and I can install the sql 2k on PE 2850 because the db sits on the SAN.

4. Will I need to get a copy of the config file from my current sql to the one that I am building?

5.Will I create any conflicts when there are sql 2000 and sql 2005 there?

Any good article that can help me to walk through the process.

Many thanks,

 

[mrdenny]

1. You can give it the same path or a different path. SQL installs to a sub folder of the folder given.

2. You simply detach the databases and move them to the SAN, then attach them back to the SQL Server. There will be an outage while you move them.

3. You need to make sure that the backup server has enough CPU power and RAM to run the applications which it will need to run.

4. There is no config file for SQL Server. You will need to get all the settings and match them as best you can on the backup machine. You will also need to make sure that all the logins exist on the backup machine.

5. No, they can run side by side.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

Are there anything else that I need to aware of ? Is this the recommend way of HA?

Thank You

 

[mrdenny]

Is this the recommend way of HA?

Not even close. The recomended HA solution for Microsoft SQL Server is to cluster the servers with Microsoft Cluster Service and shared storage.

Are there anything else that I need to aware of ?

Not that I can think of, off the top of my head. You'll need to address any issues as you come across them.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

Does this solutions sound like a temporary patch? If I really need to have the HA, then I will need to go through the msft sql whitepaper? What would the ideal way of doing it?

 

[mrdenny]

This solution very much sounds like a temporary patch.

If 99.9% uptime (or greator) is required then you need to look into clustering.

Clusterig is very expensive to setup.

1. You need some sort of shared storage such as a SAN. Some server vendors have low end SCSI solutions which can work as well.

2. For SQL 2000 you need SQL 2000 Enterprise Edition. If you require CPU licensing because a web site or IVR system (or any other customer facing system) talks to the SQL Server this will get pricy.

3. Like servers are recommended as you have no guarantee that the services will be running on the node that you want. You can force the issue if it's running on the passive node, but if the services fail over to the other machine in the middle of the night, it's probably going to be a manual fail back the next night.

All that said, clusters are very reliable, and stable. They are THE solution for HA. There are some companies out there which sell third party solutions, but I (and many of the other experts on the site) prefer clustering.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

Thanks so much for your input, Mrdenny; In ideal situation, how many databases do you need to have it to have clustering setup? It seems like the clustering require win 2003 server enterprise or data center edition but my version is only win 2003 SD at the moment. Does it make sense if you have about 12 to 14 database to create a clustering?


Thanks again,

 

[mrdenny]

Clustering required Windows 2003 Enterprise Edition.

The number of databases aren't what's important when deciding to cluster the SQL Server. It's about how much money will be lost if the SQL Server fails. If the SQL Server will be down for 3 hours while it's rebuilt and set back up how much money will the company loose? If it's $20 then it's not worth it. If it's $100,000 then it's worth it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

if I have the cluster setup, do you recommend to install the sql 2000 on the one of the cluster servers for backup or this will break the sql 2005 clustering? I know that I can't install cluster sql 2000 because sql 2000 doesn't support it.

Thanks again

 

[mrdenny]

SQL 2000 does support clustering. You have to use SQL 2000 Enterprise Edition to do it.

If the point of clustering is to get the SQL 2000 server into a HA setup, why would you then cluster the SQL 2005 machine? I thought that the SQL 2005 machine was a dev machine?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

I would like to setup the sql 2005 clustering as well as setting up the sql 2000 backup solution(so that if the sql 2000 server crashes then I can just attach the databases to one of the Sql 2005 clustering servers and users will only have limited downtime.
My sql 2005 is in dev now and won't even meet the clustering requirement, so I like to spec out the Clustering and also be able to have backup plan for sql 2000 in case the server dies.

so it won't make sense to put the sql 2000 along with the clustering sl1 2005 becase sql 2005 is in clustering mode. Can I do that?

Thanks again.

 

[mrdenny]

Ok, I think I'm getting confused. You can attach a SQL 2000 database to a SQL 2005 server, but you will not be able to go backwards.

If your system is important enough to your business that you need to have an HA setup for it, don't scrimp on the setup. Do it correctly. Doing a patched together setup is just going to cause you problems in the future, and those problems aren't going to become apparent until you have to fail over to the backup box.

Spend the money, cluster the SQL 2000 Server correctly.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

Thanks so much for the information Denny, If I need to cluster the sql 2000, then I will probably need to re-spec the sytem out; my sql 2000 is EE and runs on wind 2003 R SD, this will not meet the clustering requirement at all. Is there a white paper out there can can help me to get clustering 2000 going?

say if I have two PE1850 server, does it make sense to point the database on the SAN if it is in clustering mode or it is better. I appreciated you help.

Thank You again,

 

[mrdenny]

If you are going to cluster the SQL Server the databases must be on the SAN.

There are several documents in the MSKB about setting up a cluster.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

It seems like Win2003 EE and SQL 2005 SE can only see 4 processors in clustering, if I have 8 processors on each node; does it mean the rest of processors useless or it will use by the system resources? because I am not utilize the full processor?

 

[mrdenny]

SQL 2005 SE can only use 4 physical processors weither it's clustered or not. Any unused processors will still be available to the OS. Do you have an 8 CPU machine or an 8 Core machine?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

two quard core machine.

 

[mrdenny]

SQL "should" be able to use all eight cores.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com