Mirroring on the same computer as principal.

Category: sql server mirroring

Question

Eitan Mizrahi on Sat, 24 Nov 2012 14:01:46


I have only one computer. That's my resources.

I want to create mirror+withness+principel databases on the same machine.

Everything is fine, but when I do :

ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://EITAN-PC:5024';

I get the message:

Msg 1452, Level 16, State 6, Line 1
The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.

As this means, I understatnd that I must have mirror db on other machine the principal db.

How can I do the above on the same computer?

Thanks :)

Replies

Sarabpreet Singh Anand on Mon, 26 Nov 2012 14:50:15


I have only one computer. That's my resources.

I want to create mirror+withness+principel databases on the same machine.

Everything is fine, but when I do :

ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://EITAN-PC:5024';

I get the message:

Msg 1452, Level 16, State 6, Line 1
The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.

As this means, I understatnd that I must have mirror db on other machine the principal db.

How can I do the above on the same computer?

Thanks :)

You cannot configure mirroring with only 1 SQL Instance 

TO Test mirroring on a single machine - install at-least two sql instances (in case you want automatic failover install 3 different instances

Just make sure while configuring you need to provide different port for all three instances.

Instance A = 5022

Instance B = 5023

Instance C = 5024

Other mirroring pre-requisites remain applicable.


Sarabpreet Singh Anand

Blog , Personal website

This posting is provided , "AS IS" with no warranties, and confers no rights.

Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


SuneelKumarReddy on Wed, 28 Nov 2012 06:14:57


Hi Mizrahi,

We cannot configure database mirroring in one  Insatnce, at least we have 2 instance then it is fine .But we dont have automatic failover only manual failover is enable to access the database .

each sql server instance may have only 1 mirroring endpoint, all mirroring transport occurs through this endpoint. Multiple instances on a single server would each have their own endpoint with a unique port number.

so that  make sure while configuring you need to provide different port for all three instances.

Instance 1 = 5022

Instance 2 = 5023

Instance 3 = 5024

 

LAKSHMI NARAYANA REDDY.L on Wed, 12 Dec 2012 04:03:05


Hi Mizrahi,

hope your are doing well.

we can't configure SQL Server Database Mirroring in single sql server Instance, Becz database mirroring Runs thorough endpoints, so not possible to configure, for practice if you want automatic fail over create 2 more sql server instance , other wise if you want manually& forced fail over install one more instance.

in case of logshipping we can configure logshipping in single sql server with different database name.

plz propose as answer if its reach your requirement.