Solved

SQL DATA ROOT value question

Posted on 2010-08-13
7
872 Views
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.
screenshot.jpg
0
Comment
Question by:QuadrisIT
  • 4
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
QuadrisIT,

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.

Kevin

P.S. Guess the next question is what are you trying to do exactly?
0
 

Author Comment

by:QuadrisIT
Comment Utility
Hi Kevin

May thanks for you reply.

The services are

SQL SERVER (MSSQLSERVER)
SQL SERVER AGEMNT (MSSQLSERVER)
SQL SERVER ANALYSIS (MSSQLSERVER)
SQL SERVER FULL TEXT SEARCH (MSSQLSERVER)

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

Pete
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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,

Kevin
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Read about achieving the basic levels of HRIS security in the workplace.
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now