Solved

SQL DATA ROOT value question

Posted on 2010-08-13
7
878 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
ID: 33428687
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
ID: 33428909
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
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.
0
 
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.
0
 
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,

Kevin
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Changing multiple SQL login passwords 9 32
SQL Server CASE .. WHEN .. IN statement - Syntax issue 4 51
SQL Query 34 82
Generate Weekly Schedule 15 19
Creating and Managing Databases with phpMyAdmin in cPanel.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

863 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

22 Experts available now in Live!

Get 1:1 Help Now