database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2?
I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2'This is why DBMirroring is not recommended for applications which access
multiple databases concurrently.
It's important to keep in mind that DBMirroring is a database level
technology & therefore problems like yours really can't be solved fully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
<stevehunter_1@.hotmail.com> wrote in message
news:1176431795.881113.109140@.n76g2000hsh.googlegroups.com...
> If I have a stored procedure that accesses two databases, both
> databases are on server#1 (primary) and server#2 (backup)
> if database #1 fails over to server#2, what is my stored procedure
> going to do?
> SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
>
> How do i configure my stored procedure so that it is intelligent
> enough to recognize that databaseA has failed over and it knows to go
> to server#2'
>|||I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2'This is why DBMirroring is not recommended for applications which access
multiple databases concurrently.
It's important to keep in mind that DBMirroring is a database level
technology & therefore problems like yours really can't be solved fully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
<stevehunter_1@.hotmail.com> wrote in message
news:1176431795.881113.109140@.n76g2000hsh.googlegroups.com...
> If I have a stored procedure that accesses two databases, both
> databases are on server#1 (primary) and server#2 (backup)
> if database #1 fails over to server#2, what is my stored procedure
> going to do?
> SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
>
> How do i configure my stored procedure so that it is intelligent
> enough to recognize that databaseA has failed over and it knows to go
> to server#2'
>|||I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>sql

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
Kelly
Yes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:

> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>
|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.micro soft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
[vbcol=seagreen]
> "KellyLeia@.gmail.com" wrote:
|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> <EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.micro soft.com> wrote in message
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
KellyYes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:
> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
> "KellyLeia@.gmail.com" wrote:
>> Is there anyway to make the maintenance plan delete the backup files
>> BEFORE it backs up. My backups are failing due to lack of disk space,
>> because it essentially creates two copies of backups before deleting.
>> I know i can just write a script to delete them before hand, but it
>> would be alot neater to use the maintenace plan because the maintenance
>> plan is more dynamic that the script. Suggestions?
>> Kelly
>>|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> <EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> > Yes. If you create a backup plan using the Database Maintenance Wizard,
> > you
> > can set the amount of time you want to keep the backup files. In SQL 2005
> > it
> > translates in a Maintenance Cleanup Task.
> >
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>
> > "KellyLeia@.gmail.com" wrote:
> >
> >> Is there anyway to make the maintenance plan delete the backup files
> >> BEFORE it backs up. My backups are failing due to lack of disk space,
> >> because it essentially creates two copies of backups before deleting.
> >> I know i can just write a script to delete them before hand, but it
> >> would be alot neater to use the maintenace plan because the maintenance
> >> plan is more dynamic that the script. Suggestions?
> >>
> >> Kelly
> >>
> >>

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
KellyYes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:

> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
< EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
[vbcol=seagreen]
> "KellyLeia@.gmail.com" wrote:
>|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> < EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in messa
ge
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>