I have mirroring configured (with a witness server) on a database that is
being used by an ASP.NET application. The app uses SQL Authentication, and
the conneciton string includes the Failover Partner statement.
Whenever the database fails over, the app throws the following error: Cannot
open database 'mydb' requested by the login. The login failed. Login failed
for user 'myuser'.
Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
the problem, but only until I fail back over the the primary database, then I
have to run the script again. Every time I fail over (in either direction),
the sp_change_users_login needs to be run again before apps connection using
SQL Authentication will work.
Auto failover with mirroring, isn't really auto failover if I have to run
this script every time.
1) Is there a way to insert a custom script into the autofailover process?
2) Do I need to convert all applications using this db to use Windows Auth
only?
3) Are there any other solutions to this problem?... the first one gave me an error. I didn't think it had gone through.|||... the first one gave me an error. I didn't think it had gone through.|||Drop the [SQL] logins on your mirror.
When you run the CREATE LOGIN script on that server to create the logins,
make sure that you specify the SID of that particular user that is within
the primary server.
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
>I have mirroring configured (with a witness server) on a database that is
> being used by an ASP.NET application. The app uses SQL Authentication, and
> the conneciton string includes the Failover Partner statement.
> Whenever the database fails over, the app throws the following error:
> Cannot
> open database 'mydb' requested by the login. The login failed. Login
> failed
> for user 'myuser'.
> Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
> the problem, but only until I fail back over the the primary database,
> then I
> have to run the script again. Every time I fail over (in either
> direction),
> the sp_change_users_login needs to be run again before apps connection
> using
> SQL Authentication will work.
> Auto failover with mirroring, isn't really auto failover if I have to run
> this script every time.
> 1) Is there a way to insert a custom script into the autofailover process?
> 2) Do I need to convert all applications using this db to use Windows Auth
> only?
> 3) Are there any other solutions to this problem?
>|||How do I get the SIDs for logins on the primary?
"Keith Kratochvil" wrote:
> Drop the [SQL] logins on your mirror.
> When you run the CREATE LOGIN script on that server to create the logins,
> make sure that you specify the SID of that particular user that is within
> the primary server.
>
> --
> Keith Kratochvil
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
> >I have mirroring configured (with a witness server) on a database that is
> > being used by an ASP.NET application. The app uses SQL Authentication, and
> > the conneciton string includes the Failover Partner statement.
> >
> > Whenever the database fails over, the app throws the following error:
> > Cannot
> > open database 'mydb' requested by the login. The login failed. Login
> > failed
> > for user 'myuser'.
> >
> > Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
> > the problem, but only until I fail back over the the primary database,
> > then I
> > have to run the script again. Every time I fail over (in either
> > direction),
> > the sp_change_users_login needs to be run again before apps connection
> > using
> > SQL Authentication will work.
> >
> > Auto failover with mirroring, isn't really auto failover if I have to run
> > this script every time.
> >
> > 1) Is there a way to insert a custom script into the autofailover process?
> >
> > 2) Do I need to convert all applications using this db to use Windows Auth
> > only?
> >
> > 3) Are there any other solutions to this problem?
> >
>
>|||select name, sid from master..syslogins
--
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82944D2B-DD3F-44F7-A7D7-495C446474B6@.microsoft.com...
> How do I get the SIDs for logins on the primary?
> "Keith Kratochvil" wrote:
>> Drop the [SQL] logins on your mirror.
>> When you run the CREATE LOGIN script on that server to create the logins,
>> make sure that you specify the SID of that particular user that is within
>> the primary server.
>>
>> --
>> Keith Kratochvil
>>
>> "Dan" <Dan@.discussions.microsoft.com> wrote in message
>> news:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
>> >I have mirroring configured (with a witness server) on a database that
>> >is
>> > being used by an ASP.NET application. The app uses SQL Authentication,
>> > and
>> > the conneciton string includes the Failover Partner statement.
>> >
>> > Whenever the database fails over, the app throws the following error:
>> > Cannot
>> > open database 'mydb' requested by the login. The login failed. Login
>> > failed
>> > for user 'myuser'.
>> >
>> > Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser'
>> > fixes
>> > the problem, but only until I fail back over the the primary database,
>> > then I
>> > have to run the script again. Every time I fail over (in either
>> > direction),
>> > the sp_change_users_login needs to be run again before apps connection
>> > using
>> > SQL Authentication will work.
>> >
>> > Auto failover with mirroring, isn't really auto failover if I have to
>> > run
>> > this script every time.
>> >
>> > 1) Is there a way to insert a custom script into the autofailover
>> > process?
>> >
>> > 2) Do I need to convert all applications using this db to use Windows
>> > Auth
>> > only?
>> >
>> > 3) Are there any other solutions to this problem?
>> >
>>|||Google for sp_help_revlogin. Run this periodically on the source server so you have a script
containing the proper SID for each login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
>I have mirroring configured (with a witness server) on a database that is
> being used by an ASP.NET application. The app uses SQL Authentication, and
> the conneciton string includes the Failover Partner statement.
> Whenever the database fails over, the app throws the following error: Cannot
> open database 'mydb' requested by the login. The login failed. Login failed
> for user 'myuser'.
> Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
> the problem, but only until I fail back over the the primary database, then I
> have to run the script again. Every time I fail over (in either direction),
> the sp_change_users_login needs to be run again before apps connection using
> SQL Authentication will work.
> Auto failover with mirroring, isn't really auto failover if I have to run
> this script every time.
> 1) Is there a way to insert a custom script into the autofailover process?
> 2) Do I need to convert all applications using this db to use Windows Auth
> only?
> 3) Are there any other solutions to this problem?
>
Friday, March 30, 2012
Mirroring error filling up event log
Hi,
I'm getting this error in the application event log roughly every second:
Database Mirroring login attempt failed with error: 'Connection handshake
failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
This is in the event log of the primary mirror sever. The setup is 2
identical sql servers (SP1), in the same domain, using the same domain
account for all services. I've confirmed the sql service accounts are local
admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
anyone shed some light on this?
Thanks in advance.
Any help here Microsoft?
"sqlboy2000" wrote:
> Hi,
> I'm getting this error in the application event log roughly every second:
> Database Mirroring login attempt failed with error: 'Connection handshake
> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
> This is in the event log of the primary mirror sever. The setup is 2
> identical sql servers (SP1), in the same domain, using the same domain
> account for all services. I've confirmed the sql service accounts are local
> admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
> anyone shed some light on this?
> Thanks in advance.
|||SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
covers, and this message is logged by SSB as a result of an attempt to
connect by a mirroring session. You should either set up security correctly
or disable the mirroring session from the mentioned host (the IP is in the
error log, but apparently the IP was truncated in your post).
Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
the Setting Up Database Mirroring topic.
Don Vilen
This posting is provided "AS IS" with no warranties, and confers no rights
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...[vbcol=seagreen]
> Any help here Microsoft?
> "sqlboy2000" wrote:
|||Security was setup correctly, this post wasn't accurate.
I'm going to post the actual problem now that I understand what's happening.
"Don Vilen [MSFT]" wrote:
> SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
> covers, and this message is logged by SSB as a result of an attempt to
> connect by a mirroring session. You should either set up security correctly
> or disable the mirroring session from the mentioned host (the IP is in the
> error log, but apparently the IP was truncated in your post).
> Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
> the Setting Up Database Mirroring topic.
> --
> Don Vilen
> This posting is provided "AS IS" with no warranties, and confers no rights
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
>
I'm getting this error in the application event log roughly every second:
Database Mirroring login attempt failed with error: 'Connection handshake
failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
This is in the event log of the primary mirror sever. The setup is 2
identical sql servers (SP1), in the same domain, using the same domain
account for all services. I've confirmed the sql service accounts are local
admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
anyone shed some light on this?
Thanks in advance.
Any help here Microsoft?
"sqlboy2000" wrote:
> Hi,
> I'm getting this error in the application event log roughly every second:
> Database Mirroring login attempt failed with error: 'Connection handshake
> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
> This is in the event log of the primary mirror sever. The setup is 2
> identical sql servers (SP1), in the same domain, using the same domain
> account for all services. I've confirmed the sql service accounts are local
> admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
> anyone shed some light on this?
> Thanks in advance.
|||SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
covers, and this message is logged by SSB as a result of an attempt to
connect by a mirroring session. You should either set up security correctly
or disable the mirroring session from the mentioned host (the IP is in the
error log, but apparently the IP was truncated in your post).
Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
the Setting Up Database Mirroring topic.
Don Vilen
This posting is provided "AS IS" with no warranties, and confers no rights
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...[vbcol=seagreen]
> Any help here Microsoft?
> "sqlboy2000" wrote:
|||Security was setup correctly, this post wasn't accurate.
I'm going to post the actual problem now that I understand what's happening.
"Don Vilen [MSFT]" wrote:
> SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
> covers, and this message is logged by SSB as a result of an attempt to
> connect by a mirroring session. You should either set up security correctly
> or disable the mirroring session from the mentioned host (the IP is in the
> error log, but apparently the IP was truncated in your post).
> Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
> the Setting Up Database Mirroring topic.
> --
> Don Vilen
> This posting is provided "AS IS" with no warranties, and confers no rights
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
>
Mirroring error filling up event log
Hi,
I'm getting this error in the application event log roughly every second:
Database Mirroring login attempt failed with error: 'Connection handshake
failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
This is in the event log of the primary mirror sever. The setup is 2
identical sql servers (SP1), in the same domain, using the same domain
account for all services. I've confirmed the sql service accounts are local
admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
anyone shed some light on this?
Thanks in advance.Any help here Microsoft?
"sqlboy2000" wrote:
> Hi,
> I'm getting this error in the application event log roughly every second:
> Database Mirroring login attempt failed with error: 'Connection handshake
> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
> This is in the event log of the primary mirror sever. The setup is 2
> identical sql servers (SP1), in the same domain, using the same domain
> account for all services. I've confirmed the sql service accounts are local
> admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
> anyone shed some light on this?
> Thanks in advance.|||SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
covers, and this message is logged by SSB as a result of an attempt to
connect by a mirroring session. You should either set up security correctly
or disable the mirroring session from the mentioned host (the IP is in the
error log, but apparently the IP was truncated in your post).
Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
the Setting Up Database Mirroring topic.
--
Don Vilen
This posting is provided "AS IS" with no warranties, and confers no rights
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> Any help here Microsoft?
> "sqlboy2000" wrote:
>> Hi,
>> I'm getting this error in the application event log roughly every second:
>> Database Mirroring login attempt failed with error: 'Connection handshake
>> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
>> failed).
>> This is in the event log of the primary mirror sever. The setup is 2
>> identical sql servers (SP1), in the same domain, using the same domain
>> account for all services. I've confirmed the sql service accounts are
>> local
>> admins on both boxes. So I'm not sure what 'login attempt' is failing.
>> Can
>> anyone shed some light on this?
>> Thanks in advance.|||Security was setup correctly, this post wasn't accurate.
I'm going to post the actual problem now that I understand what's happening.
"Don Vilen [MSFT]" wrote:
> SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
> covers, and this message is logged by SSB as a result of an attempt to
> connect by a mirroring session. You should either set up security correctly
> or disable the mirroring session from the mentioned host (the IP is in the
> error log, but apparently the IP was truncated in your post).
> Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
> the Setting Up Database Mirroring topic.
> --
> Don Vilen
> This posting is provided "AS IS" with no warranties, and confers no rights
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> > Any help here Microsoft?
> >
> > "sqlboy2000" wrote:
> >
> >> Hi,
> >> I'm getting this error in the application event log roughly every second:
> >>
> >> Database Mirroring login attempt failed with error: 'Connection handshake
> >> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
> >> failed).
> >>
> >> This is in the event log of the primary mirror sever. The setup is 2
> >> identical sql servers (SP1), in the same domain, using the same domain
> >> account for all services. I've confirmed the sql service accounts are
> >> local
> >> admins on both boxes. So I'm not sure what 'login attempt' is failing.
> >> Can
> >> anyone shed some light on this?
> >>
> >> Thanks in advance.
>
I'm getting this error in the application event log roughly every second:
Database Mirroring login attempt failed with error: 'Connection handshake
failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
This is in the event log of the primary mirror sever. The setup is 2
identical sql servers (SP1), in the same domain, using the same domain
account for all services. I've confirmed the sql service accounts are local
admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
anyone shed some light on this?
Thanks in advance.Any help here Microsoft?
"sqlboy2000" wrote:
> Hi,
> I'm getting this error in the application event log roughly every second:
> Database Mirroring login attempt failed with error: 'Connection handshake
> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
> This is in the event log of the primary mirror sever. The setup is 2
> identical sql servers (SP1), in the same domain, using the same domain
> account for all services. I've confirmed the sql service accounts are local
> admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
> anyone shed some light on this?
> Thanks in advance.|||SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
covers, and this message is logged by SSB as a result of an attempt to
connect by a mirroring session. You should either set up security correctly
or disable the mirroring session from the mentioned host (the IP is in the
error log, but apparently the IP was truncated in your post).
Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
the Setting Up Database Mirroring topic.
--
Don Vilen
This posting is provided "AS IS" with no warranties, and confers no rights
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> Any help here Microsoft?
> "sqlboy2000" wrote:
>> Hi,
>> I'm getting this error in the application event log roughly every second:
>> Database Mirroring login attempt failed with error: 'Connection handshake
>> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
>> failed).
>> This is in the event log of the primary mirror sever. The setup is 2
>> identical sql servers (SP1), in the same domain, using the same domain
>> account for all services. I've confirmed the sql service accounts are
>> local
>> admins on both boxes. So I'm not sure what 'login attempt' is failing.
>> Can
>> anyone shed some light on this?
>> Thanks in advance.|||Security was setup correctly, this post wasn't accurate.
I'm going to post the actual problem now that I understand what's happening.
"Don Vilen [MSFT]" wrote:
> SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
> covers, and this message is logged by SSB as a result of an attempt to
> connect by a mirroring session. You should either set up security correctly
> or disable the mirroring session from the mentioned host (the IP is in the
> error log, but apparently the IP was truncated in your post).
> Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
> the Setting Up Database Mirroring topic.
> --
> Don Vilen
> This posting is provided "AS IS" with no warranties, and confers no rights
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> > Any help here Microsoft?
> >
> > "sqlboy2000" wrote:
> >
> >> Hi,
> >> I'm getting this error in the application event log roughly every second:
> >>
> >> Database Mirroring login attempt failed with error: 'Connection handshake
> >> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
> >> failed).
> >>
> >> This is in the event log of the primary mirror sever. The setup is 2
> >> identical sql servers (SP1), in the same domain, using the same domain
> >> account for all services. I've confirmed the sql service accounts are
> >> local
> >> admins on both boxes. So I'm not sure what 'login attempt' is failing.
> >> Can
> >> anyone shed some light on this?
> >>
> >> Thanks in advance.
>
Mirroring different versions
I am looking into Mirroring versus Replication. When going over the SQL2005
version features it list Mirroring (Safety Full Only). Does this mean the
database has to be in Full Recovery mode or that the mirror has to be
synchronous?
Thanks,
It means yes to both. With other editions of SQL Server 2005 (ie Enterprise
Edition) you have other options for mirroring, ie High Performance.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message
news:9CC6F9E4-41F0-4F7F-AC52-09E74ED88B9A@.microsoft.com...
>I am looking into Mirroring versus Replication. When going over the
>SQL2005
> version features it list Mirroring (Safety Full Only). Does this mean the
> database has to be in Full Recovery mode or that the mirror has to be
> synchronous?
> Thanks,
version features it list Mirroring (Safety Full Only). Does this mean the
database has to be in Full Recovery mode or that the mirror has to be
synchronous?
Thanks,
It means yes to both. With other editions of SQL Server 2005 (ie Enterprise
Edition) you have other options for mirroring, ie High Performance.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message
news:9CC6F9E4-41F0-4F7F-AC52-09E74ED88B9A@.microsoft.com...
>I am looking into Mirroring versus Replication. When going over the
>SQL2005
> version features it list Mirroring (Safety Full Only). Does this mean the
> database has to be in Full Recovery mode or that the mirror has to be
> synchronous?
> Thanks,
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:
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 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:
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
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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
Subscribe to:
Posts (Atom)