pivot information: log

Showing posts with label log. Show all posts

Friday, March 30, 2012

Please help me

l saw error message on log reader on replicated please suggest me
THANKS U
Another logreader agent for the subscription(s) is running.
NOTE: The step was retried the requested number of times (10) without succeeding .The step failedIs the logreader job still failing?
If so, the same logreader job is actually running on your publisher. It may be an orphaned process. If so, you will need to kill it.

--jfp|||It ok. Thank you so much

Wednesday, March 28, 2012

Please help (Sql server 2000) Critical Error

Please any one can help me ?I have multiuser application ( VB 6.0 ) on SQL Server 2000 running.

Last night my Sql Server was writting info on the Log file of one database when the power accidentally shut down. In fact, the server was executing one Microsoft Data Transformation Service ( DTS ) which transfers data from one table to another.

This morning when the users tried to work with this database everything went wrong... I've not been able to attach MDF AND LDF files to SQL Server 2000 Desktop Engine.

Looking for what happened I found those Info/Error LOG events:

A)Starting up database 'pcserveis'.

B)ex_raise2: Exception raised, major=34, minor=48, severity=21, attempting to create symptom dump
C) "The transaction ( Process Id 5 ) was left in "interblockade" in Lock
resources with another process and was chosen as subject of the "interblockade". Execute the transaction again." ( Sorry about wrong words , i'm using a translation tool from spanish )
D) Could not undo log record (5019077:221:133), for transaction ID (0:15254626), on page (1:1917), database 'pcserveis' (database ID 11). Page information: LSN = (5019072:426:5), type = 1. Log information: OpCode = 2, context 1.. ( Error: 3448, Severity: 21, State: 1)
E)Error while undoing logged operation in database 'pcserveis'. Error at log record ID (5019077:221:133).. (Error: 9004, Severity: 23, State: 7)

F)An error occurred while processing the log for database 'pcserveis'..

What can i do to restore my database? ThanksYou log file is toasted because of the power outage. Create a new log file and attach it to you database. But all transactions in th log file cannot be recovered. The other choice you have is to restore the database from the backup file and reprocess everything, assuming you have a good backup.|||Obviously, this morning when the users noticed me the problem , i've restored last backup and they kept on working.

My request was about if could be possible to repair the database more than a problem of losing data.

Is there any solution or the only thing to do is to restore the last backUp?|||You will only lose the uncommitted data if you rebuild the log file. That is probably you best bet for not losing the data between the time the database crushed and the last backup run in this situation.|||Thanks for your assistance.

Monday, March 26, 2012

Please help - Transaction Log not shrinking!

Hi,
My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
yesterday). DBCC Reindex was issued against all tables as part of
monthly DB maintenance. Since then I have performed a full DB backup,
have performed log backups, then performed log backup with truncate
only option, then reissued the DBCC Shrinkfile command and tried to
shrink the log file via SSMS - nothing happens. The transaction log
file is still over 25 GBs.
This is in SQL Server 2005. I've even changed the recovery model to
Simple, issued the DBCC Shrinkfile - no change, and then switched back
to FULL - still no change.
What am I missing here?First read about backup, and recovery model in Books Online. Then check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups.com...
> Hi,
> My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> yesterday). DBCC Reindex was issued against all tables as part of
> monthly DB maintenance. Since then I have performed a full DB backup,
> have performed log backups, then performed log backup with truncate
> only option, then reissued the DBCC Shrinkfile command and tried to
> shrink the log file via SSMS - nothing happens. The transaction log
> file is still over 25 GBs.
> This is in SQL Server 2005. I've even changed the recovery model to
> Simple, issued the DBCC Shrinkfile - no change, and then switched back
> to FULL - still no change.
> What am I missing here?
>|||Try this:
USE MASTER
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
GO
USE YourDatabase
DBCC SHRINKFILE (YourDatabase _log, 1000)
This should shrink the log file to 1GB
Ozcan
"Tibor Karaszi" wrote:
> First read about backup, and recovery model in Books Online. Then check out
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups.com...
> > Hi,
> >
> > My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> > yesterday). DBCC Reindex was issued against all tables as part of
> > monthly DB maintenance. Since then I have performed a full DB backup,
> > have performed log backups, then performed log backup with truncate
> > only option, then reissued the DBCC Shrinkfile command and tried to
> > shrink the log file via SSMS - nothing happens. The transaction log
> > file is still over 25 GBs.
> >
> > This is in SQL Server 2005. I've even changed the recovery model to
> > Simple, issued the DBCC Shrinkfile - no change, and then switched back
> > to FULL - still no change.
> >
> > What am I missing here?
> >
>

Please help - Transaction Log not shrinking!

Hi,
My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
yesterday). DBCC Reindex was issued against all tables as part of
monthly DB maintenance. Since then I have performed a full DB backup,
have performed log backups, then performed log backup with truncate
only option, then reissued the DBCC Shrinkfile command and tried to
shrink the log file via SSMS - nothing happens. The transaction log
file is still over 25 GBs.
This is in SQL Server 2005. I've even changed the recovery model to
Simple, issued the DBCC Shrinkfile - no change, and then switched back
to FULL - still no change.
What am I missing here?First read about backup, and recovery model in Books Online. Then check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups
.com...
> Hi,
> My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> yesterday). DBCC Reindex was issued against all tables as part of
> monthly DB maintenance. Since then I have performed a full DB backup,
> have performed log backups, then performed log backup with truncate
> only option, then reissued the DBCC Shrinkfile command and tried to
> shrink the log file via SSMS - nothing happens. The transaction log
> file is still over 25 GBs.
> This is in SQL Server 2005. I've even changed the recovery model to
> Simple, issued the DBCC Shrinkfile - no change, and then switched back
> to FULL - still no change.
> What am I missing here?
>|||First read about backup, and recovery model in Books Online. Then check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups
.com...
> Hi,
> My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> yesterday). DBCC Reindex was issued against all tables as part of
> monthly DB maintenance. Since then I have performed a full DB backup,
> have performed log backups, then performed log backup with truncate
> only option, then reissued the DBCC Shrinkfile command and tried to
> shrink the log file via SSMS - nothing happens. The transaction log
> file is still over 25 GBs.
> This is in SQL Server 2005. I've even changed the recovery model to
> Simple, issued the DBCC Shrinkfile - no change, and then switched back
> to FULL - still no change.
> What am I missing here?
>|||Try this:
USE MASTER
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
GO
USE YourDatabase
DBCC SHRINKFILE (YourDatabase _log, 1000)
This should shrink the log file to 1GB
Ozcan
"Tibor Karaszi" wrote:

> First read about backup, and recovery model in Books Online. Then check ou
t
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g20
00cwx.googlegroups.com...
>|||Try this:
USE MASTER
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
GO
USE YourDatabase
DBCC SHRINKFILE (YourDatabase _log, 1000)
This should shrink the log file to 1GB
Ozcan
"Tibor Karaszi" wrote:

> First read about backup, and recovery model in Books Online. Then check ou
t
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g20
00cwx.googlegroups.com...
>sql

PLEASE help - moving transaction log from one disk to another

SQL Server 7 SP3a.
In an effort to reclaim a large amount of space on disk, I have moved the
logfile from one drive to another using the steps outlined in this article:
http://www.microsoft.com/sql/techinfo/tips/administration/movingtransactionlogs.asp
when I attempted to reattach the database to the new log file on the new
drive, errors were thrown and SQL Server reported that the database was
corrupt.
Further research indicated that I had to hack the sysfiles1 table to change
the filename path to reflect the new drive location. I did this and rebooted
the server. This now created a suspect database.
How can I get this to work properly?
ThanksHi
I think there may be some problems with access rights /
permissions accessing new drive and files.
Detach and Attach usually works fine.
The indications of suspect status points the problem
accessing file / drive.
Thanks
Suri
>--Original Message--
>SQL Server 7 SP3a.
>In an effort to reclaim a large amount of space on disk,
I have moved the
>logfile from one drive to another using the steps
outlined in this article:
>http://www.microsoft.com/sql/techinfo/tips/administration/
movingtransactionlogs.asp
>when I attempted to reattach the database to the new log
file on the new
>drive, errors were thrown and SQL Server reported that
the database was
>corrupt.
>Further research indicated that I had to hack the
sysfiles1 table to change
>the filename path to reflect the new drive location. I
did this and rebooted
>the server. This now created a suspect database.
>How can I get this to work properly?
>Thanks
>
>.
>

Friday, March 23, 2012

Please evaluate this approach to shrinking log files

I've been tasked with taking over the support for a client's SQL Server
database. I'm not a DBA by profession, I'm a software developer who
uses SQL Server as a database designer.

The clients have reported that the server is running out of disk space
and examination shows that the log files for several of the databases
are at 5Gb or more.

After reading around the subject I suggested the following sequence of
operations:

-- Select the name of the database you want to shrink
USE MyDB

-- Dump unwanted transactions
dump tran MyDB with truncate_only

-- Get the name of the logfile
SELECT * from sysfiles

-- Having examined the rows returned by this use the log file...

-- Shrink the file to required size (in MB)
DBCC SHRINKFILE('MyDB_log', 10)

Is this a reasonable approach? Please bear in mind that I'm pretty new
to this, and I have many other tasks to do besides manage the server.
A previous DBA has set up good maintenance plans etc. so everything is
being properly backed up (well, I think it is)

If this IS a good approach, would it be reasonable to do this on, say,
a monthly basis as a scheduled job? Obviously the step

SELECT * from sysfiles

which gives us the physical name of the log file would be removed and
the job would operate explicitly on each log file for each database in
turn.

Many thanks for reading.

William Balmer.A previous DBA has set up good maintenance plans etc. so everything is

Quote:

Originally Posted by

being properly backed up (well, I think it is)

A common cause of unruly log files is that the database is in the FULL
recovery model but regular transaction log backups are not scheduled. Since
committed transactions won't get removed from the log until the log backup,
manual action is required to reduce the log size.

If you need to minimize data loss, the best approach is to schedule regular
log backups to run periodically between full database backups. You can
include this as part of a database maintenance plan. If more data loss is
acceptable (i.e. your plan is to simply restore from the last full backup),
you can use the SIMPLE recovery model so that committed data are
automatically removed from the log and you don't need to bother with log
backups.

In any case, the log should be sized to accommodate the activity between log
backups (FULL recovery) or the largest transaction (SIMPLE). The high-water
mark of this log space is simply the cost of doing business. IMHO, log file
shrinking should be done only after the log grows due to unusual activity.
I wouldn't schedule log shrinking because automating the process will only
hide the underlying cause.

Keep in mind that the number one responsibility of a DBA (or one that wears
a DBA hat) is to implement and test a backup/recovery plan. If you haven't
already done so, I suggest you run a restore test. You don't want any
surprises when you need to do it for real.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"William" <williambalmer@.hotmail.comwrote in message
news:1157713799.376752.153540@.i3g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

I've been tasked with taking over the support for a client's SQL Server
database. I'm not a DBA by profession, I'm a software developer who
uses SQL Server as a database designer.
>
The clients have reported that the server is running out of disk space
and examination shows that the log files for several of the databases
are at 5Gb or more.
>
After reading around the subject I suggested the following sequence of
operations:
>
-- Select the name of the database you want to shrink
USE MyDB
>
-- Dump unwanted transactions
dump tran MyDB with truncate_only
>
-- Get the name of the logfile
SELECT * from sysfiles
>
-- Having examined the rows returned by this use the log file...
>
-- Shrink the file to required size (in MB)
DBCC SHRINKFILE('MyDB_log', 10)
>
Is this a reasonable approach? Please bear in mind that I'm pretty new
to this, and I have many other tasks to do besides manage the server.
A previous DBA has set up good maintenance plans etc. so everything is
being properly backed up (well, I think it is)
>
If this IS a good approach, would it be reasonable to do this on, say,
a monthly basis as a scheduled job? Obviously the step
>
SELECT * from sysfiles
>
which gives us the physical name of the log file would be removed and
the job would operate explicitly on each log file for each database in
turn.
>
Many thanks for reading.
>
William Balmer.
>

|||Dan Guzman wrote:

snip

Many thanks, Dan - much to think about. I'll read up on the various
topics and may post later if I come up against a brick wall!

Thanks again

William Balmer

Please create news group for Log Shipping

Please create news group for Log Shipping
That is probably not going to happen. Microsoft has a pretty clear purpose
for each newsgroup, yet the newsgroups are broad enough to make it easy to
find what one is looking for without having to search thousands of
newsgroups for information.
If you have a question regarding log shipping it would probably fit best
within .server (or possibly .setup). Then again, many knowledgeable people
hang out in all the sqlserver groups so you are probably going to get a hit
no matter where you post.
in the meantime, check out these links:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
Keith
"Mayur Dhondekar" <dmny2k@.hotmail.com> wrote in message
news:2d08001c469c8$f0a55800$a501280a@.phx.gbl...
> Please create news group for Log Shipping
|||Hi
I already checked the documents on microsoft web sites. i
have opened a new message and looking for some kind of
workaround on that problem.
Thanks
Mayur

>--Original Message--
>That is probably not going to happen. Microsoft has a
pretty clear purpose
>for each newsgroup, yet the newsgroups are broad enough
to make it easy to
>find what one is looking for without having to search
thousands of
>newsgroups for information.
>If you have a question regarding log shipping it would
probably fit best
>within .server (or possibly .setup). Then again, many
knowledgeable people
>hang out in all the sqlserver groups so you are probably
going to get a hit
>no matter where you post.
>in the meantime, check out these links:
>314515 INF: Frequently Asked Questions - SQL Server 2000 -
Log Shipping
>http://support.microsoft.com/?id=314515
>323135 INF: Microsoft SQL Server 2000 - How to Set Up Log
Shipping (White
>Paper)
>http://support.microsoft.com/?id=323135
>325220 Support WebCast: Microsoft SQL Server 2000 Log
Shipping
>http://support.microsoft.com/?id=325220
>821786 Support WebCast: Microsoft SQL Server 2000: Using
Log Shipping
>http://support.microsoft.com/?id=821786
>321247 HOW TO: Configure Security for Log Shipping
>http://support.microsoft.com/?id=321247
>329133 INF: Troubleshooting SQL Server 2000 Log
Shipping "Out of Sync"
>Errors
>http://support.microsoft.com/?id=329133
>--
>Keith
>
>"Mayur Dhondekar" <dmny2k@.hotmail.com> wrote in message
>news:2d08001c469c8$f0a55800$a501280a@.phx.gbl...
>.
>

Please create news group for Log Shipping

Please create news group for Log ShippingThat is probably not going to happen. Microsoft has a pretty clear purpose
for each newsgroup, yet the newsgroups are broad enough to make it easy to
find what one is looking for without having to search thousands of
newsgroups for information.
If you have a question regarding log shipping it would probably fit best
within .server (or possibly .setup). Then again, many knowledgeable people
hang out in all the sqlserver groups so you are probably going to get a hit
no matter where you post.
in the meantime, check out these links:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
Keith
"Mayur Dhondekar" <dmny2k@.hotmail.com> wrote in message
news:2d08001c469c8$f0a55800$a501280a@.phx
.gbl...
> Please create news group for Log Shipping|||Hi
I already checked the documents on microsoft web sites. i
have opened a new message and looking for some kind of
workaround on that problem.
Thanks
Mayur

>--Original Message--
>That is probably not going to happen. Microsoft has a
pretty clear purpose
>for each newsgroup, yet the newsgroups are broad enough
to make it easy to
>find what one is looking for without having to search
thousands of
>newsgroups for information.
>If you have a question regarding log shipping it would
probably fit best
>within .server (or possibly .setup). Then again, many
knowledgeable people
>hang out in all the sqlserver groups so you are probably
going to get a hit
>no matter where you post.
>in the meantime, check out these links:
>314515 INF: Frequently Asked Questions - SQL Server 2000 -
Log Shipping
>http://support.microsoft.com/?id=314515
>323135 INF: Microsoft SQL Server 2000 - How to Set Up Log
Shipping (White
>Paper)
>http://support.microsoft.com/?id=323135
>325220 Support WebCast: Microsoft SQL Server 2000 Log
Shipping
>http://support.microsoft.com/?id=325220
>821786 Support WebCast: Microsoft SQL Server 2000: Using
Log Shipping
>http://support.microsoft.com/?id=821786
>321247 HOW TO: Configure Security for Log Shipping
>http://support.microsoft.com/?id=321247
>329133 INF: Troubleshooting SQL Server 2000 Log
Shipping "Out of Sync"
>Errors
>http://support.microsoft.com/?id=329133
>--
>Keith
>
>"Mayur Dhondekar" <dmny2k@.hotmail.com> wrote in message
> news:2d08001c469c8$f0a55800$a501280a@.phx
.gbl...
>.
>

Please create news group for Log Shipping

Please create news group for Log ShippingThat is probably not going to happen. Microsoft has a pretty clear purpose
for each newsgroup, yet the newsgroups are broad enough to make it easy to
find what one is looking for without having to search thousands of
newsgroups for information.
If you have a question regarding log shipping it would probably fit best
within .server (or possibly .setup). Then again, many knowledgeable people
hang out in all the sqlserver groups so you are probably going to get a hit
no matter where you post.
in the meantime, check out these links:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
--
Keith
"Mayur Dhondekar" <dmny2k@.hotmail.com> wrote in message
news:2d08001c469c8$f0a55800$a501280a@.phx.gbl...
> Please create news group for Log Shipping|||Hi
I already checked the documents on microsoft web sites. i
have opened a new message and looking for some kind of
workaround on that problem.
Thanks
Mayur
>--Original Message--
>That is probably not going to happen. Microsoft has a
pretty clear purpose
>for each newsgroup, yet the newsgroups are broad enough
to make it easy to
>find what one is looking for without having to search
thousands of
>newsgroups for information.
>If you have a question regarding log shipping it would
probably fit best
>within .server (or possibly .setup). Then again, many
knowledgeable people
>hang out in all the sqlserver groups so you are probably
going to get a hit
>no matter where you post.
>in the meantime, check out these links:
>314515 INF: Frequently Asked Questions - SQL Server 2000 -
Log Shipping
>http://support.microsoft.com/?id=314515
>323135 INF: Microsoft SQL Server 2000 - How to Set Up Log
Shipping (White
>Paper)
>http://support.microsoft.com/?id=323135
>325220 Support WebCast: Microsoft SQL Server 2000 Log
Shipping
>http://support.microsoft.com/?id=325220
>821786 Support WebCast: Microsoft SQL Server 2000: Using
Log Shipping
>http://support.microsoft.com/?id=821786
>321247 HOW TO: Configure Security for Log Shipping
>http://support.microsoft.com/?id=321247
>329133 INF: Troubleshooting SQL Server 2000 Log
Shipping "Out of Sync"
>Errors
>http://support.microsoft.com/?id=329133
>--
>Keith
>
>"Mayur Dhondekar" <dmny2k@.hotmail.com> wrote in message
>news:2d08001c469c8$f0a55800$a501280a@.phx.gbl...
>> Please create news group for Log Shipping
>.
>

Tuesday, March 20, 2012

Planning of log backup suggestions

Hello Guys,
I would like to overwrite the log file every two weeks.
The log file is backed up every hours every hours (append to the
device) and the full backup runs every day at 11 pm.
I would like to run a job that overwrite the log backup every two
weeks. What do you advice me to do? at which time is better?
I would like to overwrite the file every two weeks at 10 pm before of
the full backup.
any suggestion?
Inaina
> I would like to overwrite the log file every two weeks.
> The log file is backed up every hours every hours (append to the
> device) and the full backup runs every day at 11 pm.
BACKUP LOG... WITH INIT just after FULL BACKUP of the database
If you do Database20061101.bak as regular full backup , try to compare
GETDATE() -14 with '20061101' portion of .BAK file
Tools
1)CONVERT function
2)SUBSTRING/RIGHT/LEFT functions
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1163408898.605077.87490@.m73g2000cwd.googlegroups.com...
> Hello Guys,
> I would like to overwrite the log file every two weeks.
> The log file is backed up every hours every hours (append to the
> device) and the full backup runs every day at 11 pm.
>
> I would like to run a job that overwrite the log backup every two
> weeks. What do you advice me to do? at which time is better?
> I would like to overwrite the file every two weeks at 10 pm before of
> the full backup.
> any suggestion?
> Ina
>|||Thanks Uri,
I did not understand the this:
If you do Database20061101.bak as regular full backup , try to compare
> GETDATE() -14 with '20061101' portion of .BAK file
> Tools
> 1)CONVERT function
> 2)SUBSTRING/RIGHT/LEFT functions
Ina
Uri Dimant wrote:
> ina
> > I would like to overwrite the log file every two weeks.
> >
> > The log file is backed up every hours every hours (append to the
> > device) and the full backup runs every day at 11 pm.
>
> BACKUP LOG... WITH INIT just after FULL BACKUP of the database
> If you do Database20061101.bak as regular full backup , try to compare
> GETDATE() -14 with '20061101' portion of .BAK file
> Tools
> 1)CONVERT function
> 2)SUBSTRING/RIGHT/LEFT functions
>
>
>
> "ina" <roberta.inalbon@.gmail.com> wrote in message
> news:1163408898.605077.87490@.m73g2000cwd.googlegroups.com...
> > Hello Guys,
> >
> > I would like to overwrite the log file every two weeks.
> >
> > The log file is backed up every hours every hours (append to the
> > device) and the full backup runs every day at 11 pm.
> >
> >
> > I would like to run a job that overwrite the log backup every two
> > weeks. What do you advice me to do? at which time is better?
> >
> > I would like to overwrite the file every two weeks at 10 pm before of
> > the full backup.
> >
> > any suggestion?
> >
> > Ina
> >|||ina
It is how you identify your backup file
DECLARE @.FileName AS VARCHAR(255)
DECLARE @.Date AS VARCHAR(20)
SET @.Date =CONVERT(VARCHAR(10),GETDATE(),112)
SELECT @.FileName = 'C:\dbname' + @.Date+'.bak'
BACKUP DATABASE dbname TO DISK = @.FileName
Now you have to compare , whether the .BAK file is two weeks old and if it
is you just perform BACKUP LOG ...WITH INIT
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1163414330.502761.160300@.h48g2000cwc.googlegroups.com...
> Thanks Uri,
> I did not understand the this:
> If you do Database20061101.bak as regular full backup , try to compare
>> GETDATE() -14 with '20061101' portion of .BAK file
>> Tools
>> 1)CONVERT function
>> 2)SUBSTRING/RIGHT/LEFT functions
> Ina
>
> Uri Dimant wrote:
>> ina
>> > I would like to overwrite the log file every two weeks.
>> >
>> > The log file is backed up every hours every hours (append to the
>> > device) and the full backup runs every day at 11 pm.
>>
>> BACKUP LOG... WITH INIT just after FULL BACKUP of the database
>> If you do Database20061101.bak as regular full backup , try to compare
>> GETDATE() -14 with '20061101' portion of .BAK file
>> Tools
>> 1)CONVERT function
>> 2)SUBSTRING/RIGHT/LEFT functions
>>
>>
>>
>> "ina" <roberta.inalbon@.gmail.com> wrote in message
>> news:1163408898.605077.87490@.m73g2000cwd.googlegroups.com...
>> > Hello Guys,
>> >
>> > I would like to overwrite the log file every two weeks.
>> >
>> > The log file is backed up every hours every hours (append to the
>> > device) and the full backup runs every day at 11 pm.
>> >
>> >
>> > I would like to run a job that overwrite the log backup every two
>> > weeks. What do you advice me to do? at which time is better?
>> >
>> > I would like to overwrite the file every two weeks at 10 pm before of
>> > the full backup.
>> >
>> > any suggestion?
>> >
>> > Ina
>> >
>|||Ah ok thanks did not know So I can do a job that everyday test this
validation and if it true a overwrite the device.
Ina
Uri Dimant wrote:
> ina
> It is how you identify your backup file
> DECLARE @.FileName AS VARCHAR(255)
> DECLARE @.Date AS VARCHAR(20)
> SET @.Date =CONVERT(VARCHAR(10),GETDATE(),112)
> SELECT @.FileName = 'C:\dbname' + @.Date+'.bak'
> BACKUP DATABASE dbname TO DISK = @.FileName
>
> Now you have to compare , whether the .BAK file is two weeks old and if it
> is you just perform BACKUP LOG ...WITH INIT
>
>
>
>
> "ina" <roberta.inalbon@.gmail.com> wrote in message
> news:1163414330.502761.160300@.h48g2000cwc.googlegroups.com...
> > Thanks Uri,
> >
> > I did not understand the this:
> >
> > If you do Database20061101.bak as regular full backup , try to compare
> >
> >> GETDATE() -14 with '20061101' portion of .BAK file
> >>
> >> Tools
> >>
> >> 1)CONVERT function
> >> 2)SUBSTRING/RIGHT/LEFT functions
> >
> > Ina
> >
> >
> > Uri Dimant wrote:
> >
> >> ina
> >>
> >> > I would like to overwrite the log file every two weeks.
> >> >
> >> > The log file is backed up every hours every hours (append to the
> >> > device) and the full backup runs every day at 11 pm.
> >>
> >>
> >> BACKUP LOG... WITH INIT just after FULL BACKUP of the database
> >>
> >> If you do Database20061101.bak as regular full backup , try to compare
> >> GETDATE() -14 with '20061101' portion of .BAK file
> >>
> >> Tools
> >>
> >> 1)CONVERT function
> >> 2)SUBSTRING/RIGHT/LEFT functions
> >>
> >>
> >>
> >>
> >>
> >>
> >> "ina" <roberta.inalbon@.gmail.com> wrote in message
> >> news:1163408898.605077.87490@.m73g2000cwd.googlegroups.com...
> >> > Hello Guys,
> >> >
> >> > I would like to overwrite the log file every two weeks.
> >> >
> >> > The log file is backed up every hours every hours (append to the
> >> > device) and the full backup runs every day at 11 pm.
> >> >
> >> >
> >> > I would like to run a job that overwrite the log backup every two
> >> > weeks. What do you advice me to do? at which time is better?
> >> >
> >> > I would like to overwrite the file every two weeks at 10 pm before of
> >> > the full backup.
> >> >
> >> > any suggestion?
> >> >
> >> > Ina
> >> >
> >

Planning of log backup suggestions

Planning for Disaster Recovery in second data center..

We are planning on using Database Mirroring internally within the data
center for HA and since its used, I am left now with log shipping or
possibly some SAN replication.
I think either one will work with log shipping being very affordable.. Free
!!!
Does anyone see any pros to SAN replication as the strategy compared to Log
shipping to have the data to the remote site. We are looking at RTO and RPO
of maybe 30 mins of downtime and no loss of 30 mins of data in the event of
a disaster.
On 3 Apr, 05:04, "Hassan" <has...@.hotmail.com> wrote:
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable.. Free
> !!!
> Does anyone see any pros to SAN replication as the strategy compared to Log
> shipping to have the data to the remote site. We are looking at RTO and RPO
> of maybe 30 mins of downtime and no loss of 30 mins of data in the event of
> a disaster.
The replication is simpler, in the sense that you have one thing to
monitor, and in theory preferrable. We went for log shipping on the
basis that we were unsure about the burden of the extra data across
the network and some reservations about knowing the state of disk
writes to the data centre. Log shipping gives certainty in the sense
that the database is written to a known transaction at a known point,
even if it may not restore to the last possible moment before failure.
And as you say, it's cheap.
|||Hello Hassan,
Better approach for high availability is create a cluster in local data
center for the production SQL Server machines and for disaster recovery site
use Mirroring/Replication or Logshipping.
To plan the disaster site you should do a capasity planning on data growth,
network bandwidth...
If you have enough budget you could take a look into options like EMC's
SRDF.
http://www.emc.com/products/networking/srdf.jsp
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable..
> Free !!!
> Does anyone see any pros to SAN replication as the strategy compared to
> Log shipping to have the data to the remote site. We are looking at RTO
> and RPO of maybe 30 mins of downtime and no loss of 30 mins of data in the
> event of a disaster.
>
>
|||"Hassan" <hassan@.hotmail.com> wrote in message
news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable..
> Free !!!
> Does anyone see any pros to SAN replication as the strategy compared to
> Log shipping to have the data to the remote site. We are looking at RTO
> and RPO of maybe 30 mins of downtime and no loss of 30 mins of data in the
> event of a disaster.
>
>
Personally in a case like this, I'd probably go with log-shipping over SAN
replication.
It's cheaper. It's also easier to ensure a consistent transactional point
in time.
And you can run log-shipping "behind" by some fixed amount of time
(basically do't update unless log is older than X minutes.)
This can save you when someone says, "oops, I just deleted this data, can we
get it back?" As long as they let you know in time, it's fairly trivial.
Then when you have a real disaster just apply the more recent logs, and
voila, you're up to date.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hari,
If I use mirroring or Log shipping, why would one consider storage
replication like EMCs SRDF ?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23jtZWkedHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Hello Hassan,
> Better approach for high availability is create a cluster in local data
> center for the production SQL Server machines and for disaster recovery
> site use Mirroring/Replication or Logshipping.
> To plan the disaster site you should do a capasity planning on data
> growth, network bandwidth...
> If you have enough budget you could take a look into options like EMC's
> SRDF.
> http://www.emc.com/products/networking/srdf.jsp
> Thanks
> Hari
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
>
|||The main difference between SAN mirroring and Database Mirroring/Log
Shipping has to do with which machines are left online at the DR site.
By mirroring the SAN, you can mirror the entire Data Center only need to
keep the remote SAN turned on, no hosts.
In Database Mirroring/Log Shipping, you have to have the remote site fully
operational: Domain Controllers, network, DNS servers, SAN, etc., etc., etc.
And, you have to perform Database Mirroring/Log Shipping on a database per
database configuration. If you have an installation with, say, 50 or more
databases, it is going to be a real pain attempting to mirror or ship logs
for each of them.
If you extrapolate this for the entire Data Center, you will see that
mirroring the disks to remote hosts is going to be more efficient,
administratively at least.
Be careful though. SAN mirroring comes in at least 3 flavors: Synchronous,
Asynchronous, and Snap Copy. Each has its purpose and potential drawbacks.
You need to understand how each works.
Sincerely,
Anthony Thomas

"Hassan" <hassan@.hotmail.com> wrote in message
news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable..
Free
> !!!
> Does anyone see any pros to SAN replication as the strategy compared to
Log
> shipping to have the data to the remote site. We are looking at RTO and
RPO
> of maybe 30 mins of downtime and no loss of 30 mins of data in the event
of
> a disaster.
>
>
|||We will most likely go with Asynchronous. What are drawbacks to that other
than latency ? Would there be integrity issues ? Are there chances that when
you bring the secondary site online, some databases may be corrupt ?
Does it impact the primary site while you setup Storage replication? Does it
impact performance on the primary ?
I am no SAN expert but have heard some SAN horror stories. Care to share ?
We will use the HDS Storage.
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OGX2AF4dHHA.4564@.TK2MSFTNGP03.phx.gbl...
> The main difference between SAN mirroring and Database Mirroring/Log
> Shipping has to do with which machines are left online at the DR site.
> By mirroring the SAN, you can mirror the entire Data Center only need to
> keep the remote SAN turned on, no hosts.
> In Database Mirroring/Log Shipping, you have to have the remote site fully
> operational: Domain Controllers, network, DNS servers, SAN, etc., etc.,
> etc.
> And, you have to perform Database Mirroring/Log Shipping on a database per
> database configuration. If you have an installation with, say, 50 or more
> databases, it is going to be a real pain attempting to mirror or ship logs
> for each of them.
> If you extrapolate this for the entire Data Center, you will see that
> mirroring the disks to remote hosts is going to be more efficient,
> administratively at least.
> Be careful though. SAN mirroring comes in at least 3 flavors:
> Synchronous,
> Asynchronous, and Snap Copy. Each has its purpose and potential
> drawbacks.
> You need to understand how each works.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Free
> Log
> RPO
> of
>
|||"Hassan" wrote:
> Hari,
> If I use mirroring or Log shipping, why would one consider storage
> replication like EMCs SRDF ?
This has to be looked at from the whole data center infrastructure
perspective. If you are talking about a few machines and a few databases on a
single platform, sure use database mirroring or log shipping by all means.
But if you have a large data center with a lot of servers, a lot of
databases, and different platforms, setting up and maintaining database
mirroring or log shipping for each of the databases would be very
inconvenient. So because of the expenses, nobody would go out to buy a SAN
with SRDF just for a few databases. But if you already have a significant SAN
infrastructure in place, a SRDF-like technologies become very appealing to
consider because it is at the storage level and the DBAs are freed to
concentrate on the database issues.
Imagine if you only have a few houses in a place, having individual
generators to provide power probably makes sense. But if the place starts to
get populated, it may make more sense to get a power grid in and have the
power supply managed centrally by professionals.
Linchi
|||"Hassan" wrote:

> Does it impact the primary site while you setup Storage replication? Does it
> impact performance on the primary ?
> I am no SAN expert but have heard some SAN horror stories. Care to share ?
There is a performance penalty with synchronous storage replication just
like there is a performance penalty with full-safety (or synchronous)
database mirroring. I/Os (or transaction records) have to travel across the
distance, and that has inherent delay. There is no free lunch. But the issue
is whether the performance impact is too severe for the app response
requirements. Current state of the art is that most of the apps can live with
the performance penalty. For a specific app, one has to evaluate it
specifically.
This is like buying storage. You don't always go out buying the fastest
storage. You buy least expensive storage that meets your all requirements.
Linchi

> We will use the HDS Storage.
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OGX2AF4dHHA.4564@.TK2MSFTNGP03.phx.gbl...
>
>
|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:FFC68E76-BF64-4C98-9A42-FA5A87A7E959@.microsoft.com...
> "Hassan" wrote:
> This has to be looked at from the whole data center infrastructure
> perspective. If you are talking about a few machines and a few databases
> on a
> single platform, sure use database mirroring or log shipping by all means.
> But if you have a large data center with a lot of servers, a lot of
> databases, and different platforms, setting up and maintaining database
> mirroring or log shipping for each of the databases would be very
> inconvenient. So because of the expenses, nobody would go out to buy a SAN
> with SRDF just for a few databases. But if you already have a significant
> SAN
> infrastructure in place, a SRDF-like technologies become very appealing to
> consider because it is at the storage level and the DBAs are freed to
> concentrate on the database issues.
> Imagine if you only have a few houses in a place, having individual
> generators to provide power probably makes sense. But if the place starts
> to
> get populated, it may make more sense to get a power grid in and have the
> power supply managed centrally by professionals.
Thank you for the excellent analogy.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp class is:
April 30, 2007 - Denver

Planning for Disaster Recovery in second data center..

We are planning on using Database Mirroring internally within the data
center for HA and since its used, I am left now with log shipping or
possibly some SAN replication.
I think either one will work with log shipping being very affordable.. Free
!!!
Does anyone see any pros to SAN replication as the strategy compared to Log
shipping to have the data to the remote site. We are looking at RTO and RPO
of maybe 30 mins of downtime and no loss of 30 mins of data in the event of
a disaster.On 3 Apr, 05:04, "Hassan" <has...@.hotmail.com> wrote:
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable.. Fre
e
> !!!
> Does anyone see any pros to SAN replication as the strategy compared to Lo
g
> shipping to have the data to the remote site. We are looking at RTO and RP
O
> of maybe 30 mins of downtime and no loss of 30 mins of data in the event o
f
> a disaster.
The replication is simpler, in the sense that you have one thing to
monitor, and in theory preferrable. We went for log shipping on the
basis that we were unsure about the burden of the extra data across
the network and some reservations about knowing the state of disk
writes to the data centre. Log shipping gives certainty in the sense
that the database is written to a known transaction at a known point,
even if it may not restore to the last possible moment before failure.
And as you say, it's cheap.|||Hello Hassan,
Better approach for high availability is create a cluster in local data
center for the production SQL Server machines and for disaster recovery site
use Mirroring/Replication or Logshipping.
To plan the disaster site you should do a capasity planning on data growth,
network bandwidth...
If you have enough budget you could take a look into options like EMC's
SRDF.
http://www.emc.com/products/networking/srdf.jsp
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable..
> Free !!!
> Does anyone see any pros to SAN replication as the strategy compared to
> Log shipping to have the data to the remote site. We are looking at RTO
> and RPO of maybe 30 mins of downtime and no loss of 30 mins of data in the
> event of a disaster.
>
>|||"Hassan" <hassan@.hotmail.com> wrote in message
news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable..
> Free !!!
> Does anyone see any pros to SAN replication as the strategy compared to
> Log shipping to have the data to the remote site. We are looking at RTO
> and RPO of maybe 30 mins of downtime and no loss of 30 mins of data in the
> event of a disaster.
>
>
Personally in a case like this, I'd probably go with log-shipping over SAN
replication.
It's cheaper. It's also easier to ensure a consistent transactional point
in time.
And you can run log-shipping "behind" by some fixed amount of time
(basically do't update unless log is older than X minutes.)
This can save you when someone says, "oops, I just deleted this data, can we
get it back?" As long as they let you know in time, it's fairly trivial.
Then when you have a real disaster just apply the more recent logs, and
voila, you're up to date.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hari,
If I use mirroring or Log shipping, why would one consider storage
replication like EMCs SRDF ?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23jtZWkedHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Hello Hassan,
> Better approach for high availability is create a cluster in local data
> center for the production SQL Server machines and for disaster recovery
> site use Mirroring/Replication or Logshipping.
> To plan the disaster site you should do a capasity planning on data
> growth, network bandwidth...
> If you have enough budget you could take a look into options like EMC's
> SRDF.
> http://www.emc.com/products/networking/srdf.jsp
> Thanks
> Hari
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
>|||The main difference between SAN mirroring and Database Mirroring/Log
Shipping has to do with which machines are left online at the DR site.
By mirroring the SAN, you can mirror the entire Data Center only need to
keep the remote SAN turned on, no hosts.
In Database Mirroring/Log Shipping, you have to have the remote site fully
operational: Domain Controllers, network, DNS servers, SAN, etc., etc., etc.
And, you have to perform Database Mirroring/Log Shipping on a database per
database configuration. If you have an installation with, say, 50 or more
databases, it is going to be a real pain attempting to mirror or ship logs
for each of them.
If you extrapolate this for the entire Data Center, you will see that
mirroring the disks to remote hosts is going to be more efficient,
administratively at least.
Be careful though. SAN mirroring comes in at least 3 flavors: Synchronous,
Asynchronous, and Snap Copy. Each has its purpose and potential drawbacks.
You need to understand how each works.
Sincerely,
Anthony Thomas
"Hassan" <hassan@.hotmail.com> wrote in message
news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable..
Free
> !!!
> Does anyone see any pros to SAN replication as the strategy compared to
Log
> shipping to have the data to the remote site. We are looking at RTO and
RPO
> of maybe 30 mins of downtime and no loss of 30 mins of data in the event
of
> a disaster.
>
>|||We will most likely go with Asynchronous. What are drawbacks to that other
than latency ? Would there be integrity issues ? Are there chances that when
you bring the secondary site online, some databases may be corrupt ?
Does it impact the primary site while you setup Storage replication? Does it
impact performance on the primary ?
I am no SAN expert but have heard some SAN horror stories. Care to share ?
We will use the HDS Storage.
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OGX2AF4dHHA.4564@.TK2MSFTNGP03.phx.gbl...
> The main difference between SAN mirroring and Database Mirroring/Log
> Shipping has to do with which machines are left online at the DR site.
> By mirroring the SAN, you can mirror the entire Data Center only need to
> keep the remote SAN turned on, no hosts.
> In Database Mirroring/Log Shipping, you have to have the remote site fully
> operational: Domain Controllers, network, DNS servers, SAN, etc., etc.,
> etc.
> And, you have to perform Database Mirroring/Log Shipping on a database per
> database configuration. If you have an installation with, say, 50 or more
> databases, it is going to be a real pain attempting to mirror or ship logs
> for each of them.
> If you extrapolate this for the entire Data Center, you will see that
> mirroring the disks to remote hosts is going to be more efficient,
> administratively at least.
> Be careful though. SAN mirroring comes in at least 3 flavors:
> Synchronous,
> Asynchronous, and Snap Copy. Each has its purpose and potential
> drawbacks.
> You need to understand how each works.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Free
> Log
> RPO
> of
>|||"Hassan" wrote:
> Hari,
> If I use mirroring or Log shipping, why would one consider storage
> replication like EMCs SRDF ?
This has to be looked at from the whole data center infrastructure
perspective. If you are talking about a few machines and a few databases on
a
single platform, sure use database mirroring or log shipping by all means.
But if you have a large data center with a lot of servers, a lot of
databases, and different platforms, setting up and maintaining database
mirroring or log shipping for each of the databases would be very
inconvenient. So because of the expenses, nobody would go out to buy a SAN
with SRDF just for a few databases. But if you already have a significant SA
N
infrastructure in place, a SRDF-like technologies become very appealing to
consider because it is at the storage level and the DBAs are freed to
concentrate on the database issues.
Imagine if you only have a few houses in a place, having individual
generators to provide power probably makes sense. But if the place starts to
get populated, it may make more sense to get a power grid in and have the
power supply managed centrally by professionals.
Linchi|||"Hassan" wrote:

> Does it impact the primary site while you setup Storage replication? Does
it
> impact performance on the primary ?
> I am no SAN expert but have heard some SAN horror stories. Care to share ?
There is a performance penalty with synchronous storage replication just
like there is a performance penalty with full-safety (or synchronous)
database mirroring. I/Os (or transaction records) have to travel across the
distance, and that has inherent delay. There is no free lunch. But the issue
is whether the performance impact is too severe for the app response
requirements. Current state of the art is that most of the apps can live wit
h
the performance penalty. For a specific app, one has to evaluate it
specifically.
This is like buying storage. You don't always go out buying the fastest
storage. You buy least expensive storage that meets your all requirements.
Linchi

> We will use the HDS Storage.
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OGX2AF4dHHA.4564@.TK2MSFTNGP03.phx.gbl...
>
>|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:FFC68E76-BF64-4C98-9A42-FA5A87A7E959@.microsoft.com...
> "Hassan" wrote:
> This has to be looked at from the whole data center infrastructure
> perspective. If you are talking about a few machines and a few databases
> on a
> single platform, sure use database mirroring or log shipping by all means.
> But if you have a large data center with a lot of servers, a lot of
> databases, and different platforms, setting up and maintaining database
> mirroring or log shipping for each of the databases would be very
> inconvenient. So because of the expenses, nobody would go out to buy a SAN
> with SRDF just for a few databases. But if you already have a significant
> SAN
> infrastructure in place, a SRDF-like technologies become very appealing to
> consider because it is at the storage level and the DBAs are freed to
> concentrate on the database issues.
> Imagine if you only have a few houses in a place, having individual
> generators to provide power probably makes sense. But if the place starts
> to
> get populated, it may make more sense to get a power grid in and have the
> power supply managed centrally by professionals.
Thank you for the excellent analogy.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp class is:
April 30, 2007 - Denver

Planning for Disaster Recovery in second data center..

We are planning on using Database Mirroring internally within the data
center for HA and since its used, I am left now with log shipping or
possibly some SAN replication.
I think either one will work with log shipping being very affordable.. Free
!!!
Does anyone see any pros to SAN replication as the strategy compared to Log
shipping to have the data to the remote site. We are looking at RTO and RPO
of maybe 30 mins of downtime and no loss of 30 mins of data in the event of
a disaster.On 3 Apr, 05:04, "Hassan" <has...@.hotmail.com> wrote:
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable.. Free
> !!!
> Does anyone see any pros to SAN replication as the strategy compared to Log
> shipping to have the data to the remote site. We are looking at RTO and RPO
> of maybe 30 mins of downtime and no loss of 30 mins of data in the event of
> a disaster.
The replication is simpler, in the sense that you have one thing to
monitor, and in theory preferrable. We went for log shipping on the
basis that we were unsure about the burden of the extra data across
the network and some reservations about knowing the state of disk
writes to the data centre. Log shipping gives certainty in the sense
that the database is written to a known transaction at a known point,
even if it may not restore to the last possible moment before failure.
And as you say, it's cheap.|||Hello Hassan,
Better approach for high availability is create a cluster in local data
center for the production SQL Server machines and for disaster recovery site
use Mirroring/Replication or Logshipping.
To plan the disaster site you should do a capasity planning on data growth,
network bandwidth...
If you have enough budget you could take a look into options like EMC's
SRDF.
http://www.emc.com/products/networking/srdf.jsp
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable..
> Free !!!
> Does anyone see any pros to SAN replication as the strategy compared to
> Log shipping to have the data to the remote site. We are looking at RTO
> and RPO of maybe 30 mins of downtime and no loss of 30 mins of data in the
> event of a disaster.
>
>|||"Hassan" <hassan@.hotmail.com> wrote in message
news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable..
> Free !!!
> Does anyone see any pros to SAN replication as the strategy compared to
> Log shipping to have the data to the remote site. We are looking at RTO
> and RPO of maybe 30 mins of downtime and no loss of 30 mins of data in the
> event of a disaster.
>
>
Personally in a case like this, I'd probably go with log-shipping over SAN
replication.
It's cheaper. It's also easier to ensure a consistent transactional point
in time.
And you can run log-shipping "behind" by some fixed amount of time
(basically do't update unless log is older than X minutes.)
This can save you when someone says, "oops, I just deleted this data, can we
get it back?" As long as they let you know in time, it's fairly trivial.
Then when you have a real disaster just apply the more recent logs, and
voila, you're up to date.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hari,
If I use mirroring or Log shipping, why would one consider storage
replication like EMCs SRDF ?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23jtZWkedHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Hello Hassan,
> Better approach for high availability is create a cluster in local data
> center for the production SQL Server machines and for disaster recovery
> site use Mirroring/Replication or Logshipping.
> To plan the disaster site you should do a capasity planning on data
> growth, network bandwidth...
> If you have enough budget you could take a look into options like EMC's
> SRDF.
> http://www.emc.com/products/networking/srdf.jsp
> Thanks
> Hari
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
>> We are planning on using Database Mirroring internally within the data
>> center for HA and since its used, I am left now with log shipping or
>> possibly some SAN replication.
>> I think either one will work with log shipping being very affordable..
>> Free !!!
>> Does anyone see any pros to SAN replication as the strategy compared to
>> Log shipping to have the data to the remote site. We are looking at RTO
>> and RPO of maybe 30 mins of downtime and no loss of 30 mins of data in
>> the event of a disaster.
>>
>|||The main difference between SAN mirroring and Database Mirroring/Log
Shipping has to do with which machines are left online at the DR site.
By mirroring the SAN, you can mirror the entire Data Center only need to
keep the remote SAN turned on, no hosts.
In Database Mirroring/Log Shipping, you have to have the remote site fully
operational: Domain Controllers, network, DNS servers, SAN, etc., etc., etc.
And, you have to perform Database Mirroring/Log Shipping on a database per
database configuration. If you have an installation with, say, 50 or more
databases, it is going to be a real pain attempting to mirror or ship logs
for each of them.
If you extrapolate this for the entire Data Center, you will see that
mirroring the disks to remote hosts is going to be more efficient,
administratively at least.
Be careful though. SAN mirroring comes in at least 3 flavors: Synchronous,
Asynchronous, and Snap Copy. Each has its purpose and potential drawbacks.
You need to understand how each works.
Sincerely,
Anthony Thomas
"Hassan" <hassan@.hotmail.com> wrote in message
news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> We are planning on using Database Mirroring internally within the data
> center for HA and since its used, I am left now with log shipping or
> possibly some SAN replication.
> I think either one will work with log shipping being very affordable..
Free
> !!!
> Does anyone see any pros to SAN replication as the strategy compared to
Log
> shipping to have the data to the remote site. We are looking at RTO and
RPO
> of maybe 30 mins of downtime and no loss of 30 mins of data in the event
of
> a disaster.
>
>|||We will most likely go with Asynchronous. What are drawbacks to that other
than latency ? Would there be integrity issues ? Are there chances that when
you bring the secondary site online, some databases may be corrupt ?
Does it impact the primary site while you setup Storage replication? Does it
impact performance on the primary ?
I am no SAN expert but have heard some SAN horror stories. Care to share ?
We will use the HDS Storage.
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OGX2AF4dHHA.4564@.TK2MSFTNGP03.phx.gbl...
> The main difference between SAN mirroring and Database Mirroring/Log
> Shipping has to do with which machines are left online at the DR site.
> By mirroring the SAN, you can mirror the entire Data Center only need to
> keep the remote SAN turned on, no hosts.
> In Database Mirroring/Log Shipping, you have to have the remote site fully
> operational: Domain Controllers, network, DNS servers, SAN, etc., etc.,
> etc.
> And, you have to perform Database Mirroring/Log Shipping on a database per
> database configuration. If you have an installation with, say, 50 or more
> databases, it is going to be a real pain attempting to mirror or ship logs
> for each of them.
> If you extrapolate this for the entire Data Center, you will see that
> mirroring the disks to remote hosts is going to be more efficient,
> administratively at least.
> Be careful though. SAN mirroring comes in at least 3 flavors:
> Synchronous,
> Asynchronous, and Snap Copy. Each has its purpose and potential
> drawbacks.
> You need to understand how each works.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
>> We are planning on using Database Mirroring internally within the data
>> center for HA and since its used, I am left now with log shipping or
>> possibly some SAN replication.
>> I think either one will work with log shipping being very affordable..
> Free
>> !!!
>> Does anyone see any pros to SAN replication as the strategy compared to
> Log
>> shipping to have the data to the remote site. We are looking at RTO and
> RPO
>> of maybe 30 mins of downtime and no loss of 30 mins of data in the event
> of
>> a disaster.
>>
>|||"Hassan" wrote:
> Hari,
> If I use mirroring or Log shipping, why would one consider storage
> replication like EMCs SRDF ?
This has to be looked at from the whole data center infrastructure
perspective. If you are talking about a few machines and a few databases on a
single platform, sure use database mirroring or log shipping by all means.
But if you have a large data center with a lot of servers, a lot of
databases, and different platforms, setting up and maintaining database
mirroring or log shipping for each of the databases would be very
inconvenient. So because of the expenses, nobody would go out to buy a SAN
with SRDF just for a few databases. But if you already have a significant SAN
infrastructure in place, a SRDF-like technologies become very appealing to
consider because it is at the storage level and the DBAs are freed to
concentrate on the database issues.
Imagine if you only have a few houses in a place, having individual
generators to provide power probably makes sense. But if the place starts to
get populated, it may make more sense to get a power grid in and have the
power supply managed centrally by professionals.
Linchi|||"Hassan" wrote:
> Does it impact the primary site while you setup Storage replication? Does it
> impact performance on the primary ?
> I am no SAN expert but have heard some SAN horror stories. Care to share ?
There is a performance penalty with synchronous storage replication just
like there is a performance penalty with full-safety (or synchronous)
database mirroring. I/Os (or transaction records) have to travel across the
distance, and that has inherent delay. There is no free lunch. But the issue
is whether the performance impact is too severe for the app response
requirements. Current state of the art is that most of the apps can live with
the performance penalty. For a specific app, one has to evaluate it
specifically.
This is like buying storage. You don't always go out buying the fastest
storage. You buy least expensive storage that meets your all requirements.
Linchi
> We will use the HDS Storage.
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OGX2AF4dHHA.4564@.TK2MSFTNGP03.phx.gbl...
> > The main difference between SAN mirroring and Database Mirroring/Log
> > Shipping has to do with which machines are left online at the DR site.
> >
> > By mirroring the SAN, you can mirror the entire Data Center only need to
> > keep the remote SAN turned on, no hosts.
> >
> > In Database Mirroring/Log Shipping, you have to have the remote site fully
> > operational: Domain Controllers, network, DNS servers, SAN, etc., etc.,
> > etc.
> > And, you have to perform Database Mirroring/Log Shipping on a database per
> > database configuration. If you have an installation with, say, 50 or more
> > databases, it is going to be a real pain attempting to mirror or ship logs
> > for each of them.
> >
> > If you extrapolate this for the entire Data Center, you will see that
> > mirroring the disks to remote hosts is going to be more efficient,
> > administratively at least.
> >
> > Be careful though. SAN mirroring comes in at least 3 flavors:
> > Synchronous,
> > Asynchronous, and Snap Copy. Each has its purpose and potential
> > drawbacks.
> > You need to understand how each works.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Hassan" <hassan@.hotmail.com> wrote in message
> > news:OHXNjUadHHA.2332@.TK2MSFTNGP04.phx.gbl...
> >> We are planning on using Database Mirroring internally within the data
> >> center for HA and since its used, I am left now with log shipping or
> >> possibly some SAN replication.
> >>
> >> I think either one will work with log shipping being very affordable..
> > Free
> >> !!!
> >>
> >> Does anyone see any pros to SAN replication as the strategy compared to
> > Log
> >> shipping to have the data to the remote site. We are looking at RTO and
> > RPO
> >> of maybe 30 mins of downtime and no loss of 30 mins of data in the event
> > of
> >> a disaster.
> >>
> >>
> >>
> >
> >
>
>|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:FFC68E76-BF64-4C98-9A42-FA5A87A7E959@.microsoft.com...
> "Hassan" wrote:
>> Hari,
>> If I use mirroring or Log shipping, why would one consider storage
>> replication like EMCs SRDF ?
> This has to be looked at from the whole data center infrastructure
> perspective. If you are talking about a few machines and a few databases
> on a
> single platform, sure use database mirroring or log shipping by all means.
> But if you have a large data center with a lot of servers, a lot of
> databases, and different platforms, setting up and maintaining database
> mirroring or log shipping for each of the databases would be very
> inconvenient. So because of the expenses, nobody would go out to buy a SAN
> with SRDF just for a few databases. But if you already have a significant
> SAN
> infrastructure in place, a SRDF-like technologies become very appealing to
> consider because it is at the storage level and the DBAs are freed to
> concentrate on the database issues.
> Imagine if you only have a few houses in a place, having individual
> generators to provide power probably makes sense. But if the place starts
> to
> get populated, it may make more sense to get a power grid in and have the
> power supply managed centrally by professionals.
Thank you for the excellent analogy.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp class is:
April 30, 2007 - Denver

Friday, March 30, 2012

Please help me

Wednesday, March 28, 2012

Please help (Sql server 2000) Critical Error

Monday, March 26, 2012

Please help - Transaction Log not shrinking!

Please help - Transaction Log not shrinking!

PLEASE help - moving transaction log from one disk to another

Friday, March 23, 2012

Please evaluate this approach to shrinking log files

Please create news group for Log Shipping

Please create news group for Log Shipping

Please create news group for Log Shipping

Tuesday, March 20, 2012

Planning of log backup suggestions

Planning of log backup suggestions

Planning of log backup suggestions

Planning for Disaster Recovery in second data center..

Planning for Disaster Recovery in second data center..

Planning for Disaster Recovery in second data center..

Planning for Disaster Recovery in second data center..

pivot information

Blog Archive

About Me