Balancing scalability with availability

We are beginning to design a new application with SQL Server 2005. Our current production environment is slated to be two SQL Server 2005 machines with the databases residing on an EMC SAN. We have requirements to both have automatic failover between servers for availability and also be able to balance the load over two hot servers for scalability.

Can anyone point me in the right direction for things I need to consider in order to be able to implement both of these requirements? Can I implement database mirroring (for failover) and transactional replication (for balancing) given the hardware configuration I'v mentioned? Is more information needed? Where should I turn next?

I am coming off a mainly Oracle background for the last ten or so years with a smattering of SQL Server mixed in. I've tried to hit the ground running on this project, but sometimes find myself hitting the wall running instead.

Thanks,

Larry

Automatic failover can be accomplished with Database Mirroring, and clustering. With both of these technologies the mirrored server or the failover node will be offline so you can't access the databases on these mirrored servers - hence they won't deliver load balancing. For load balancing you could use replication assuming all the DML occurs on one server. Replication will not provide client failover however.

-- Hilary Cotter

|||

That is not correct.

The instance hosting the mirror database is NOT offline. It is online and running otherwise transactions would not be able to reach it. The mirror database is inaccessible, because it is in a recovering state.

As far as clustering goes, the secondary node is also not offline. It is online and functioning, otherwise, there would be no way to failover to the node. There is only one copy of the database within a cluster, so you can't use the secondary node to execute anything.

The combination that is being asked for - automatic failover along with scalability is not possible. Failover clustering will automatically failover, but is not a scalability solution. Database Mirroring running in high availability operating mode will automatically failover, but is not a scalability solution. However, if your applications are not coded to intercept the disconnect and then reconnect to the cluster, it doesn't matter if the cluster automatically fails over, because only those applications forming new connections will find it. If your applications are not coded to utilize the new connection library in ADO.NET that shipped with VS 2005, then your applications are not going to automatically failover to the mirror when it gets promoted.

Replication is your closest bet to getting both fault tolerance as well as scalability. If your applications are built with a connection library that can detect that the server they are connected to is offline and then reform a connection to a secondary server and continue issuing transactions, then you can accomplish the basic goals. Your application then handles the automatic failover while the replication engine keeps everything synchronized to allow scalability. The basic options here are transactional with queued updating subscribers and merge. I would NOT configure a bi-directional or peer-to-peer architecture unless your are going to have full control over your applications and can guarantee that you can never have a conflict generated.

bad table design

A developer has just gave me a few tables table to put
into production, with all character columns defined as
varchar(255), null.
As I am absolutely positive that the majority of these
columns will not be storing this amount of data in each
of the columns, as some of them are code columns, types,
and one description field.
After asking him to review all of the columns and the
maximum estimated data stored in each one, he said that
it shouldn't make any difference because A) they're
varchar and B) they're defined as null.
What can I say to him to make him do the needful?
Thanks,
MarcusMarcus
Well, variablle-length characters ( as in your case) is stored 1 byte per
character.Declared but unused characters don't consume storage.
On the other hand I'll be conserned about using NULL's when you will be
querieng the tables.
Using IS NULL/NOT NULL clause will not allow Query Optimizer to use an index
and respectively your query will suffer from perfomance hit.
Also consider using DEFAULT values in those columns.
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus|||Pick up one of Joe Celko's post from here regarding how important it is to
get the database design right and where he state that he almost never have
to use NULL. If he question Joe's credibility, search for instance Amazon of
the books he has been writing (or go to www.celko.com).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus|||Right. I've brought that up also and he's come back and
said that the table is for importing data only and it
shouldn't matter.
You're thoughts?
Thanks!
Marcus
>--Original Message--
>Marcus
>Well, variablle-length characters ( as in your case) is
stored 1 byte per
>character.Declared but unused characters don't consume
storage.
>On the other hand I'll be conserned about using NULL's
when you will be
>querieng the tables.
>Using IS NULL/NOT NULL clause will not allow Query
Optimizer to use an index
>and respectively your query will suffer from perfomance
hit.
>Also consider using DEFAULT values in those columns.
>
>"Marcus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
types,
>
>.
>|||Marcus
Look, we can discuss a lot on this topic.
In outer-join operation you should carefully account for NULL's values that
are generated to preserve rows that don't have a match in the table being
joined.
Also dealing with NULL's adding complexity to the storage engine because SQL
Server keeps a special bitmap in every row to indicate which nullable
columns actually are NULL. SQL Server must decode the bitmap for every row
accessed.
Again I'd recommed you/your opponent to make all nullable columns as NOT
NULL and DEFAULT values.
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8c2301c404f2$255e24a0$a501280a@.phx.gbl...
> Right. I've brought that up also and he's come back and
> said that the table is for importing data only and it
> shouldn't matter.
> You're thoughts?
> Thanks!
> Marcus
> stored 1 byte per
> storage.
> when you will be
> Optimizer to use an index
> hit.
> message
> types,|||"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
Two aspects :
varchar(255), there is nothing wrong defining a varchar
type with a number of characters. Varchar(20) and varchar(255)
both occupie the same number of bytes if the strings are the same.
255 doesn't look like a 'natural' number, it is typical a number
which comes out of the head of a 'computer' oriented mind.
(80, 100, 200, 400 are more natural). But if the data comes
from another 'computer' system 255 could be the right number.
Null,
Never prefered and should be avoided. But for only importing
data (on the way to another table) and for text holding fields
on which no selection and no join is done a 'logical' choice.
If the text is human generated and a 'non' text is one of
the posibilities, I think that null is ok.
(When indexed, joined, used in a where clause one should
avoid a null).
Also concatenating with <null> strings should be avoided,
because this is handled differently in different databases.
(One could choose as wel for an empty string instead of
a null, this is supported in SQL-server, but be aware there
are RDBMSses which consider an empty string equal to
a Null string.)
I would not know of a default in a text string which is a
good replacement for a null string.
(Considering other factors as usage by programs, utilities
and other databases. Empty string is not supported
in all databases.
Using a single character as a default might conflict
with a 'real' single character even if it is a space.)
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
vvvvvvvvvv
Does anybody know of a good 'default' for character strings
instead of using a null ?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ben brugman

> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus

backupset table info

My employer wants me to extract information for each nightly database backup
s
for each production server.
So I have created a DTS package that extracts this info into excel using the
following script:
use msdb
go
select database_name as dbname,
convert(char(20),backup_start_date,100) as start_time,
convert(char(20),backup_finish_date,100)
as end_time, backup_size from
backupset
where backup_start_date >
(SELECT MAX(backup_start_date) FROM backupset) - 1
order by database_name, backup_start_date
Which gives the following output:
dbname start_time end_time backup_size
db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
He now wants two additional columns in the excel sheet. One with yesterday's
backup_size and 1 w ago's backup size.
Can anyone tell me how to modify this sql script to be able to retieve this
additional info from the backupset table?
Thanks,
HillaireLook at sysbackuphistory system table.
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> My employer wants me to extract information for each nightly database
backups
> for each production server.
> So I have created a DTS package that extracts this info into excel using
the
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> DB2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with
yesterday's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve
this
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||Try,
select
a.database_name as dbname,
convert(char(20),a.backup_start_date,100) as start_time,
convert(char(20),a.backup_finish_date,100) as end_time,
a.backup_size,
b.backup_size
from
msdb.dbo.backupset as a
left join
msdb.dbo.backupset as b
on
a.database_name = b.database_name
and a.backup_start_date > (SELECT MAX(backup_start_date) FROM
msdb.dbo.backupset) - 1
and b.backup_start_date = dateadd(w, -1, (SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset) - 1)
order by
a.database_name,
a.backup_start_date
AMB
"gracie" wrote:

> My employer wants me to extract information for each nightly database back
ups
> for each production server.
> So I have created a DTS package that extracts this info into excel using t
he
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with yesterday
's
> backup_size and 1 w ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve thi
s
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||What I actually need to accomplish is a comparison on the same excel sheet
showing yesterday's backup size and 1 w ago's backup size, side by side s
o
that he can look for any large decrepancies in the sizes.
"Uri Dimant" wrote:

> Look at sysbackuphistory system table.
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> backups
> the
> yesterday's
> this
>
>

Backups Failing on Production Servers

Hello,
Could you tell me please if there is something a DBA is supposed to do to
override an operating system error that states the device is offline and the
backups fail? The error is as follows:
BackupDiskFile::CreateMedia: Backup device
'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
system error = 32(The process cannot access the file because it is being used
by another process.).
Note*There was a job running that was doing some db activity, but this
should not prevent a backup from occurring. The job was running from 9:30 PM
to 11:30 PM and the dump starts at 11 PM.
BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge] TO
DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
NOFORMAT
How do you override an operating system error if it is possible to be sure
that production dumps are successful? Is there something the DBA can do?
Thanks
Your message is telling you that another process still has a hold of your
file.
You would need to figure out what process has a hold of it. I would go ask
whoever is in charge of putting your backups onto tape if his job is still
running. I used to always have these when my guy was trying to read from my
backup device to put my backup on tape, but I was trying to write to the file
to do a backup.
ChrisR
"Cynthia" wrote:

> Hello,
> Could you tell me please if there is something a DBA is supposed to do to
> override an operating system error that states the device is offline and the
> backups fail? The error is as follows:
> BackupDiskFile::CreateMedia: Backup device
> 'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
> system error = 32(The process cannot access the file because it is being used
> by another process.).
> Note*There was a job running that was doing some db activity, but this
> should not prevent a backup from occurring. The job was running from 9:30 PM
> to 11:30 PM and the dump starts at 11 PM.
> BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge] TO
> DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
> NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
> NOFORMAT
> How do you override an operating system error if it is possible to be sure
> that production dumps are successful? Is there something the DBA can do?
> Thanks
>

Backups Failing on Production Servers

Hello,
Could you tell me please if there is something a DBA is supposed to do to
override an operating system error that states the device is offline and the
backups fail? The error is as follows:
BackupDiskFile::CreateMedia: Backup device
'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
system error = 32(The process cannot access the file because it is being used
by another process.).
Note*There was a job running that was doing some db activity, but this
should not prevent a backup from occurring. The job was running from 9:30 PM
to 11:30 PM and the dump starts at 11 PM.
BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge] TO
DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
NOFORMAT
How do you override an operating system error if it is possible to be sure
that production dumps are successful? Is there something the DBA can do?
ThanksYour message is telling you that another process still has a hold of your
file.
You would need to figure out what process has a hold of it. I would go ask
whoever is in charge of putting your backups onto tape if his job is still
running. I used to always have these when my guy was trying to read from my
backup device to put my backup on tape, but I was trying to write to the file
to do a backup.
--
ChrisR
"Cynthia" wrote:
> Hello,
> Could you tell me please if there is something a DBA is supposed to do to
> override an operating system error that states the device is offline and the
> backups fail? The error is as follows:
> BackupDiskFile::CreateMedia: Backup device
> 'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
> system error = 32(The process cannot access the file because it is being used
> by another process.).
> Note*There was a job running that was doing some db activity, but this
> should not prevent a backup from occurring. The job was running from 9:30 PM
> to 11:30 PM and the dump starts at 11 PM.
> BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge] TO
> DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
> NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
> NOFORMAT
> How do you override an operating system error if it is possible to be sure
> that production dumps are successful? Is there something the DBA can do?
> Thanks
>

Backups Failing on Production Servers

Hello,
Could you tell me please if there is something a DBA is supposed to do to
override an operating system error that states the device is offline and the
backups fail? The error is as follows:
BackupDiskFile::CreateMedia: Backup device
'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
system error = 32(The process cannot access the file because it is being use
d
by another process.).
Note*There was a job running that was doing some db activity, but this
should not prevent a backup from occurring. The job was running from 9:30 PM
to 11:30 PM and the dump starts at 11 PM.
BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge
] TO
DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
NOFORMAT
How do you override an operating system error if it is possible to be sure
that production dumps are successful? Is there something the DBA can do?
ThanksYour message is telling you that another process still has a hold of your
file.
You would need to figure out what process has a hold of it. I would go ask
whoever is in charge of putting your backups onto tape if his job is still
running. I used to always have these when my guy was trying to read from my
backup device to put my backup on tape, but I was trying to write to the fil
e
to do a backup.
ChrisR
"Cynthia" wrote:

> Hello,
> Could you tell me please if there is something a DBA is supposed to do to
> override an operating system error that states the device is offline and t
he
> backups fail? The error is as follows:
> BackupDiskFile::CreateMedia: Backup device
> 'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
> system error = 32(The process cannot access the file because it is being u
sed
> by another process.).
> Note*There was a job running that was doing some db activity, but this
> should not prevent a backup from occurring. The job was running from 9:30
PM
> to 11:30 PM and the dump starts at 11 PM.
> BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEd
ge] TO
> DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
> NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
> NOFORMAT
> How do you override an operating system error if it is possible to be sure
> that production dumps are successful? Is there something the DBA can do?
> Thanks
>

Backups

Our current SAN capacity has just hit the wall and I need some advice. The
SAN stores our current production SQL db and the backups on another volume.
The volume that the backups for TL's and the database is on is completely
full. Can backups be sent to any server with capacity on the network or
would it be too much overhead? All the the servers are on a fibre switch
between all of the servers so I am not sure it would it would have that much
impact. Moving the backups would give us breathing room for a while until
the SAN is upgraded.Yeah you can - whether it's tolerable, doable or not is
going to depend on the size of the backups and the activity
on the network resources.
-Sue
On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
<Rockn@.newsgroups.nospam> wrote:

>Our current SAN capacity has just hit the wall and I need some advice. The
>SAN stores our current production SQL db and the backups on another volume.
>The volume that the backups for TL's and the database is on is completely
>full. Can backups be sent to any server with capacity on the network or
>would it be too much overhead? All the the servers are on a fibre switch
>between all of the servers so I am not sure it would it would have that muc
h
>impact. Moving the backups would give us breathing room for a while until
>the SAN is upgraded.
>|||I would probably do it at it's scheduled time which is currently 12 am when
no one is on the network.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lgpnd29qne8ajposrnuvtuspbu4gp05t0e@.
4ax.com...
> Yeah you can - whether it's tolerable, doable or not is
> going to depend on the size of the backups and the activity
> on the network resources.
> -Sue
> On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
> <Rockn@.newsgroups.nospam> wrote:
>
The[vbcol=seagreen]
volume.[vbcol=seagreen]
much[vbcol=seagreen]
>

Backups

Our current SAN capacity has just hit the wall and I need some advice. The
SAN stores our current production SQL db and the backups on another volume.
The volume that the backups for TL's and the database is on is completely
full. Can backups be sent to any server with capacity on the network or
would it be too much overhead? All the the servers are on a fibre switch
between all of the servers so I am not sure it would it would have that much
impact. Moving the backups would give us breathing room for a while until
the SAN is upgraded.Yeah you can - whether it's tolerable, doable or not is
going to depend on the size of the backups and the activity
on the network resources.
-Sue
On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
<Rockn@.newsgroups.nospam> wrote:
>Our current SAN capacity has just hit the wall and I need some advice. The
>SAN stores our current production SQL db and the backups on another volume.
>The volume that the backups for TL's and the database is on is completely
>full. Can backups be sent to any server with capacity on the network or
>would it be too much overhead? All the the servers are on a fibre switch
>between all of the servers so I am not sure it would it would have that much
>impact. Moving the backups would give us breathing room for a while until
>the SAN is upgraded.
>|||I would probably do it at it's scheduled time which is currently 12 am when
no one is on the network.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lgpnd29qne8ajposrnuvtuspbu4gp05t0e@.4ax.com...
> Yeah you can - whether it's tolerable, doable or not is
> going to depend on the size of the backups and the activity
> on the network resources.
> -Sue
> On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
> <Rockn@.newsgroups.nospam> wrote:
> >Our current SAN capacity has just hit the wall and I need some advice.
The
> >SAN stores our current production SQL db and the backups on another
volume.
> >The volume that the backups for TL's and the database is on is completely
> >full. Can backups be sent to any server with capacity on the network or
> >would it be too much overhead? All the the servers are on a fibre switch
> >between all of the servers so I am not sure it would it would have that
much
> >impact. Moving the backups would give us breathing room for a while until
> >the SAN is upgraded.
> >
>

Backup up warm-standby server

Hello all-
We have a two databases, BoxA and BoxB. BoxA is the live production server
and BoxB is a backup server that we do log shipping to.
Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
standby SQL server.
We would like to backup BoxB, the warm standby, however we receive the
following error message:
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database '' is in warm-standby state (set by
executing RESTORE WITH STANDBY) and cannot be backed up until the entire
load sequence is completed.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
"
Now is there any easy way to backup the database on BoxB without dropping
the maintenance plans?
Any help would be greatly appreciated.
We just had a discussion about this. See the thread "How can I backup a log-shipped database?".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"anonymous" <a@.b.bad> wrote in message news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>
|||Instead of trying to backup the database that is constantly recovering, why
not add another target server to your logshipping configuration?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"anonymous" <a@.b.bad> wrote in message
news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production
server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>

Backup up warm-standby server

Hello all-
We have a two databases, BoxA and BoxB. BoxA is the live production server
and BoxB is a backup server that we do log shipping to.
Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
standby SQL server.
We would like to backup BoxB, the warm standby, however we receive the
following error message:
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Database '' is in warm-standby state (set by
executing RESTORE WITH STANDBY) and cannot be backed up until the entire
load sequence is completed.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE i
s
terminating abnormally.
"
Now is there any easy way to backup the database on BoxB without dropping
the maintenance plans?
Any help would be greatly appreciated.We just had a discussion about this. See the thread "How can I backup a log-
shipped database?".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"anonymous" <a@.b.bad> wrote in message news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...[vbcol
=seagreen]
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production serve
r
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE
is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>[/vbcol]|||Instead of trying to backup the database that is constantly recovering, why
not add another target server to your logshipping configuration?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"anonymous" <a@.b.bad> wrote in message
news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production
server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE
is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>

Backup up warm-standby server

Hello all-
We have a two databases, BoxA and BoxB. BoxA is the live production server
and BoxB is a backup server that we do log shipping to.
Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
standby SQL server.
We would like to backup BoxB, the warm standby, however we receive the
following error message:
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database '' is in warm-standby state (set by
executing RESTORE WITH STANDBY) and cannot be backed up until the entire
load sequence is completed.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
"
Now is there any easy way to backup the database on BoxB without dropping
the maintenance plans?
Any help would be greatly appreciated.We just had a discussion about this. See the thread "How can I backup a log-shipped database?".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"anonymous" <a@.b.bad> wrote in message news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>|||Instead of trying to backup the database that is constantly recovering, why
not add another target server to your logshipping configuration?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"anonymous" <a@.b.bad> wrote in message
news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production
server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>