SQL DATA ROOT value question

Posted on 2010-08-13
Last Modified: 2012-05-10
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.
Question by:QuadrisIT
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
LVL 59

Expert Comment

by:Kevin Cross
ID: 33428687

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?

Author Comment

ID: 33428909
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

LVL 59

Accepted Solution

Kevin Cross earned 500 total points
ID: 33429041
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.
LVL 59

Expert Comment

by:Kevin Cross
ID: 33586180
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.
LVL 59

Expert Comment

by:Kevin Cross
ID: 33590345
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,


Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question