SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An easy way to synchronize data


An easy way to synchronize data

Author
Message
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3136 Visits: 4793
You existing backup process should already be automated.

Assuming this is the case: determine where those backups are going; determine whether the test server has access to the backup files; determine the frequency and naming convention of the backup files.

Then you can create a SQL Agent job which will contain a RESTORE command referencing the backup file you want to restore to the test server.
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1970 Visits: 1266
Of course you can automate that.
Make a sqlcmd script that
1) finds last full backup name and diff backup name.
2) restore them to test.

1) To find a backup name you can by querying msdb.dbo.backupset table on the production server,
or using dir command like this:

!!dir *.bak /o-d /b

or xp_cmdshell with the same command.

2) Once you have filenames, restore is almost trivial:
:CONNECT testserver\instancename

alter database XY set single_user with rollback after 2
GO

RESTORE DATABASE XY FROM DISK='$(bak_filename)'
WITH
MOVE('logicaldatafilename' TO 'new data path and file name'),
MOVE('logicallogfilename' TO 'new log path and file name'),
REPLACE,
NORECOVERY
GO
RESTORE DATABASE XY FROM DISK='$(dif_filename)'
WITH RECOVERY
GO

Verify that script work fine in sqlcmd mode in SQL Management Studio (Query->SQLCMD Mode).
After that, create a sql agent job with step type "CmdExec" and put sqlcmd.exe command to execute the script you just created.
Of course, you have to take care about the rights of sql agent account (or proxy account if you set one for that step) to be able to connect/read/write where it needs.

If you need to copy files, use "!!ROBOCOPY sourcedir destinationdir filename1 filename2 filename3 /MT:2"

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

imani_technology
imani_technology
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1726 Visits: 787
So which is the better approach, scripted restore or mirrored database? Which has better performance, when is easier to maintain?
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1970 Visits: 1266
Mirror affects primary (production) server in many aspects. One of them is - your production transaction log will grow if restoring stream to mirror slows down. You do not want to pair a production with a test in such tight way as mirror is, jeopardizing availability. Mirror should be specially monitored. Log shipping would be better. But simple, automated restore will do.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

imani_technology
imani_technology
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1726 Visits: 787
Here's a problem. The prod server backs up the file to a location that the test server can't access. I don't have authority to change that location.
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3136 Visits: 4793
Can you you arrange to copy the file to a location the test server can access? You could add this to the backup job or use a separate process.
imani_technology
imani_technology
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1726 Visits: 787
I got mirroring to work in the testing environment. Now how can I write queries against it?
SQLSalas
SQLSalas
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 594
Here is a link related to Creating a database snapshot.
http://msdn.microsoft.com/en-us/library/ms175876(v=sql.105).aspx

You can go ahead a create a snapshot based on the Mirrored Database that you have (in Synchronized state). There are other things to consider in a snapshot, like how sparse files are used, the space usage as presented on the explorer vs. the actual disk usage.

basic syntax:
CREATE DATABASE <DatabaseSnapshotName>
(NAME = '<Logical Data File Name>', FILENAME = '<Valid Snapshot Full path and filename>')
AS SNAPSHOT OF <Databasename>;

For a Database called DB1:
CREATE DATABASE DB1_SS1
(NAME = 'DB1', FILENAME = 'E:\MSSQL\Snapshot\DB1_Snapshot.SS1')
AS SNAPSHOT OF DB1;

And then you can go ahead and issue :
USE DB1_SS1
GO

SELECT ..... your queries...
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1970 Visits: 1266
Snapshot is read-only. If you want to test something other than reads, it will not work.
Snapshot might not be good to test even the reads because it is physically different than original database. The query you are trying to test will partially read the data files of snapshot database and partially mirror database.

Restored test db is read-write, and is exact copy of production. It doesn't change production server, as mirror does. Mirror prevents certain operations on your production system. For example, you cannot do maintenance anymore in bulk_logged recovery model on your production. Synchronized mirror will slow down your production server - it will wait until the transaction log records are written to the transaction log of the mirror. And what will happen if, accidentally, you fail-over to the mirror db which is on a test system, and your production becomes not read-only but unreadable?

Mirror is a great HA feature, don't get me wrong, but it is a completely wrong tool for the purpose you need. I can't believe that I'm the only one here telling you this.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7710 Visits: 6045
Vedran Kesegic (1/28/2013)
Snapshot is read-only. If you want to test something other than reads, it will not work.
Snapshot might not be good to test even the reads because it is physically different than original database. The query you are trying to test will partially read the data files of snapshot database and partially mirror database.

Restored test db is read-write, and is exact copy of production. It doesn't change production server, as mirror does. Mirror prevents certain operations on your production system. For example, you cannot do maintenance anymore in bulk_logged recovery model on your production. Synchronized mirror will slow down your production server - it will wait until the transaction log records are written to the transaction log of the mirror. And what will happen if, accidentally, you fail-over to the mirror db which is on a test system, and your production becomes not read-only but unreadable?

Mirror is a great HA feature, don't get me wrong, but it is a completely wrong tool for the purpose you need. I can't believe that I'm the only one here telling you this.


I would have said all that if I'd been back sooner :-)
I agree with Vedran that mirroring really isn't what you need in this situation.

Cheers
Gaz
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search