DR Test Scenario

Hello All

We have Asynchronous DB Mirroring established for our Production database

which runs on SQL Server 2005.

For the DR Test we plan to do the following during the period of low activity :

1. Pause the mirroring on the Principal Server.

2. Break the mirror on the Mirror Server

3. Take on offline backup on the Mirror.

4. Bring the Mirror Database up.

5. Run the DR Tests on the Mirrored Database

6. Restore the Offline Backup taken in step 3.

7. Reestablish the mirror from the Principal to Mirror.

8. Resume Mirroring on the Principal Server.

9. Verify Mirroring is happening on the Mirror Server

Can u please let me know if this plan is feasible and if there should be any modifications to the plan that are required. Any other suggestions/input is appreciated.

TIA

Best,

Jay

Hi Jay,

You do need to pause and break the mirror on two different places to start with. Also I don't think your plan is feasible.

If you want to test the Mirror. DO the following:

1. Perform a backup of Principal database.

2. Break the mirror. (if you don't intend to keep changes from DR test)

3. Bring the mirror online.

4. Do your tests on the mirror.

5. Once finished testing, restore the database backup , taken in Step 1, to the as mirror db on mirror server.

6. Apply any logs if any necessary.

7. reconfigure mirroring.

Jag

DR strategies

We have a large database for production and need to come up with a solution
for Disaster Recovery.
Performing tasks such as a full backup and transaction backups, then
shipping over across country is okay during initial setup. But long term it
will not be possible to ship a full backup across. Transactional backups
shipping and restoring to a read-only instance would be okay; but, occasiona
l
full backups of the production system would be needed for development teams.
So this would cause a problem for the DR system.
What are the different options either via a Microsoft solution or a 3rd part
y?Tom,
For MS solutions you might want to have a look at
http://support.microsoft.com/defaul...b;en-us;822400.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:9D54441D-E203-4544-8F37-6EDAFE7DCC79@.microsoft.com...
> We have a large database for production and need to come up with a
solution
> for Disaster Recovery.
> Performing tasks such as a full backup and transaction backups, then
> shipping over across country is okay during initial setup. But long term
it
> will not be possible to ship a full backup across. Transactional backups
> shipping and restoring to a read-only instance would be okay; but,
occasional
> full backups of the production system would be needed for development
teams.
> So this would cause a problem for the DR system.
> What are the different options either via a Microsoft solution or a 3rd
party?sql

DR strategies

We have a large database for production and need to come up with a solution
for Disaster Recovery.
Performing tasks such as a full backup and transaction backups, then
shipping over across country is okay during initial setup. But long term it
will not be possible to ship a full backup across. Transactional backups
shipping and restoring to a read-only instance would be okay; but, occasional
full backups of the production system would be needed for development teams.
So this would cause a problem for the DR system.
What are the different options either via a Microsoft solution or a 3rd party?Tom,
For MS solutions you might want to have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;822400.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:9D54441D-E203-4544-8F37-6EDAFE7DCC79@.microsoft.com...
> We have a large database for production and need to come up with a
solution
> for Disaster Recovery.
> Performing tasks such as a full backup and transaction backups, then
> shipping over across country is okay during initial setup. But long term
it
> will not be possible to ship a full backup across. Transactional backups
> shipping and restoring to a read-only instance would be okay; but,
occasional
> full backups of the production system would be needed for development
teams.
> So this would cause a problem for the DR system.
> What are the different options either via a Microsoft solution or a 3rd
party?

DR strategies

We have a large database for production and need to come up with a solution
for Disaster Recovery.
Performing tasks such as a full backup and transaction backups, then
shipping over across country is okay during initial setup. But long term it
will not be possible to ship a full backup across. Transactional backups
shipping and restoring to a read-only instance would be okay; but, occasional
full backups of the production system would be needed for development teams.
So this would cause a problem for the DR system.
What are the different options either via a Microsoft solution or a 3rd party?
Tom,
For MS solutions you might want to have a look at
http://support.microsoft.com/default...;en-us;822400.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:9D54441D-E203-4544-8F37-6EDAFE7DCC79@.microsoft.com...
> We have a large database for production and need to come up with a
solution
> for Disaster Recovery.
> Performing tasks such as a full backup and transaction backups, then
> shipping over across country is okay during initial setup. But long term
it
> will not be possible to ship a full backup across. Transactional backups
> shipping and restoring to a read-only instance would be okay; but,
occasional
> full backups of the production system would be needed for development
teams.
> So this would cause a problem for the DR system.
> What are the different options either via a Microsoft solution or a 3rd
party?

DR Server using replication

Hi,

I am planning on setting up disaster recovery configuration. It is planned to use transactional replication from the production server to the DR server. Are there any recommendations for replicating changes back to the production server, once it is back online. I suppose that it would be safest to move the database back the other server over a weekend or during downtime. But I am just wondering whether anyone has experience of other practical solutions.

Many thanks
DavidDid you look into log shipping?|||Replication is a poor choice for Disaster Recovery. The cost of maintaining the replication is much higher than using Log Shipping or a home grown solution similar to Log Shipping.

The first questions that need to be answered by the owner of the budget are:
1. How much data can you afford to lose?
2. How much (business hours) downtime can be tolerated?
3. How much money are you willing to spend on Disaster Recovery?

You gotta put numbers on the cost/benefit or you'll be rolling boulders uphill for a long, long time.

Unfortunately, typical, thoughtless, knee-jerk answers are:
1. none
2. 1 minute
3. $9.95

A better approach may be to put together a menu of options to go along w/ the questions above as well as other relevant questions:
A1. Lose no more than 5 minutes of transactions, recover within 20 minutes at a cost of $40K + $10K per subsequent years.
B2. Lose no more than 60 minutes of transactions, recover within 4 hours at a cost of $20K + $5K per subsequent years.
C3. Lose no more than one day of data and recover within 4 hours at a cost of $1K + $1K per subsequent years.

Ninety percent of failures are caused by humans - e.g. accidentally Delete w/o a Where clause. Those are the types of failures from which you need to focus on recovering. For example, your DR/backup server should intentionally be kept out of sync. by however much time is needed to detect and respond to a human error: 4 hours works well.

Finally, unless you are willing to determine every failure permutation and the steps needed to recover from each, the answer to your first question is: stop production, figure out what is missing and copy the data using the most convenient, familiar approach you have.|||Sybase has a replication server product that does exactly what you need. It can replicate many database vendor's data including MSSQL.

http://www.sybase.com/products/informationmanagement/replicationserver|||Thank you for the responses.

Unfortunately the client insists that they cannot afford any downtime, which is why they are going down this route. I had asked them to look at third party tools such as emc, but they decided that this wouldn't be possible for political reasons. Log shipping doesn't suit application either, so we are left with doing transactional rep on all the data.

Thanks|||Why can't companies understand DR is not the same is High Availability? Too bad you can't cluster your production server and use log shipping to a DR solution.

I'm not very familiar with replication but if you need to get changes back to the original production server shouldn't you use merge replication?|||Thank you for the responses.

Unfortunately the client insists that they cannot afford any downtime, which is why they are going down this route. I had asked them to look at third party tools such as emc, but they decided that this wouldn't be possible for political reasons. Log shipping doesn't suit application either, so we are left with doing transactional rep on all the data.

Thanks

Did you look at the Sybase solution? What you are really looking at is Warm Standby, which replicates (transactionally) all of the changes from the "active" connection to the warm standby connection. The repserver is aware which connection is the "active" and which is the "warm standby" at all times. When you switch them, replication flows the other way automatically.

downgrade from sql 2k enterprise edition to standard

Hello All,
I need to downgrade my production server from enterprise to standard edition
because of cost.
Has anyone been through this scenario?
My plan is to backup all dbs, leave data folders intact (I have db files in
4 drives), uninstall enterprise, install standard and reattach db files.
My questions are:
I have tons of jobs and DTS packages on the server. Can I simply restore
the msdb from enterprise or is it going to have problem with standard
edition?
Does anyone have a script to save logins and restore logins?
Thanks for any help,
sqlgirl"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:407a9a90$0$40211$39cecf19@.news.twtelecom.net...
> Hello All,
> I need to downgrade my production server from enterprise to standard
edition
> because of cost.
> Has anyone been through this scenario?
> My plan is to backup all dbs, leave data folders intact (I have db files
in
> 4 drives), uninstall enterprise, install standard and reattach db files.
> My questions are:
> I have tons of jobs and DTS packages on the server. Can I simply restore
> the msdb from enterprise or is it going to have problem with standard
> edition?
> Does anyone have a script to save logins and restore logins?
The following links should help you in the restore and account migration
process:
http://support.microsoft.com/defaul...kb;en-us;246133
http://support.microsoft.com/defaul...2&Product=sql2k
With regard to your question on MSDB restore, I'm less certain if that will
work... If the SQL Server builds are the same I think you'll be OK, if not,
please see the following link:
http://support.microsoft.com/defaul...4&Product=sql2k
Also, you may want to consider running SQL backups on all of your databases,
prior to uninstalling SQL Server. This will give you one more avenue for the
restore process.
Steve|||Hi Biva,
I am reviewing your post and our MVP provide you some good links for you
question, I want to know if you still have any question on this issue. For
any question, please feel free to post message here and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

downgrade from sql 2k enterprise edition to standard

Hello All,
I need to downgrade my production server from enterprise to standard edition
because of cost.
Has anyone been through this scenario?
My plan is to backup all dbs, leave data folders intact (I have db files in
4 drives), uninstall enterprise, install standard and reattach db files.
My questions are:
I have tons of jobs and DTS packages on the server. Can I simply restore
the msdb from enterprise or is it going to have problem with standard
edition?
Does anyone have a script to save logins and restore logins?
Thanks for any help,
sqlgirl
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:407a9a90$0$40211$39cecf19@.news.twtelecom.net. ..
> Hello All,
> I need to downgrade my production server from enterprise to standard
edition
> because of cost.
> Has anyone been through this scenario?
> My plan is to backup all dbs, leave data folders intact (I have db files
in
> 4 drives), uninstall enterprise, install standard and reattach db files.
> My questions are:
> I have tons of jobs and DTS packages on the server. Can I simply restore
> the msdb from enterprise or is it going to have problem with standard
> edition?
> Does anyone have a script to save logins and restore logins?
The following links should help you in the restore and account migration
process:
http://support.microsoft.com/default...b;en-us;246133
http://support.microsoft.com/default...&Product=sql2k
With regard to your question on MSDB restore, I'm less certain if that will
work... If the SQL Server builds are the same I think you'll be OK, if not,
please see the following link:
http://support.microsoft.com/default...&Product=sql2k
Also, you may want to consider running SQL backups on all of your databases,
prior to uninstalling SQL Server. This will give you one more avenue for the
restore process.
Steve
|||Hi Biva,
I am reviewing your post and our MVP provide you some good links for you
question, I want to know if you still have any question on this issue. For
any question, please feel free to post message here and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

downgrade from sql 2k enterprise edition to standard

Hello All,
I need to downgrade my production server from enterprise to standard edition
because of cost.
Has anyone been through this scenario?
My plan is to backup all dbs, leave data folders intact (I have db files in
4 drives), uninstall enterprise, install standard and reattach db files.
My questions are:
I have tons of jobs and DTS packages on the server. Can I simply restore
the msdb from enterprise or is it going to have problem with standard
edition?
Does anyone have a script to save logins and restore logins?
Thanks for any help,
sqlgirl"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:407a9a90$0$40211$39cecf19@.news.twtelecom.net...
> Hello All,
> I need to downgrade my production server from enterprise to standard
edition
> because of cost.
> Has anyone been through this scenario?
> My plan is to backup all dbs, leave data folders intact (I have db files
in
> 4 drives), uninstall enterprise, install standard and reattach db files.
> My questions are:
> I have tons of jobs and DTS packages on the server. Can I simply restore
> the msdb from enterprise or is it going to have problem with standard
> edition?
> Does anyone have a script to save logins and restore logins?
The following links should help you in the restore and account migration
process:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
http://support.microsoft.com/default.aspx?scid=kb;en-us;240872&Product=sql2k
With regard to your question on MSDB restore, I'm less certain if that will
work... If the SQL Server builds are the same I think you'll be OK, if not,
please see the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264474&Product=sql2k
Also, you may want to consider running SQL backups on all of your databases,
prior to uninstalling SQL Server. This will give you one more avenue for the
restore process.
Steve|||Hi Biva,
I am reviewing your post and our MVP provide you some good links for you
question, I want to know if you still have any question on this issue. For
any question, please feel free to post message here and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql

Down time from shrink?

Hello, everyone:

I have a production database in which data file takes 17GB and used 10GB. I want to shrink it. Once I take about one hour to shrink 1GB. So I am worry long shrinking time. Is there down time during the shrinking that doesn't allow user access?

Thanks

ZYThow big is(are) your log file(s)?

dbcc shrinkdatabase does not require the database to be in single user mode but you may want to schedule some down time as you are likely to see a performance hit on the server and you are going to want reindex after the shrink because of the frgamentation shrinking causes.|||Hi, Sean:

Thanks for the reply. The log file is not big, just be about 100MB. Only data files are too.

ZYT

how big is(are) your log file(s)?

dbcc shrinkdatabase does not require the database to be in single user mode but you may want to schedule some down time as you are likely to see a performance hit on the server and you are going to want reindex after the shrink because of the frgamentation shrinking causes.|||Are you shrinking the database on a regular interval? If you do: Don't!
If you are running out of disk space: add more disks.|||17GB is tiny for a database. After your shrink, I would review your automatic growth if you are worried about too much free space.

Doing a diff on 2 identical databases?

I have a production database that has log shipping enabled to a remote
server. It has been about a year now, and i wanted to know if there was a
tool or command i can use to do a diff to assure that both databases are in
fact truly in sync. Due to the application utilizing the database, it is
running in Bulk-logged mode and not FULL. Any help is greatly appreciated.You could use DataCompare from Redgate or AdeptSQL Diff - both do
essentially the same.
HTH,
Paul Ibison|||On Thu, 19 Jul 2007 22:55:26 +0100, Paul Ibison wrote:

> You could use DataCompare from Redgate or AdeptSQL Diff - both do
> essentially the same.
> HTH,
> Paul Ibison
Also look into a free tool from Quest: ComparisonSuite for SQLServer I use
it and it works really well.
-Chuck|||ApexSQL Diff is very good.
TheSQLGuru
President
Indicium Resources, Inc.
"FS" <FS@.discussions.microsoft.com> wrote in message
news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>I have a production database that has log shipping enabled to a remote
> server. It has been about a year now, and i wanted to know if there was a
> tool or command i can use to do a diff to assure that both databases are
> in
> fact truly in sync. Due to the application utilizing the database, it is
> running in Bulk-logged mode and not FULL. Any help is greatly appreciated.|||Thank you all for your replies.
"TheSQLGuru" wrote:

> ApexSQL Diff is very good.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>
>

Doing a diff on 2 identical databases?

I have a production database that has log shipping enabled to a remote
server. It has been about a year now, and i wanted to know if there was a
tool or command i can use to do a diff to assure that both databases are in
fact truly in sync. Due to the application utilizing the database, it is
running in Bulk-logged mode and not FULL. Any help is greatly appreciated.
On Thu, 19 Jul 2007 22:55:26 +0100, Paul Ibison wrote:

> You could use DataCompare from Redgate or AdeptSQL Diff - both do
> essentially the same.
> HTH,
> Paul Ibison
Also look into a free tool from Quest: ComparisonSuite for SQLServer I use
it and it works really well.
-Chuck
|||ApexSQL Diff is very good.
TheSQLGuru
President
Indicium Resources, Inc.
"FS" <FS@.discussions.microsoft.com> wrote in message
news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>I have a production database that has log shipping enabled to a remote
> server. It has been about a year now, and i wanted to know if there was a
> tool or command i can use to do a diff to assure that both databases are
> in
> fact truly in sync. Due to the application utilizing the database, it is
> running in Bulk-logged mode and not FULL. Any help is greatly appreciated.
|||Thank you all for your replies.
"TheSQLGuru" wrote:

> ApexSQL Diff is very good.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>
>

Doing a diff on 2 identical databases?

I have a production database that has log shipping enabled to a remote
server. It has been about a year now, and i wanted to know if there was a
tool or command i can use to do a diff to assure that both databases are in
fact truly in sync. Due to the application utilizing the database, it is
running in Bulk-logged mode and not FULL. Any help is greatly appreciated.You could use DataCompare from Redgate or AdeptSQL Diff - both do
essentially the same.
HTH,
Paul Ibison|||On Thu, 19 Jul 2007 22:55:26 +0100, Paul Ibison wrote:
> You could use DataCompare from Redgate or AdeptSQL Diff - both do
> essentially the same.
> HTH,
> Paul Ibison
Also look into a free tool from Quest: ComparisonSuite for SQLServer I use
it and it works really well.
-Chuck|||ApexSQL Diff is very good.
--
TheSQLGuru
President
Indicium Resources, Inc.
"FS" <FS@.discussions.microsoft.com> wrote in message
news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
>I have a production database that has log shipping enabled to a remote
> server. It has been about a year now, and i wanted to know if there was a
> tool or command i can use to do a diff to assure that both databases are
> in
> fact truly in sync. Due to the application utilizing the database, it is
> running in Bulk-logged mode and not FULL. Any help is greatly appreciated.|||Thank you all for your replies.
"TheSQLGuru" wrote:
> ApexSQL Diff is very good.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:A9351434-E932-4F55-837D-B3ED75B44BAF@.microsoft.com...
> >I have a production database that has log shipping enabled to a remote
> > server. It has been about a year now, and i wanted to know if there was a
> > tool or command i can use to do a diff to assure that both databases are
> > in
> > fact truly in sync. Due to the application utilizing the database, it is
> > running in Bulk-logged mode and not FULL. Any help is greatly appreciated.
>
>