Prev: Search and Replace within string
Next: Help with upgrade from SQL 2000 sp3 to 2005
From: siddhath on 25 Jun 2010 16:42
All,
I have two databases A and B on one pyhsical box called 'P'. I need to put
another instance of database A on same box. i can not have same database name
twice on same physical box twice so my solution was to create another
instance of sql server on same box and create database A on it. so now it
looks like this.

Physical box P has two DBs
-A and B

New instance of sql server on same physical box P has
- A (copy of DB A )

Both DB's "A on physical box P and A on the new sql server instance" need
to share/join to single copy of B runing on physical box P. Is there a way to
do that without using linked servers?
Thanks
Siddharth
From: Erland Sommarskog on 25 Jun 2010 18:37
siddhath (siddhath(a)discussions.microsoft.com) writes:
> I have two databases A and B on one pyhsical box called 'P'. I need to
> put another instance of database A on same box. i can not have same
> database name twice on same physical box twice so my solution was to
> create another instance of sql server on same box and create database A
> on it. so now it looks like this.
>
> Physical box P has two DBs
> -A and B
>
> New instance of sql server on same physical box P has
> - A (copy of DB A )
>
> Both DB's "A on physical box P and A on the new sql server instance"
> need to share/join to single copy of B runing on physical box P. Is
> there a way to do that without using linked servers?

Actually, two server instances can attach the same database file,
provided that the database is readonly on all servers. I don't remember
all details, but I think this was added in SQL 2008, and it may be
Enterprise Edition only.

I don't know why you think you cannot have to instances of A on the
same machine; if the database is hard-coded somehere, I would try to
fix that. But else, I think duplicating B is the best choice. If you
use linked servers, you will need to modify the queries. If B is
updated, replication can be a way to go.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Search and Replace within string
Next: Help with upgrade from SQL 2000 sp3 to 2005