[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


SQL DATA ROOT value question

Posted on 2010-08-13
Medium Priority
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 60

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 60

Accepted Solution

Kevin Cross earned 2000 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 60

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 60

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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