Mirroring dedicated traffic

We are using the following configuration:

2 stand alone Windows 2003 servers 64 bit.

Each have two Gig Network cards configured with different IP addresses.

Each running multiple instances of SQL Server 64.

I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.

I am also trying to avoid Windows authentication.

Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...

Would someone please point me in the right direction?

Thank you.

Use certificate authentication. Look up "database mirroring [SQL Server], certificates" in Books Online.|||

Thank you very much for your response.

That is definetely a lot of information.

As about "database mirroring, certificates" - there is no such a topic in BOL. There is a "Setting Up database mirroring using certificates (Transact SQL)". That is a great topic! However, I thought that my question was specific? Sorry if not. I asked about dedicating NIC No.2 to a traffic generated by mirroring process. Unfortunately, topic mentioned by you does not have anything about TCP/IP; adding port numbers and/or netowrk cards to the mirror. (Actually, it is roughly covering even the main topic since witness installation totally skipped).

And if we will go here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx and go down to the networking section we will read a great article:

Q.

How do I specify a specific network adapter/network just for mirroring?

A.

Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.

I can' believe that nobody didn't get to the point where he will have several NICs on the server? Is it such an exclusive installation?

|||

Thank you for all your help.

Resolved.

|||Is another way to do this just to change the hosts file on the Servers to point to the other network address as opposed changing the name?|||

You can simply bind the mirroring endpoint to listen only on the dedicated NIC's IP. This will apply to outgoing traffic as well as incomming.

alter endpoint [endpointname] as TCP (listener_ip = (<4 part ip of dedicated NIC>));

|||

Mark,

simple 'on surface' type of decision is not always a correct one. What is the sense in certificate based security and ecryption if you are exposing your DB network structure ? Wouldn't it be a security risk?

|||

Sorry, Remus.

I marked your response as an answer. It is incorrect though. (I am novice here and do not know how to reverse it).

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic. Question was; how to separate the mirroring traffic through a separate NIC?

And solution is:

ALTER DATABASE [DB] SET PARTNER = 'TCP://<4part.IP.address>:<port_number>'

repeating this statement on both partners (principle and mirror) and then submitting

ALTER DATABASE SET [DB] WITNESS= 'TCP://<4part.IP.address>:<port_number>'

on principle or on mirror.

|||

I though you want to separate mirroring traffic from user connections traffic (TDS). Separting witness traffic from partner traffic is somehow unusual considering that connectivity with the witness is just as important as connectivity with the partner, otherwise why have a witness in the first place?

HTH,
~ Remus

|||

Glen Sidelnikov wrote:

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic.

There are many enpoints in an instance: user connection endpoints (shared memory, named pipes, TCP), mirroring endpoint, service broker endpoint, HTTP enpoints. The settings for mirroring endpoint and user endpoints are distinct, therefore you can set the user endpoints (TDS listenning port) on one NIC and the mirroring endpoint on another NIC.

Another way to achieve this is to listen on all NICs, but rely on underlying IP to choose the appropiate NIC. That is, set the partner/witness names to a name that is only reachable through the desired NIC.

HTH,
~ Remus

sql

Mirroring dedicated traffic

We are using the following configuration:

2 stand alone Windows 2003 servers 64 bit.

Each have two Gig Network cards configured with different IP addresses.

Each running multiple instances of SQL Server 64.

I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.

I am also trying to avoid Windows authentication.

Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...

Would someone please point me in the right direction?

Thank you.

Use certificate authentication. Look up "database mirroring [SQL Server], certificates" in Books Online.|||

Thank you very much for your response.

That is definetely a lot of information.

As about "database mirroring, certificates" - there is no such a topic in BOL. There is a "Setting Up database mirroring using certificates (Transact SQL)". That is a great topic! However, I thought that my question was specific? Sorry if not. I asked about dedicating NIC No.2 to a traffic generated by mirroring process. Unfortunately, topic mentioned by you does not have anything about TCP/IP; adding port numbers and/or netowrk cards to the mirror. (Actually, it is roughly covering even the main topic since witness installation totally skipped).

And if we will go here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx and go down to the networking section we will read a great article:

Q.

How do I specify a specific network adapter/network just for mirroring?

A.

Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.

I can' believe that nobody didn't get to the point where he will have several NICs on the server? Is it such an exclusive installation?

|||

Thank you for all your help.

Resolved.

|||Is another way to do this just to change the hosts file on the Servers to point to the other network address as opposed changing the name?|||

You can simply bind the mirroring endpoint to listen only on the dedicated NIC's IP. This will apply to outgoing traffic as well as incomming.

alter endpoint [endpointname] as TCP (listener_ip = (<4 part ip of dedicated NIC>));

|||

Mark,

simple 'on surface' type of decision is not always a correct one. What is the sense in certificate based security and ecryption if you are exposing your DB network structure ? Wouldn't it be a security risk?

|||

Sorry, Remus.

I marked your response as an answer. It is incorrect though. (I am novice here and do not know how to reverse it).

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic. Question was; how to separate the mirroring traffic through a separate NIC?

And solution is:

ALTER DATABASE [DB] SET PARTNER = 'TCP://<4part.IP.address>:<port_number>'

repeating this statement on both partners (principle and mirror) and then submitting

ALTER DATABASE SET [DB] WITNESS= 'TCP://<4part.IP.address>:<port_number>'

on principle or on mirror.

|||

I though you want to separate mirroring traffic from user connections traffic (TDS). Separting witness traffic from partner traffic is somehow unusual considering that connectivity with the witness is just as important as connectivity with the partner, otherwise why have a witness in the first place?

HTH,
~ Remus

|||

Glen Sidelnikov wrote:

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic.

There are many enpoints in an instance: user connection endpoints (shared memory, named pipes, TCP), mirroring endpoint, service broker endpoint, HTTP enpoints. The settings for mirroring endpoint and user endpoints are distinct, therefore you can set the user endpoints (TDS listenning port) on one NIC and the mirroring endpoint on another NIC.

Another way to achieve this is to listen on all NICs, but rely on underlying IP to choose the appropiate NIC. That is, set the partner/witness names to a name that is only reachable through the desired NIC.

HTH,
~ Remus

mirroring db: communication problem

Hi All
I am trying to test db mirroring. I went exactly as book says: started 2 sql
servers with the same domain accounts, created endpoints. Checked if
endpoints exist and listen - they do.
Than I try to alter test db:
ALTER DATABASE test
SET PARTNER = 'TCP://pc500:5000'
and get:
Msg 1418, Level 16, State 1, Line 2
The server network address "TCP://pc500:5000" can not be reached or does not
exist. Check the network address name and that the ports for the local and
remote endpoints are operational.
pc500 is a partner pc.
I also tried to use full domain name and ip addres. Each time I removed
endpoints and checked from os prompt if anything listens on the port - no.
But no matter what I do, I get this error. I also checked with my sysadmin
if there is any kind of filtering or firewalls - no.
As a matter of fact, pc500 is virtual pc which resides on my pc.
It does not work. I tried replication from 2005 to 2005 - the same problem.
But replication from 2k to 2005 - works painlessly.
Does anybody have clue what's the problem here?Hello,
I supposed you can ping that server, right? :-)
Is the endpoint started? When it is created the default is stopped. Check
state_desc on
select * from sys.database_mirroring_endpoints
Always use full domain name.
Check port is correct on sys.tcp_endpoints.
Hope this helps,
Ben Nevarez
"Gene." wrote:
> Hi All
> I am trying to test db mirroring. I went exactly as book says: started 2 sql
> servers with the same domain accounts, created endpoints. Checked if
> endpoints exist and listen - they do.
> Than I try to alter test db:
> ALTER DATABASE test
> SET PARTNER => 'TCP://pc500:5000'
> and get:
> Msg 1418, Level 16, State 1, Line 2
> The server network address "TCP://pc500:5000" can not be reached or does not
> exist. Check the network address name and that the ports for the local and
> remote endpoints are operational.
> pc500 is a partner pc.
> I also tried to use full domain name and ip addres. Each time I removed
> endpoints and checked from os prompt if anything listens on the port - no.
> But no matter what I do, I get this error. I also checked with my sysadmin
> if there is any kind of filtering or firewalls - no.
> As a matter of fact, pc500 is virtual pc which resides on my pc.
> It does not work. I tried replication from 2005 to 2005 - the same problem.
> But replication from 2k to 2005 - works painlessly.
> Does anybody have clue what's the problem here?
>|||Hi Ben
Here is an output from:
select name, type_desc, port, ip_address from sys.tcp_endpoints
select name, role_desc, state_desc from sys.database_mirroring_endpoints
name
type_desc
port ip_address
------
---- --
---
Dedicated Admin Connection
TSQL
0 NULL
TSQL Default TCP
TSQL
0 NULL
Endpoint_Mirroring
DATABASE_MIRRORING
5001 NULL
(3 row(s) affected)
name
role_desc
state_desc
------
----
----
Endpoint_Mirroring
ALL
STARTED
Yes, it's started.
In addition to it I found message which looks so innocent:
Login failed for user 'group\user'. [CLIENT: <local machine>]
I logged in with that user hundreds times for sure.
"Ben Nevarez" wrote:
> Hello,
> I supposed you can ping that server, right? :-)
> Is the endpoint started? When it is created the default is stopped. Check
> state_desc on
> select * from sys.database_mirroring_endpoints
> Always use full domain name.
> Check port is correct on sys.tcp_endpoints.
> Hope this helps,
> Ben Nevarez
>
>
> "Gene." wrote:
> > Hi All
> > I am trying to test db mirroring. I went exactly as book says: started 2 sql
> > servers with the same domain accounts, created endpoints. Checked if
> > endpoints exist and listen - they do.
> > Than I try to alter test db:
> >
> > ALTER DATABASE test
> > SET PARTNER => > 'TCP://pc500:5000'
> > and get:
> >
> > Msg 1418, Level 16, State 1, Line 2
> > The server network address "TCP://pc500:5000" can not be reached or does not
> > exist. Check the network address name and that the ports for the local and
> > remote endpoints are operational.
> >
> > pc500 is a partner pc.
> >
> > I also tried to use full domain name and ip addres. Each time I removed
> > endpoints and checked from os prompt if anything listens on the port - no.
> > But no matter what I do, I get this error. I also checked with my sysadmin
> > if there is any kind of filtering or firewalls - no.
> > As a matter of fact, pc500 is virtual pc which resides on my pc.
> >
> > It does not work. I tried replication from 2005 to 2005 - the same problem.
> > But replication from 2k to 2005 - works painlessly.
> >
> > Does anybody have clue what's the problem here?
> >|||Gene,
According to that output the port number is 5001 not 5000. So you should use
ALTER DATABASE test
SET PARTNER = 'TCP://pc500:5001'
Please add the full domain name too.
Please test this and see if it works.
Hope this helps,
Ben Nevarez
"Gene." wrote:
> Hi Ben
> Here is an output from:
> select name, type_desc, port, ip_address from sys.tcp_endpoints
> select name, role_desc, state_desc from sys.database_mirroring_endpoints
>
> name
> type_desc
> port ip_address
> ------
> ---- --
> ---
> Dedicated Admin Connection
> TSQL
> 0 NULL
> TSQL Default TCP
> TSQL
> 0 NULL
> Endpoint_Mirroring
> DATABASE_MIRRORING
> 5001 NULL
> (3 row(s) affected)
> name
> role_desc
> state_desc
> ------
> ----
> ----
> Endpoint_Mirroring
> ALL
> STARTED
>
> Yes, it's started.
> In addition to it I found message which looks so innocent:
> Login failed for user 'group\user'. [CLIENT: <local machine>]
> I logged in with that user hundreds times for sure.
>
> "Ben Nevarez" wrote:
> >
> > Hello,
> >
> > I supposed you can ping that server, right? :-)
> >
> > Is the endpoint started? When it is created the default is stopped. Check
> > state_desc on
> >
> > select * from sys.database_mirroring_endpoints
> >
> > Always use full domain name.
> >
> > Check port is correct on sys.tcp_endpoints.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Gene." wrote:
> >
> > > Hi All
> > > I am trying to test db mirroring. I went exactly as book says: started 2 sql
> > > servers with the same domain accounts, created endpoints. Checked if
> > > endpoints exist and listen - they do.
> > > Than I try to alter test db:
> > >
> > > ALTER DATABASE test
> > > SET PARTNER => > > 'TCP://pc500:5000'
> > > and get:
> > >
> > > Msg 1418, Level 16, State 1, Line 2
> > > The server network address "TCP://pc500:5000" can not be reached or does not
> > > exist. Check the network address name and that the ports for the local and
> > > remote endpoints are operational.
> > >
> > > pc500 is a partner pc.
> > >
> > > I also tried to use full domain name and ip addres. Each time I removed
> > > endpoints and checked from os prompt if anything listens on the port - no.
> > > But no matter what I do, I get this error. I also checked with my sysadmin
> > > if there is any kind of filtering or firewalls - no.
> > > As a matter of fact, pc500 is virtual pc which resides on my pc.
> > >
> > > It does not work. I tried replication from 2005 to 2005 - the same problem.
> > > But replication from 2k to 2005 - works painlessly.
> > >
> > > Does anybody have clue what's the problem here?
> > >|||Ben
That's correct. Principal uses 5000 port for it's own end point, but refers
to port 5001 of the partner. And vice verse.
I am using full domain names but for this post I used short ones. As I said
before, full domain names were tested with ping from both hosts. I also used
ip's in their place.
"Ben Nevarez" wrote:
> Gene,
> According to that output the port number is 5001 not 5000. So you should use
> ALTER DATABASE test
> SET PARTNER => 'TCP://pc500:5001'
> Please add the full domain name too.
> Please test this and see if it works.
> Hope this helps,
> Ben Nevarez
>
>
> "Gene." wrote:
> > Hi Ben
> >
> > Here is an output from:
> >
> > select name, type_desc, port, ip_address from sys.tcp_endpoints
> > select name, role_desc, state_desc from sys.database_mirroring_endpoints
> >
> >
> >
> > name
> > type_desc
> > port ip_address
> > ------
> > ---- --
> > ---
> > Dedicated Admin Connection
> > TSQL
> > 0 NULL
> > TSQL Default TCP
> > TSQL
> > 0 NULL
> > Endpoint_Mirroring
> > DATABASE_MIRRORING
> > 5001 NULL
> >
> > (3 row(s) affected)
> >
> > name
> > role_desc
> > state_desc
> > ------
> > ----
> > ----
> > Endpoint_Mirroring
> > ALL
> > STARTED
> >
> >
> >
> > Yes, it's started.
> >
> > In addition to it I found message which looks so innocent:
> >
> > Login failed for user 'group\user'. [CLIENT: <local machine>]
> >
> > I logged in with that user hundreds times for sure.
> >
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Hello,
> > >
> > > I supposed you can ping that server, right? :-)
> > >
> > > Is the endpoint started? When it is created the default is stopped. Check
> > > state_desc on
> > >
> > > select * from sys.database_mirroring_endpoints
> > >
> > > Always use full domain name.
> > >
> > > Check port is correct on sys.tcp_endpoints.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > >
> > >
> > >
> > >
> > > "Gene." wrote:
> > >
> > > > Hi All
> > > > I am trying to test db mirroring. I went exactly as book says: started 2 sql
> > > > servers with the same domain accounts, created endpoints. Checked if
> > > > endpoints exist and listen - they do.
> > > > Than I try to alter test db:
> > > >
> > > > ALTER DATABASE test
> > > > SET PARTNER => > > > 'TCP://pc500:5000'
> > > > and get:
> > > >
> > > > Msg 1418, Level 16, State 1, Line 2
> > > > The server network address "TCP://pc500:5000" can not be reached or does not
> > > > exist. Check the network address name and that the ports for the local and
> > > > remote endpoints are operational.
> > > >
> > > > pc500 is a partner pc.
> > > >
> > > > I also tried to use full domain name and ip addres. Each time I removed
> > > > endpoints and checked from os prompt if anything listens on the port - no.
> > > > But no matter what I do, I get this error. I also checked with my sysadmin
> > > > if there is any kind of filtering or firewalls - no.
> > > > As a matter of fact, pc500 is virtual pc which resides on my pc.
> > > >
> > > > It does not work. I tried replication from 2005 to 2005 - the same problem.
> > > > But replication from 2k to 2005 - works painlessly.
> > > >
> > > > Does anybody have clue what's the problem here?
> > > >|||Have you applied SP1, or better, SP2?
Is the login failed message from the user who is supposed to connect to the
endpoint? if so, assign connect permissions to the required logins like in
grant connect on endpoint::endpoint_mirroring to [user]
Hope this helps,
Ben Nevarez
"Gene." wrote:
> Ben
> That's correct. Principal uses 5000 port for it's own end point, but refers
> to port 5001 of the partner. And vice verse.
> I am using full domain names but for this post I used short ones. As I said
> before, full domain names were tested with ping from both hosts. I also used
> ip's in their place.
> "Ben Nevarez" wrote:
> >
> > Gene,
> >
> > According to that output the port number is 5001 not 5000. So you should use
> >
> > ALTER DATABASE test
> > SET PARTNER => > 'TCP://pc500:5001'
> >
> > Please add the full domain name too.
> >
> > Please test this and see if it works.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Gene." wrote:
> >
> > > Hi Ben
> > >
> > > Here is an output from:
> > >
> > > select name, type_desc, port, ip_address from sys.tcp_endpoints
> > > select name, role_desc, state_desc from sys.database_mirroring_endpoints
> > >
> > >
> > >
> > > name
> > > type_desc
> > > port ip_address
> > > ------
> > > ---- --
> > > ---
> > > Dedicated Admin Connection
> > > TSQL
> > > 0 NULL
> > > TSQL Default TCP
> > > TSQL
> > > 0 NULL
> > > Endpoint_Mirroring
> > > DATABASE_MIRRORING
> > > 5001 NULL
> > >
> > > (3 row(s) affected)
> > >
> > > name
> > > role_desc
> > > state_desc
> > > ------
> > > ----
> > > ----
> > > Endpoint_Mirroring
> > > ALL
> > > STARTED
> > >
> > >
> > >
> > > Yes, it's started.
> > >
> > > In addition to it I found message which looks so innocent:
> > >
> > > Login failed for user 'group\user'. [CLIENT: <local machine>]
> > >
> > > I logged in with that user hundreds times for sure.
> > >
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Hello,
> > > >
> > > > I supposed you can ping that server, right? :-)
> > > >
> > > > Is the endpoint started? When it is created the default is stopped. Check
> > > > state_desc on
> > > >
> > > > select * from sys.database_mirroring_endpoints
> > > >
> > > > Always use full domain name.
> > > >
> > > > Check port is correct on sys.tcp_endpoints.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > >
> > > >
> > > >
> > > >
> > > > "Gene." wrote:
> > > >
> > > > > Hi All
> > > > > I am trying to test db mirroring. I went exactly as book says: started 2 sql
> > > > > servers with the same domain accounts, created endpoints. Checked if
> > > > > endpoints exist and listen - they do.
> > > > > Than I try to alter test db:
> > > > >
> > > > > ALTER DATABASE test
> > > > > SET PARTNER => > > > > 'TCP://pc500:5000'
> > > > > and get:
> > > > >
> > > > > Msg 1418, Level 16, State 1, Line 2
> > > > > The server network address "TCP://pc500:5000" can not be reached or does not
> > > > > exist. Check the network address name and that the ports for the local and
> > > > > remote endpoints are operational.
> > > > >
> > > > > pc500 is a partner pc.
> > > > >
> > > > > I also tried to use full domain name and ip addres. Each time I removed
> > > > > endpoints and checked from os prompt if anything listens on the port - no.
> > > > > But no matter what I do, I get this error. I also checked with my sysadmin
> > > > > if there is any kind of filtering or firewalls - no.
> > > > > As a matter of fact, pc500 is virtual pc which resides on my pc.
> > > > >
> > > > > It does not work. I tried replication from 2005 to 2005 - the same problem.
> > > > > But replication from 2k to 2005 - works painlessly.
> > > > >
> > > > > Does anybody have clue what's the problem here?
> > > > >|||Hi Ben
It finaly started working.
The problem was quite simple.
I created end points and tried to alter db to communicate with partner.
Instead of it I had to dump and load partner with db and tran and only than
to finalize it's communication by altering db.
Thank you for your help anyway.
"Ben Nevarez" wrote:
> Have you applied SP1, or better, SP2?
> Is the login failed message from the user who is supposed to connect to the
> endpoint? if so, assign connect permissions to the required logins like in
> grant connect on endpoint::endpoint_mirroring to [user]
> Hope this helps,
> Ben Nevarez
>
>
> "Gene." wrote:
> > Ben
> >
> > That's correct. Principal uses 5000 port for it's own end point, but refers
> > to port 5001 of the partner. And vice verse.
> > I am using full domain names but for this post I used short ones. As I said
> > before, full domain names were tested with ping from both hosts. I also used
> > ip's in their place.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Gene,
> > >
> > > According to that output the port number is 5001 not 5000. So you should use
> > >
> > > ALTER DATABASE test
> > > SET PARTNER => > > 'TCP://pc500:5001'
> > >
> > > Please add the full domain name too.
> > >
> > > Please test this and see if it works.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > >
> > >
> > >
> > >
> > > "Gene." wrote:
> > >
> > > > Hi Ben
> > > >
> > > > Here is an output from:
> > > >
> > > > select name, type_desc, port, ip_address from sys.tcp_endpoints
> > > > select name, role_desc, state_desc from sys.database_mirroring_endpoints
> > > >
> > > >
> > > >
> > > > name
> > > > type_desc
> > > > port ip_address
> > > > ------
> > > > ---- --
> > > > ---
> > > > Dedicated Admin Connection
> > > > TSQL
> > > > 0 NULL
> > > > TSQL Default TCP
> > > > TSQL
> > > > 0 NULL
> > > > Endpoint_Mirroring
> > > > DATABASE_MIRRORING
> > > > 5001 NULL
> > > >
> > > > (3 row(s) affected)
> > > >
> > > > name
> > > > role_desc
> > > > state_desc
> > > > ------
> > > > ----
> > > > ----
> > > > Endpoint_Mirroring
> > > > ALL
> > > > STARTED
> > > >
> > > >
> > > >
> > > > Yes, it's started.
> > > >
> > > > In addition to it I found message which looks so innocent:
> > > >
> > > > Login failed for user 'group\user'. [CLIENT: <local machine>]
> > > >
> > > > I logged in with that user hundreds times for sure.
> > > >
> > > >
> > > > "Ben Nevarez" wrote:
> > > >
> > > > >
> > > > > Hello,
> > > > >
> > > > > I supposed you can ping that server, right? :-)
> > > > >
> > > > > Is the endpoint started? When it is created the default is stopped. Check
> > > > > state_desc on
> > > > >
> > > > > select * from sys.database_mirroring_endpoints
> > > > >
> > > > > Always use full domain name.
> > > > >
> > > > > Check port is correct on sys.tcp_endpoints.
> > > > >
> > > > > Hope this helps,
> > > > >
> > > > > Ben Nevarez
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Gene." wrote:
> > > > >
> > > > > > Hi All
> > > > > > I am trying to test db mirroring. I went exactly as book says: started 2 sql
> > > > > > servers with the same domain accounts, created endpoints. Checked if
> > > > > > endpoints exist and listen - they do.
> > > > > > Than I try to alter test db:
> > > > > >
> > > > > > ALTER DATABASE test
> > > > > > SET PARTNER => > > > > > 'TCP://pc500:5000'
> > > > > > and get:
> > > > > >
> > > > > > Msg 1418, Level 16, State 1, Line 2
> > > > > > The server network address "TCP://pc500:5000" can not be reached or does not
> > > > > > exist. Check the network address name and that the ports for the local and
> > > > > > remote endpoints are operational.
> > > > > >
> > > > > > pc500 is a partner pc.
> > > > > >
> > > > > > I also tried to use full domain name and ip addres. Each time I removed
> > > > > > endpoints and checked from os prompt if anything listens on the port - no.
> > > > > > But no matter what I do, I get this error. I also checked with my sysadmin
> > > > > > if there is any kind of filtering or firewalls - no.
> > > > > > As a matter of fact, pc500 is virtual pc which resides on my pc.
> > > > > >
> > > > > > It does not work. I tried replication from 2005 to 2005 - the same problem.
> > > > > > But replication from 2k to 2005 - works painlessly.
> > > > > >
> > > > > > Does anybody have clue what's the problem here?
> > > > > >

mirrored sql servers

Hello:
I have 2 MS SQL Servers 2000 Developer edition.
Both have same settings and databases. I'd like to have them as mirrored
servers - all transaction on one server must be performed on the other
automatically. More than that I'd like to get possible if something happened
to one server, all transactions will be performed on another one.
Please, give me a hint how to do that.

Thanks,
GBGB (v7v1k3@.hotmail.com) writes:
> I have 2 MS SQL Servers 2000 Developer edition.
> Both have same settings and databases. I'd like to have them as mirrored
> servers - all transaction on one server must be performed on the other
> automatically. More than that I'd like to get possible if something
> happened to one server, all transactions will be performed on another
> one.

Symmetric or one-way? If one server is a main server, and the other is a
standby server, you can use either log shipping or transactional
replication. Log shipping is easier to manage, as transactional
replication leaves its mark on the source database. On the other hand,
the standby server will not be accessible each time a log is applied,
and the lag time is longer.

If it is symmetric, that actions are to be replicated in both directions,
then you may want to look into merge replication. But this sounds anything
about trivial to me. What about if the same row was updated in both
databases? Who wins?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The only purpose I have is to have a "backup" server to be ready
to substitute main server at any time with recent data in it
and "seamless" switching in production mode.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970419D43A88Yazorman@.127.0.0.1...
> GB (v7v1k3@.hotmail.com) writes:
> > I have 2 MS SQL Servers 2000 Developer edition.
> > Both have same settings and databases. I'd like to have them as mirrored
> > servers - all transaction on one server must be performed on the other
> > automatically. More than that I'd like to get possible if something
> > happened to one server, all transactions will be performed on another
> > one.
> Symmetric or one-way? If one server is a main server, and the other is a
> standby server, you can use either log shipping or transactional
> replication. Log shipping is easier to manage, as transactional
> replication leaves its mark on the source database. On the other hand,
> the standby server will not be accessible each time a log is applied,
> and the lag time is longer.
> If it is symmetric, that actions are to be replicated in both directions,
> then you may want to look into merge replication. But this sounds anything
> about trivial to me. What about if the same row was updated in both
> databases? Who wins?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||GB (v7v1k3@.hotmail.com) writes:
> The only purpose I have is to have a "backup" server to be ready
> to substitute main server at any time with recent data in it
> and "seamless" switching in production mode.

OK. The I would suggest log shipping.

However, you cannot really switch into real production mode with
Developer Edition, as you don't have a license for it. But it is
of course, perfectly OK to set up log shipping to learn how to
use it for a production scenario.

Oh, I should add that once you come to production, you may only
have Standard or Workgroup Edition. Log Shipping is included in
Enterprise Edition only, so for these edition replication is about
the only option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In message <Xns97047DCD3077Yazorman@.127.0.0.1>, Erland Sommarskog
<esquel@.sommarskog.se> writes
>GB (v7v1k3@.hotmail.com) writes:
>> The only purpose I have is to have a "backup" server to be ready
>> to substitute main server at any time with recent data in it
>> and "seamless" switching in production mode.
>OK. The I would suggest log shipping.
>However, you cannot really switch into real production mode with
>Developer Edition, as you don't have a license for it. But it is
>of course, perfectly OK to set up log shipping to learn how to
>use it for a production scenario.
>Oh, I should add that once you come to production, you may only
>have Standard or Workgroup Edition. Log Shipping is included in
>Enterprise Edition only, so for these edition replication is about
>the only option.

If GB wants clustering and failover I think he will need the enterprise
edition.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.|||Bernard Peek (bap@.shrdlu.com) writes:
> If GB wants clustering and failover I think he will need the enterprise
> edition.

That is correct. (OK, since Developer Edition has all that Enterprise
Edition has, you can put that on a cluster. But if you put Developer
Edition on a cluster, then your hardware budget for your development
environment is quite excessive.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Bernard Peek" <bap@.shrdlu.com> wrote in message
news:ahtXDqTk30aDFwG3@.shrdlu.com...
> In message <Xns97047DCD3077Yazorman@.127.0.0.1>, Erland Sommarskog
> <esquel@.sommarskog.se> writes
> >GB (v7v1k3@.hotmail.com) writes:
> >> The only purpose I have is to have a "backup" server to be ready
> >> to substitute main server at any time with recent data in it
> >> and "seamless" switching in production mode.
> >OK. The I would suggest log shipping.
> >However, you cannot really switch into real production mode with
> >Developer Edition, as you don't have a license for it. But it is
> >of course, perfectly OK to set up log shipping to learn how to
> >use it for a production scenario.
> >Oh, I should add that once you come to production, you may only
> >have Standard or Workgroup Edition. Log Shipping is included in
> >Enterprise Edition only, so for these edition replication is about
> >the only option.
> If GB wants clustering and failover I think he will need the enterprise
> edition.

SQL Server 2005 will be released with DB mirroring, though in a "beta form".

He may want to look at this.

> --
> Bernard Peek
> London, UK. DBA, Manager, Trainer & Author.

Mirrored db - Witness server multiple networks

All,
I've just completed setting up a mirrored db with 2 servers that reside on
different networks. All the appropriate routes are added and the boxes see
each other fine both at the OS level and the SQL server level. My witness
server is a machine with 2 nics, each can talk to each network and it sees
both sql servers just fine. IP Forwarding is enabled. I've succesfully
configured the Primary to use the witness server and it sees it fine. However
the Mirror server is constantly reporting that the witness server is
disconnected. I can connect through management studio and every other just
fine. Anything I need due to the fact that the primary and the mirror are on
different networks when it comes to the witness box? Any suggestions
appreciated. There are no firewalls in play.
Thanks.
Hi
I am not sure why you have not configured these on their own subnet? I would
contact PSS to see if this is possible.
John
"sqlboy2000" wrote:

> All,
> I've just completed setting up a mirrored db with 2 servers that reside on
> different networks. All the appropriate routes are added and the boxes see
> each other fine both at the OS level and the SQL server level. My witness
> server is a machine with 2 nics, each can talk to each network and it sees
> both sql servers just fine. IP Forwarding is enabled. I've succesfully
> configured the Primary to use the witness server and it sees it fine. However
> the Mirror server is constantly reporting that the witness server is
> disconnected. I can connect through management studio and every other just
> fine. Anything I need due to the fact that the primary and the mirror are on
> different networks when it comes to the witness box? Any suggestions
> appreciated. There are no firewalls in play.
> Thanks.
|||You won't be able to use Windows authentication across networks - you will
need to use certificates.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:6605AA68-D48F-4827-A583-1302CBEB55C5@.microsoft.com...
> All,
> I've just completed setting up a mirrored db with 2 servers that reside on
> different networks. All the appropriate routes are added and the boxes see
> each other fine both at the OS level and the SQL server level. My witness
> server is a machine with 2 nics, each can talk to each network and it sees
> both sql servers just fine. IP Forwarding is enabled. I've succesfully
> configured the Primary to use the witness server and it sees it fine.
> However
> the Mirror server is constantly reporting that the witness server is
> disconnected. I can connect through management studio and every other just
> fine. Anything I need due to the fact that the primary and the mirror are
> on
> different networks when it comes to the witness box? Any suggestions
> appreciated. There are no firewalls in play.
> Thanks.
|||By "different networks" I meant different subnets.
"Roger Wolter[MSFT]" wrote:

> You won't be able to use Windows authentication across networks - you will
> need to use certificates.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:6605AA68-D48F-4827-A583-1302CBEB55C5@.microsoft.com...
>
>

Mirrored db - Witness server multiple networks

All,
I've just completed setting up a mirrored db with 2 servers that reside on
different networks. All the appropriate routes are added and the boxes see
each other fine both at the OS level and the SQL server level. My witness
server is a machine with 2 nics, each can talk to each network and it sees
both sql servers just fine. IP Forwarding is enabled. I've succesfully
configured the Primary to use the witness server and it sees it fine. However
the Mirror server is constantly reporting that the witness server is
disconnected. I can connect through management studio and every other just
fine. Anything I need due to the fact that the primary and the mirror are on
different networks when it comes to the witness box? Any suggestions
appreciated. There are no firewalls in play.
Thanks.Hi
I am not sure why you have not configured these on their own subnet? I would
contact PSS to see if this is possible.
John
"sqlboy2000" wrote:
> All,
> I've just completed setting up a mirrored db with 2 servers that reside on
> different networks. All the appropriate routes are added and the boxes see
> each other fine both at the OS level and the SQL server level. My witness
> server is a machine with 2 nics, each can talk to each network and it sees
> both sql servers just fine. IP Forwarding is enabled. I've succesfully
> configured the Primary to use the witness server and it sees it fine. However
> the Mirror server is constantly reporting that the witness server is
> disconnected. I can connect through management studio and every other just
> fine. Anything I need due to the fact that the primary and the mirror are on
> different networks when it comes to the witness box? Any suggestions
> appreciated. There are no firewalls in play.
> Thanks.|||You won't be able to use Windows authentication across networks - you will
need to use certificates.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:6605AA68-D48F-4827-A583-1302CBEB55C5@.microsoft.com...
> All,
> I've just completed setting up a mirrored db with 2 servers that reside on
> different networks. All the appropriate routes are added and the boxes see
> each other fine both at the OS level and the SQL server level. My witness
> server is a machine with 2 nics, each can talk to each network and it sees
> both sql servers just fine. IP Forwarding is enabled. I've succesfully
> configured the Primary to use the witness server and it sees it fine.
> However
> the Mirror server is constantly reporting that the witness server is
> disconnected. I can connect through management studio and every other just
> fine. Anything I need due to the fact that the primary and the mirror are
> on
> different networks when it comes to the witness box? Any suggestions
> appreciated. There are no firewalls in play.
> Thanks.

Mirrored db - Witness server multiple networks

All,
I've just completed setting up a mirrored db with 2 servers that reside on
different networks. All the appropriate routes are added and the boxes see
each other fine both at the OS level and the SQL server level. My witness
server is a machine with 2 nics, each can talk to each network and it sees
both sql servers just fine. IP Forwarding is enabled. I've succesfully
configured the Primary to use the witness server and it sees it fine. Howeve
r
the Mirror server is constantly reporting that the witness server is
disconnected. I can connect through management studio and every other just
fine. Anything I need due to the fact that the primary and the mirror are on
different networks when it comes to the witness box? Any suggestions
appreciated. There are no firewalls in play.
Thanks.Hi
I am not sure why you have not configured these on their own subnet? I would
contact PSS to see if this is possible.
John
"sqlboy2000" wrote:

> All,
> I've just completed setting up a mirrored db with 2 servers that reside on
> different networks. All the appropriate routes are added and the boxes see
> each other fine both at the OS level and the SQL server level. My witness
> server is a machine with 2 nics, each can talk to each network and it sees
> both sql servers just fine. IP Forwarding is enabled. I've succesfully
> configured the Primary to use the witness server and it sees it fine. Howe
ver
> the Mirror server is constantly reporting that the witness server is
> disconnected. I can connect through management studio and every other just
> fine. Anything I need due to the fact that the primary and the mirror are
on
> different networks when it comes to the witness box? Any suggestions
> appreciated. There are no firewalls in play.
> Thanks.|||You won't be able to use Windows authentication across networks - you will
need to use certificates.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:6605AA68-D48F-4827-A583-1302CBEB55C5@.microsoft.com...
> All,
> I've just completed setting up a mirrored db with 2 servers that reside on
> different networks. All the appropriate routes are added and the boxes see
> each other fine both at the OS level and the SQL server level. My witness
> server is a machine with 2 nics, each can talk to each network and it sees
> both sql servers just fine. IP Forwarding is enabled. I've succesfully
> configured the Primary to use the witness server and it sees it fine.
> However
> the Mirror server is constantly reporting that the witness server is
> disconnected. I can connect through management studio and every other just
> fine. Anything I need due to the fact that the primary and the mirror are
> on
> different networks when it comes to the witness box? Any suggestions
> appreciated. There are no firewalls in play.
> Thanks.|||By "different networks" I meant different subnets.
"Roger Wolter[MSFT]" wrote:

> You won't be able to use Windows authentication across networks - you will
> need to use certificates.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:6605AA68-D48F-4827-A583-1302CBEB55C5@.microsoft.com...
>
>sql

Mirror Status

Are there Stored Procedures or tables that will tell me which of the mirrored
servers is active ?Is this a database mirroring question? Mirroring is at the database level.
Try
select * from sys.database_mirroring
and look for mirroring_role and mirroring_role_desc. They should say
PRINCIPAL or MIRROR.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"PJU" wrote:
> Are there Stored Procedures or tables that will tell me which of the mirrored
> servers is active ?
>

Mirror Setup

Hello,

I'm trying to setup database mirroring between 3 servers and the details are as follows:

1. All 3 machines reside in the same domain.

2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.

3. All the 3 machines are having their individual service domain accounts.

4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.

5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.

6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.

7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.

8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.

9. On Server 2 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'

10. On Server 1 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'

ALTER DATABASE <dbname>

SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'

On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."

I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.

Thanks

I just have one question, did you restore the tran logs from principal to mirror using with norecovery option?.......coz you need to restore atleast 1tran log in mirror with norecovery option prior to configuring mirroring......|||

Hello Deepak,

I have restored 2 sets of tlog backups from principal to mirror -- first with norecovery mode and the second one with recovery mode. Still I'm getting the same errors.

Query Window

===========

Msg 1416, Level 16, State 31, Line 3

Database " " is not configured for database mirroring.

Error Log

=======

Error: 1443, Severity: 16, State: 2.

Database mirroring has been terminated for database ' '. This is an informational message only. No user action is required.

Do you have any other workaround for this problem?

Thanks

|||

Hello Deepak,

I have even tried with a sample database on the same set of servers and encountering the same set of errors in query window and error log. Is it something to do with any sort of configuration which I need to do before implementing database mirroring?

Thanks

|||

here is the mistake,

1. you should restore the full backup in the mirror using with norecovery clause

2.you need to restore one tran log in mirror using with norecovery clause

the idea behind restoring the full backups and tran log backups using with norecovery is that you can apply further transaction logs

since you have applied one with norecovey and other with recovery option further tran logs cannot be applied in the mirror...that seems to be the error.........perform the 2 steps as above and then start mirroring.....

|||

Hello Deepak,

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

abybody out there who can troubleshoot my problem?

TIA

|||

well as i said earlier you need to restore the full and subsequent tran log backups in mirror using with norecovery option.

this step has to be performed prior to configuring mirroring....further adding to your problem the error says, that the "database is not configured for mirroring".....which is a clear indication that either your mirror is not restored with norecovery or it is not in Full Recovery i believe.....this is what i know.....

|||

Hello Deepak,

It seems you haven't gone through my earlier messages and here it is

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

refer this,

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode > this has to be in norecovery mode but u have restored it in recover mode other steps are fine

|||

Hello Deepak,

Thanks for pointing the problem and I could setup mirroring successfully..

Mirror database, what about logins?

I have setup SQL Server 2005 SP1 with mirror of a database to another
server.
How am I supposed to duplicate the logins between the two servers and map
the logins defined at the server level to the database level?
OlavSSIS has a task that allows you to transfer logins. You have to carefully
plan the two servers to ensure that logins created on the principal(s) are
also created on the mirror.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Olav" <x@.y.com> wrote in message
news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>I have setup SQL Server 2005 SP1 with mirror of a database to another
>server.
> How am I supposed to duplicate the logins between the two servers and map
> the logins defined at the server level to the database level?
> Olav
>|||Yes, I understand that.
But, even if the logins are transferred they will not be mapped correctly to
the database after the failover from the Principal to the Mirror server.
It looks like I have to issue a command like this for every login in the
database:
USE Commerce;
GO
sp_change_users_login @.Action='update_one',
@.UserNamePattern='CommerceWebUser', @.LoginName='CommerceWebUser';
GO
How are you supposed to do an automatic failover when you have to issue
those kind of commands to get the Logins to work?
Olav
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> SSIS has a task that allows you to transfer logins. You have to carefully
> plan the two servers to ensure that logins created on the principal(s) are
> also created on the mirror.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Olav" <x@.y.com> wrote in message
> news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>>I have setup SQL Server 2005 SP1 with mirror of a database to another
>>server.
>> How am I supposed to duplicate the logins between the two servers and map
>> the logins defined at the server level to the database level?
>> Olav
>|||You have to make that part of the process when it fails over. To do it in
an automated way, you are going to need to create a job that checks the
state and executes a script to remap the logins in the event of a failure.
Database Mirroring does not handle logins or do anything at all to
synchronize them. This is by design. You can mirror multiple databases all
into the same SQL Server instance and if it automatically moved logins, it
could create major security problems. So, anything external to the database
is entirely up to you which means you have to handle the logins, linked
servers, remote servers, SSIS packages, custom error messages, jobs, and any
other server level objects.
Mirroring also does not handle multi-database issues. For example say you
had the following databases: customers, inventory, orders. When a customer
places an order you need to debit their remaining credit line, decrease
inventory, and insert into order + order detail table. From your
application's perspective, this is a single, atomic transaction spanning 3
databases. To SQL Server this is 3 completely independent transactions
since a transaction can not span databases. So, you can create issues
during a failover where the data does not match up from a business
perspective (credit line debited and inventory debited, but the order
doesn't exist because it was rolled back during the failover), but is
perfectly valid within each of the databases.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Olav" <x@.y.com> wrote in message
news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Yes, I understand that.
> But, even if the logins are transferred they will not be mapped correctly
> to the database after the failover from the Principal to the Mirror
> server.
> It looks like I have to issue a command like this for every login in the
> database:
> USE Commerce;
> GO
> sp_change_users_login @.Action='update_one',
> @.UserNamePattern='CommerceWebUser', @.LoginName='CommerceWebUser';
> GO
> How are you supposed to do an automatic failover when you have to issue
> those kind of commands to get the Logins to work?
> Olav
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>> SSIS has a task that allows you to transfer logins. You have to
>> carefully plan the two servers to ensure that logins created on the
>> principal(s) are also created on the mirror.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my sole
>> views on the subject. It does not represent the views of any other
>> person or entity either by inference or direct reference.
>>
>> "Olav" <x@.y.com> wrote in message
>> news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>>I have setup SQL Server 2005 SP1 with mirror of a database to another
>>server.
>> How am I supposed to duplicate the logins between the two servers and
>> map the logins defined at the server level to the database level?
>> Olav
>>
>|||How about using sp_help_revlogin instead?
http://support.microsoft.com/kb/246133/en-us
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav" <x@.y.com> wrote in message news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Yes, I understand that.
> But, even if the logins are transferred they will not be mapped correctly to the database after
> the failover from the Principal to the Mirror server.
> It looks like I have to issue a command like this for every login in the database:
> USE Commerce;
> GO
> sp_change_users_login @.Action='update_one', @.UserNamePattern='CommerceWebUser',
> @.LoginName='CommerceWebUser';
> GO
> How are you supposed to do an automatic failover when you have to issue those kind of commands to
> get the Logins to work?
> Olav
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>> SSIS has a task that allows you to transfer logins. You have to carefully plan the two servers
>> to ensure that logins created on the principal(s) are also created on the mirror.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my sole views on the subject.
>> It does not represent the views of any other person or entity either by inference or direct
>> reference.
>>
>> "Olav" <x@.y.com> wrote in message news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>>I have setup SQL Server 2005 SP1 with mirror of a database to another server.
>> How am I supposed to duplicate the logins between the two servers and map the logins defined at
>> the server level to the database level?
>> Olav
>>
>|||Where can I find a sample of such script?
It's hard to belive that this is not provided out of the box.
Olav
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uQ57htoaGHA.3376@.TK2MSFTNGP05.phx.gbl...
> You have to make that part of the process when it fails over. To do it in
> an automated way, you are going to need to create a job that checks the
> state and executes a script to remap the logins in the event of a failure.
> Database Mirroring does not handle logins or do anything at all to
> synchronize them. This is by design. You can mirror multiple databases
> all into the same SQL Server instance and if it automatically moved
> logins, it could create major security problems. So, anything external to
> the database is entirely up to you which means you have to handle the
> logins, linked servers, remote servers, SSIS packages, custom error
> messages, jobs, and any other server level objects.
> Mirroring also does not handle multi-database issues. For example say you
> had the following databases: customers, inventory, orders. When a
> customer places an order you need to debit their remaining credit line,
> decrease inventory, and insert into order + order detail table. From your
> application's perspective, this is a single, atomic transaction spanning 3
> databases. To SQL Server this is 3 completely independent transactions
> since a transaction can not span databases. So, you can create issues
> during a failover where the data does not match up from a business
> perspective (credit line debited and inventory debited, but the order
> doesn't exist because it was rolled back during the failover), but is
> perfectly valid within each of the databases.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Olav" <x@.y.com> wrote in message
> news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>> Yes, I understand that.
>> But, even if the logins are transferred they will not be mapped correctly
>> to the database after the failover from the Principal to the Mirror
>> server.
>> It looks like I have to issue a command like this for every login in the
>> database:
>> USE Commerce;
>> GO
>> sp_change_users_login @.Action='update_one',
>> @.UserNamePattern='CommerceWebUser', @.LoginName='CommerceWebUser';
>> GO
>> How are you supposed to do an automatic failover when you have to issue
>> those kind of commands to get the Logins to work?
>> Olav
>> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>> news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>> SSIS has a task that allows you to transfer logins. You have to
>> carefully plan the two servers to ensure that logins created on the
>> principal(s) are also created on the mirror.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my
>> sole views on the subject. It does not represent the views of any other
>> person or entity either by inference or direct reference.
>>
>> "Olav" <x@.y.com> wrote in message
>> news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>>I have setup SQL Server 2005 SP1 with mirror of a database to another
>>server.
>> How am I supposed to duplicate the logins between the two servers and
>> map the logins defined at the server level to the database level?
>> Olav
>>
>>
>|||I don't know of a sample for the script. You launch SSIS, select the
transfer logins task, save the package. You can also build your own using
sp_help_revlogin.
There is a reason that it is not provided and this is not done
automatically. That is because it CAN'T be done automatically. I can have
Inst1, Inst2, and Inst3 in my environment. DB1 on Inst1 is mirrored to
Inst3. DB2 on Inst2 is mirrored to Inst3. Inst1 has a SQL Server login
called mylogin that has access to DB1. Inst2 has a login called mylogin
that has access to DB2. The user accessing DB1 does not have the authority
to access DB2 and vice versa. BUT, I am mirroring both of them to Inst3.
Just how is mirroring supposed to resolve this? (Should you have that type
of configuration, no. But, it is possible and is just one of the dozens of
things that prevent this stuff from being done automatically.) And before
someone says, "I don't have that problem", they aren't designing software
for just a single system, this has to work reliably on every system that is
out there. So, unfortunately, everything is designed very conservatively,
especially when it comes to security implications.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Olav" <x@.y.com> wrote in message
news:ul7DEevaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Where can I find a sample of such script?
> It's hard to belive that this is not provided out of the box.
> Olav
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:uQ57htoaGHA.3376@.TK2MSFTNGP05.phx.gbl...
>> You have to make that part of the process when it fails over. To do it
>> in an automated way, you are going to need to create a job that checks
>> the state and executes a script to remap the logins in the event of a
>> failure.
>> Database Mirroring does not handle logins or do anything at all to
>> synchronize them. This is by design. You can mirror multiple databases
>> all into the same SQL Server instance and if it automatically moved
>> logins, it could create major security problems. So, anything external
>> to the database is entirely up to you which means you have to handle the
>> logins, linked servers, remote servers, SSIS packages, custom error
>> messages, jobs, and any other server level objects.
>> Mirroring also does not handle multi-database issues. For example say
>> you had the following databases: customers, inventory, orders. When a
>> customer places an order you need to debit their remaining credit line,
>> decrease inventory, and insert into order + order detail table. From
>> your application's perspective, this is a single, atomic transaction
>> spanning 3 databases. To SQL Server this is 3 completely independent
>> transactions since a transaction can not span databases. So, you can
>> create issues during a failover where the data does not match up from a
>> business perspective (credit line debited and inventory debited, but the
>> order doesn't exist because it was rolled back during the failover), but
>> is perfectly valid within each of the databases.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my sole
>> views on the subject. It does not represent the views of any other
>> person or entity either by inference or direct reference.
>> "Olav" <x@.y.com> wrote in message
>> news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>> Yes, I understand that.
>> But, even if the logins are transferred they will not be mapped
>> correctly to the database after the failover from the Principal to the
>> Mirror server.
>> It looks like I have to issue a command like this for every login in the
>> database:
>> USE Commerce;
>> GO
>> sp_change_users_login @.Action='update_one',
>> @.UserNamePattern='CommerceWebUser', @.LoginName='CommerceWebUser';
>> GO
>> How are you supposed to do an automatic failover when you have to issue
>> those kind of commands to get the Logins to work?
>> Olav
>> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>> news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>> SSIS has a task that allows you to transfer logins. You have to
>> carefully plan the two servers to ensure that logins created on the
>> principal(s) are also created on the mirror.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my
>> sole views on the subject. It does not represent the views of any
>> other person or entity either by inference or direct reference.
>>
>> "Olav" <x@.y.com> wrote in message
>> news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>>I have setup SQL Server 2005 SP1 with mirror of a database to another
>>server.
>> How am I supposed to duplicate the logins between the two servers and
>> map the logins defined at the server level to the database level?
>> Olav
>>
>>
>>
>|||Database mirroring is obviously designed to be an automated failover
solution, right?
I'm not asking for how to replicate the logins, I have already done that.
What I'm asking for is how to automate the failover process. I don't sit
besides my server and watch it until it failover in order to fix the logins
manually.
How do you design a job to run after the failover is complete so the logins
can be fixed? Can a job be started automatically right after the failover of
the database has completed?
Olav
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u9FPEryaGHA.508@.TK2MSFTNGP02.phx.gbl...
>I don't know of a sample for the script. You launch SSIS, select the
>transfer logins task, save the package. You can also build your own using
>sp_help_revlogin.
> There is a reason that it is not provided and this is not done
> automatically. That is because it CAN'T be done automatically. I can
> have Inst1, Inst2, and Inst3 in my environment. DB1 on Inst1 is mirrored
> to Inst3. DB2 on Inst2 is mirrored to Inst3. Inst1 has a SQL Server
> login called mylogin that has access to DB1. Inst2 has a login called
> mylogin that has access to DB2. The user accessing DB1 does not have the
> authority to access DB2 and vice versa. BUT, I am mirroring both of them
> to Inst3. Just how is mirroring supposed to resolve this? (Should you
> have that type of configuration, no. But, it is possible and is just one
> of the dozens of things that prevent this stuff from being done
> automatically.) And before someone says, "I don't have that problem",
> they aren't designing software for just a single system, this has to work
> reliably on every system that is out there. So, unfortunately, everything
> is designed very conservatively, especially when it comes to security
> implications.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Olav" <x@.y.com> wrote in message
> news:ul7DEevaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>> Where can I find a sample of such script?
>> It's hard to belive that this is not provided out of the box.
>> Olav
>> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>> news:uQ57htoaGHA.3376@.TK2MSFTNGP05.phx.gbl...
>> You have to make that part of the process when it fails over. To do it
>> in an automated way, you are going to need to create a job that checks
>> the state and executes a script to remap the logins in the event of a
>> failure.
>> Database Mirroring does not handle logins or do anything at all to
>> synchronize them. This is by design. You can mirror multiple databases
>> all into the same SQL Server instance and if it automatically moved
>> logins, it could create major security problems. So, anything external
>> to the database is entirely up to you which means you have to handle the
>> logins, linked servers, remote servers, SSIS packages, custom error
>> messages, jobs, and any other server level objects.
>> Mirroring also does not handle multi-database issues. For example say
>> you had the following databases: customers, inventory, orders. When a
>> customer places an order you need to debit their remaining credit line,
>> decrease inventory, and insert into order + order detail table. From
>> your application's perspective, this is a single, atomic transaction
>> spanning 3 databases. To SQL Server this is 3 completely independent
>> transactions since a transaction can not span databases. So, you can
>> create issues during a failover where the data does not match up from a
>> business perspective (credit line debited and inventory debited, but the
>> order doesn't exist because it was rolled back during the failover), but
>> is perfectly valid within each of the databases.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my
>> sole views on the subject. It does not represent the views of any other
>> person or entity either by inference or direct reference.
>> "Olav" <x@.y.com> wrote in message
>> news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>> Yes, I understand that.
>> But, even if the logins are transferred they will not be mapped
>> correctly to the database after the failover from the Principal to the
>> Mirror server.
>> It looks like I have to issue a command like this for every login in
>> the database:
>> USE Commerce;
>> GO
>> sp_change_users_login @.Action='update_one',
>> @.UserNamePattern='CommerceWebUser', @.LoginName='CommerceWebUser';
>> GO
>> How are you supposed to do an automatic failover when you have to issue
>> those kind of commands to get the Logins to work?
>> Olav
>> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
>> news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>> SSIS has a task that allows you to transfer logins. You have to
>> carefully plan the two servers to ensure that logins created on the
>> principal(s) are also created on the mirror.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my
>> sole views on the subject. It does not represent the views of any
>> other person or entity either by inference or direct reference.
>>
>> "Olav" <x@.y.com> wrote in message
>> news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>>I have setup SQL Server 2005 SP1 with mirror of a database to another
>>server.
>> How am I supposed to duplicate the logins between the two servers and
>> map the logins defined at the server level to the database level?
>> Olav
>>
>>
>>
>>
>

Mirror database, what about logins?

I have setup SQL Server 2005 SP1 with mirror of a database to another
server.
How am I supposed to duplicate the logins between the two servers and map
the logins defined at the server level to the database level?
OlavSSIS has a task that allows you to transfer logins. You have to carefully
plan the two servers to ensure that logins created on the principal(s) are
also created on the mirror.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Olav" <x@.y.com> wrote in message
news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>I have setup SQL Server 2005 SP1 with mirror of a database to another
>server.
> How am I supposed to duplicate the logins between the two servers and map
> the logins defined at the server level to the database level?
> Olav
>|||Yes, I understand that.
But, even if the logins are transferred they will not be mapped correctly to
the database after the failover from the Principal to the Mirror server.
It looks like I have to issue a command like this for every login in the
database:
USE Commerce;
GO
sp_change_users_login @.Action='update_one',
@.UserNamePattern='CommerceWebUser', @.LoginName='CommerceWebUser';
GO
How are you supposed to do an automatic failover when you have to issue
those kind of commands to get the Logins to work?
Olav
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> SSIS has a task that allows you to transfer logins. You have to carefully
> plan the two servers to ensure that logins created on the principal(s) are
> also created on the mirror.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Olav" <x@.y.com> wrote in message
> news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>|||You have to make that part of the process when it fails over. To do it in
an automated way, you are going to need to create a job that checks the
state and executes a script to remap the logins in the event of a failure.
Database Mirroring does not handle logins or do anything at all to
synchronize them. This is by design. You can mirror multiple databases all
into the same SQL Server instance and if it automatically moved logins, it
could create major security problems. So, anything external to the database
is entirely up to you which means you have to handle the logins, linked
servers, remote servers, SSIS packages, custom error messages, jobs, and any
other server level objects.
Mirroring also does not handle multi-database issues. For example say you
had the following databases: customers, inventory, orders. When a customer
places an order you need to debit their remaining credit line, decrease
inventory, and insert into order + order detail table. From your
application's perspective, this is a single, atomic transaction spanning 3
databases. To SQL Server this is 3 completely independent transactions
since a transaction can not span databases. So, you can create issues
during a failover where the data does not match up from a business
perspective (credit line debited and inventory debited, but the order
doesn't exist because it was rolled back during the failover), but is
perfectly valid within each of the databases.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Olav" <x@.y.com> wrote in message
news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Yes, I understand that.
> But, even if the logins are transferred they will not be mapped correctly
> to the database after the failover from the Principal to the Mirror
> server.
> It looks like I have to issue a command like this for every login in the
> database:
> USE Commerce;
> GO
> sp_change_users_login @.Action='update_one',
> @.UserNamePattern='CommerceWebUser', @.LoginName='CommerceWebUser';
> GO
> How are you supposed to do an automatic failover when you have to issue
> those kind of commands to get the Logins to work?
> Olav
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>|||How about using sp_help_revlogin instead?
http://support.microsoft.com/kb/246133/en-us
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav" <x@.y.com> wrote in message news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...en">
> Yes, I understand that.
> But, even if the logins are transferred they will not be mapped correctly
to the database after
> the failover from the Principal to the Mirror server.
> It looks like I have to issue a command like this for every login in the d
atabase:
> USE Commerce;
> GO
> sp_change_users_login @.Action='update_one', @.UserNamePattern='CommerceWebU
ser',
> @.LoginName='CommerceWebUser';
> GO
> How are you supposed to do an automatic failover when you have to issue th
ose kind of commands to
> get the Logins to work?
> Olav
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>|||Where can I find a sample of such script?
It's hard to belive that this is not provided out of the box.
Olav
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uQ57htoaGHA.3376@.TK2MSFTNGP05.phx.gbl...
> You have to make that part of the process when it fails over. To do it in
> an automated way, you are going to need to create a job that checks the
> state and executes a script to remap the logins in the event of a failure.
> Database Mirroring does not handle logins or do anything at all to
> synchronize them. This is by design. You can mirror multiple databases
> all into the same SQL Server instance and if it automatically moved
> logins, it could create major security problems. So, anything external to
> the database is entirely up to you which means you have to handle the
> logins, linked servers, remote servers, SSIS packages, custom error
> messages, jobs, and any other server level objects.
> Mirroring also does not handle multi-database issues. For example say you
> had the following databases: customers, inventory, orders. When a
> customer places an order you need to debit their remaining credit line,
> decrease inventory, and insert into order + order detail table. From your
> application's perspective, this is a single, atomic transaction spanning 3
> databases. To SQL Server this is 3 completely independent transactions
> since a transaction can not span databases. So, you can create issues
> during a failover where the data does not match up from a business
> perspective (credit line debited and inventory debited, but the order
> doesn't exist because it was rolled back during the failover), but is
> perfectly valid within each of the databases.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Olav" <x@.y.com> wrote in message
> news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>|||I don't know of a sample for the script. You launch SSIS, select the
transfer logins task, save the package. You can also build your own using
sp_help_revlogin.
There is a reason that it is not provided and this is not done
automatically. That is because it CAN'T be done automatically. I can have
Inst1, Inst2, and Inst3 in my environment. DB1 on Inst1 is mirrored to
Inst3. DB2 on Inst2 is mirrored to Inst3. Inst1 has a SQL Server login
called mylogin that has access to DB1. Inst2 has a login called mylogin
that has access to DB2. The user accessing DB1 does not have the authority
to access DB2 and vice versa. BUT, I am mirroring both of them to Inst3.
Just how is mirroring supposed to resolve this? (Should you have that type
of configuration, no. But, it is possible and is just one of the dozens of
things that prevent this stuff from being done automatically.) And before
someone says, "I don't have that problem", they aren't designing software
for just a single system, this has to work reliably on every system that is
out there. So, unfortunately, everything is designed very conservatively,
especially when it comes to security implications.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Olav" <x@.y.com> wrote in message
news:ul7DEevaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Where can I find a sample of such script?
> It's hard to belive that this is not provided out of the box.
> Olav
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:uQ57htoaGHA.3376@.TK2MSFTNGP05.phx.gbl...
>|||Database mirroring is obviously designed to be an automated failover
solution, right?
I'm not asking for how to replicate the logins, I have already done that.
What I'm asking for is how to automate the failover process. I don't sit
besides my server and watch it until it failover in order to fix the logins
manually.
How do you design a job to run after the failover is complete so the logins
can be fixed? Can a job be started automatically right after the failover of
the database has completed?
Olav
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u9FPEryaGHA.508@.TK2MSFTNGP02.phx.gbl...
>I don't know of a sample for the script. You launch SSIS, select the
>transfer logins task, save the package. You can also build your own using
>sp_help_revlogin.
> There is a reason that it is not provided and this is not done
> automatically. That is because it CAN'T be done automatically. I can
> have Inst1, Inst2, and Inst3 in my environment. DB1 on Inst1 is mirrored
> to Inst3. DB2 on Inst2 is mirrored to Inst3. Inst1 has a SQL Server
> login called mylogin that has access to DB1. Inst2 has a login called
> mylogin that has access to DB2. The user accessing DB1 does not have the
> authority to access DB2 and vice versa. BUT, I am mirroring both of them
> to Inst3. Just how is mirroring supposed to resolve this? (Should you
> have that type of configuration, no. But, it is possible and is just one
> of the dozens of things that prevent this stuff from being done
> automatically.) And before someone says, "I don't have that problem",
> they aren't designing software for just a single system, this has to work
> reliably on every system that is out there. So, unfortunately, everything
> is designed very conservatively, especially when it comes to security
> implications.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Olav" <x@.y.com> wrote in message
> news:ul7DEevaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>sql

Minimum rights for SQL Agent

Hi,
Here is a problem:
SQL 2000 servers on Win 2000 servers in NT4 Domain
Security restrictions exclude Everyone group from all the shares and
registries.
The SQL agent and SQL Server service accounts should NOT be Local or Domain
Administrative privileges.
What are the minimum rights and registry access required for these accounts
in order to operate?
Any help is greatly appreciated.
Regards,
JDHi,
Do not run SQL Server and SQL Agent services as local system, local
administrator, or domain administrator accounts.
If your services starts based on above, most of the jobs which require an OS
level admin previlages will fail.
Eg:
1. Using XP_CMDSHELL wrting into hard drives, Registry read/write/delete...
2. SQL Agent connection to SQL Server with Admini prev.
Go thru the below link for more information on setting up security,
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/SP3SEC02.ASP
Thanks
Hari
MCDBA
"Bruce Rhoades" <bruce.rhoades@.gdsinc.com> wrote in message
news:eI8S4C0#DHA.2484@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Here is a problem:
> SQL 2000 servers on Win 2000 servers in NT4 Domain
> Security restrictions exclude Everyone group from all the shares and
> registries.
> The SQL agent and SQL Server service accounts should NOT be Local or
Domain
> Administrative privileges.
> What are the minimum rights and registry access required for these
accounts
> in order to operate?
> Any help is greatly appreciated.
> Regards,
> JD
>|||I disagree.
There are a large number of bad side effects if the SQL service account is
NOT a member of the local administrators group on a server. It needs to be
a domain account so you can access domain resources, but not necessarily a
domain admin. If the box is dedicated to SQL, then there is really no
seciruty risk. If not, then you are in for more problems anyway.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Bruce Rhoades" <bruce.rhoades@.gdsinc.com> wrote in message
news:eI8S4C0%23DHA.2484@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Here is a problem:
> SQL 2000 servers on Win 2000 servers in NT4 Domain
> Security restrictions exclude Everyone group from all the shares and
> registries.
> The SQL agent and SQL Server service accounts should NOT be Local or
Domain
> Administrative privileges.
> What are the minimum rights and registry access required for these
accounts
> in order to operate?
> Any help is greatly appreciated.
> Regards,
> JD
>|||See the BOL topic "Setting up Windows Services Accounts" for more details
on the permissions needed. If you're on a cluster then the startup
accounts need to be local admins. There are a subset of activities outlined
in the above topic that also require a local admin. Otherwise, the
account(s) just needs to be added to SQL Server as sysadmins and have the
permissions outlined in the referenced topic. If you set the account
through Enterprise Manager then all the permissions are automatically set
for you.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.