SQL DATA ROOT value question

I have a production SQL 2005 server that has no documentation. I need to build an exact replica of this server.

Please see the attached screen shot of the registry settings

You'll notice that there is no MSSQL.1 in the instance names, only MSSQL.2. You should also notice that the SQLDATAROOT key has a value of E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL while the SQLDATA has a value of D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL

I don't know how someone has managed to install SQL Database services and have the data path and data root different?  

My question is, how is this possible, how would I go about making a new installation look like this, since a new installation always wants to install a new default instance to MSSQL.1. Please note that I have tried to name the instance MSSQL.2 however this is not a valid name.
Who is Participating?
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
For SQL DR, you may want to consider MERGE REPLICATION feature in MS SQL and simply install independent SQL servers making the registry a non-issue because as you have found the numerical value is a flat order of installation; therefore, down the line if you install say SSAS and SSIS and another instance, etc. now you are trying to keep matching up numbers in the registry.

Geographic cluster may be another way to go. Log shipping.

May be a different solution / path you should choose for DR of SQL and then use Double Take for your other servers more akin to that sort of replication.
Kevin CrossChief Technology OfficerCommented:

Couple of things here:
+A good place to start for you would be to look under services and see how many SQL Server services are installed with different instance names. Instance name will be in () as in (MSSQLSERVER) or (APPNAME) or (SQLEXPRESS). Note that on full blown SQL, the default instance is (MSSQLSERVER) and the (SQLEXPRESS) is for Express!

I wouldn't use the registry to check for that personally.

Other method, is simply try to connect to it. How are you connecting now ?
i.e., ServerName\InstanceName
If so, then you can try ServerName
If not, then this is your default instance and you will need to install a NAMED instance on the second install.

It is very common practice for the base code of SQL to be on a different drive than your actual MDF, LDF and NDF files therefore, this is not too strange.

If you are installing to a whole new physical box, you need not worry about the numerical value assigned in terms of beign a replica of this server -- what is important is the configuration of SQL and the databases and data matching exactly.

Hope that makes sense.


P.S. Guess the next question is what are you trying to do exactly?
QuadrisITAuthor Commented:
Hi Kevin

May thanks for you reply.

The services are


Basically I am using Double Take to replicate this server to another SQL server in a DR centre. The Double Take app doesn't like the registry settings to differ at all, everything has to be the same.

I've figured out that if I install the reporting services first, then install the database services the database services are given the MSSQL.2 instance. I can then simply remove the reporting services. Unfortunately this doesn't fix the problem, since the SQLDATAROOT still points to MSSQL.1 on the original server (source) I have tried moving the files and amending the registry entry on the Target server (New SQL server) however the SQL services won't start once I do that.

Hope that makes sence, it does sound a bit waffley

Thanks again

Kevin CrossChief Technology OfficerCommented:
With respect, QuadrisIT, you have not properly responded to my last comment in this thread but are now closing this arbitrarily.  This is not the typical way to do things here, so please provide some feedback on why this was not helpful if you could.
Kevin CrossChief Technology OfficerCommented:
http:#a33429041 is the correct method as you cannot depend on aligning numbers of instances in both environments without doing things like installing services you don't need just to fill gaps.

Respectfully yours,

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.