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?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...
>
>|||select name, sid from master..syslogins
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82944D2B-DD3F-44F7-A7D7-495C446474B6@.microsoft.com...[vbcol=seagreen]
> How do I get the SIDs for logins on the primary?
> "Keith Kratochvil" wrote:
>|||Google for sp_help_revlogin. Run this periodically on the source server so y
ou 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: Cann
ot
> open database 'mydb' requested by the login. The login failed. Login faile
d
> 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, the
n 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 usi
ng
> 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?
>
Showing posts with label app. Show all posts
Showing posts with label app. Show all posts
Friday, March 30, 2012
Mirroring Failover - Have to run sp_change_users_login
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?
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...
>
>
|||select name, sid from master..syslogins
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82944D2B-DD3F-44F7-A7D7-495C446474B6@.microsoft.com...[vbcol=seagreen]
> How do I get the SIDs for logins on the primary?
> "Keith Kratochvil" wrote:
|||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?
>
sql
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?
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...
>
>
|||select name, sid from master..syslogins
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82944D2B-DD3F-44F7-A7D7-495C446474B6@.microsoft.com...[vbcol=seagreen]
> How do I get the SIDs for logins on the primary?
> "Keith Kratochvil" wrote:
|||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?
>
sql
Mirroring Failover - Have to run sp_change_users_login
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?
>
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?
>
Mirroring + remoting
Hi,
I have a 3-tier app that is currently running on a simple single server setup. However, I have to adapt it to run on the following setup:
1) Client .NET app on client systems with remoting connection to...
2) Host .NET app with a DAL connecting to ...
3) SQL Server 2005 mirrored
Using mirroring, if the principle SQL server goes down it switches automatically to the mirror server.
My question relates to my connections. What do I need to do to make sure the Client and Host apps follow SQL?
There are two setups I have in mind:
1) Host app + SQL on same box
2) Host app on its own server + SQL on its server (+mirror)
With these scenarios:
Setup 1 - SQL fails but server continues - Host app has to connect to mirror - clients continue to connect to Host on principle server.
Setup 1 - Whole server fails - Host on mirror server connects to mirror SQL and clints now have to locate Host on mirror.
Setup 2 - SQL's server fails so mirror switches in. Only Host app has to re-direct. Clients connect to same Host app.
This is new to me, so if anyone can advise, or direct me to the right info.
Hi,
I assume your solution employs the Presentation Layer <-> Business Layer <-> Data Layer architecture. Since you want to maximise transparency to the client application (pres layer), in my opinion, it's better to go with option 2 and put the host app on a separate server from the database to decrease the probability of multiple failures (if the machine with host + sql goes down - both will be inaccessible).
If you do this and code the host app to use ADO.NET to connect to the database, you could take advantage of the ability of the SqlConnection object to automatically re-connect to the failover partner in a mirrored configuration. This MSDN article explains how this works - http://msdn2.microsoft.com/en-us/library/5h52hef8.aspx.
In addition, if you want complete transparency to the client application, you will still have to make it handle the case where the host fails.
-- Kaloian.
|||Thanks Kaloian,
That leaves me with the problem of having the clinet apps fail over to another server when the host app's server fails. I guess I'm asking is there a remoting option to fail over, similar to the Failover Partner attribute in the sql connection string? Or am I going to have to trap the failure and code the switch?
|||As far as I know there's no such mechanism, but you may want to post this question to the .NET Remoting newsgroup - http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=41&SiteID=1
- Kaloian.
Mirroring + remoting
Hi,
I have a 3-tier app that is currently running on a simple single server setup. However, I have to adapt it to run on the following setup:
1) Client .NET app on client systems with remoting connection to...
2) Host .NET app with a DAL connecting to ...
3) SQL Server 2005 mirrored
Using mirroring, if the principle SQL server goes down it switches automatically to the mirror server.
My question relates to my connections. What do I need to do to make sure the Client and Host apps follow SQL?
There are two setups I have in mind:
1) Host app + SQL on same box
2) Host app on its own server + SQL on its server (+mirror)
With these scenarios:
Setup 1 - SQL fails but server continues - Host app has to connect to mirror - clients continue to connect to Host on principle server.
Setup 1 - Whole server fails - Host on mirror server connects to mirror SQL and clints now have to locate Host on mirror.
Setup 2 - SQL's server fails so mirror switches in. Only Host app has to re-direct. Clients connect to same Host app.
This is new to me, so if anyone can advise, or direct me to the right info.
Hi,
I assume your solution employs the Presentation Layer <-> Business Layer <-> Data Layer architecture. Since you want to maximise transparency to the client application (pres layer), in my opinion, it's better to go with option 2 and put the host app on a separate server from the database to decrease the probability of multiple failures (if the machine with host + sql goes down - both will be inaccessible).
If you do this and code the host app to use ADO.NET to connect to the database, you could take advantage of the ability of the SqlConnection object to automatically re-connect to the failover partner in a mirrored configuration. This MSDN article explains how this works - http://msdn2.microsoft.com/en-us/library/5h52hef8.aspx.
In addition, if you want complete transparency to the client application, you will still have to make it handle the case where the host fails.
-- Kaloian.
|||Thanks Kaloian,
That leaves me with the problem of having the clinet apps fail over to another server when the host app's server fails. I guess I'm asking is there a remoting option to fail over, similar to the Failover Partner attribute in the sql connection string? Or am I going to have to trap the failure and code the switch?
|||As far as I know there's no such mechanism, but you may want to post this question to the .NET Remoting newsgroup - http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=41&SiteID=1
- Kaloian.
Monday, March 26, 2012
Minor pdf-rendering error.
Hi.
We use RS intensively in our app. However, we have had several reports from our customers, that they cannot open our pdf-files on their macintosh computers. I have investigated the problem, and found that this is due to a very simple omission in the rendered pdf.
Apparently the 1.3 specs requires a "carriage return" (char decimal value 13) after the %%EOF-element. If I add this manually, the files open just fine on mac.
Is this a known issue?
Is there some way to get around it, apart from adding it manually in the code?
- Kristian
(Note: There might be some pdf-readers for mac that can read these files, but we have no way of controlling our customers ap-selection.)
I was a little fast on the trigger there.
After manually adding the carriage return, they still do not open on mac. It only made some of the unix-tools we used for testing stop complaining (pdf2ps).
Are there any known issues with pdf-files from RS and Macintosh compatibility.
|||We found out, that if we installed Adobe Reader on the Mac, we could view our file.
So it's propably the internal (default) pdf-viewer, that doesn't understand the RS pdf format.
sqlWednesday, March 21, 2012
Minimizing Locking Question
Hello all,
I am working on an Access 2000 project that is used to generate reports on archived sales data. This app uses linked tables that are pointing to some SQL 2K tables. Some of the queries are large and end up creating a table lock when executed, blocking all other users from using the app until it has finished executing the query. I would like to have queries that read data ignore this lock on the table but do not know of a way to do this using linked tables in Access 2000. I would use a NOLOCk table hint but need something else for this issue.
Any ideas?
ThanksWhat else do you need other than NOLOCK with linked tables?
I am working on an Access 2000 project that is used to generate reports on archived sales data. This app uses linked tables that are pointing to some SQL 2K tables. Some of the queries are large and end up creating a table lock when executed, blocking all other users from using the app until it has finished executing the query. I would like to have queries that read data ignore this lock on the table but do not know of a way to do this using linked tables in Access 2000. I would use a NOLOCk table hint but need something else for this issue.
Any ideas?
ThanksWhat else do you need other than NOLOCK with linked tables?
Subscribe to:
Posts (Atom)