Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

An easy way to synchronize data Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 12:09 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 653, Visits: 3,909
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.
Post #1412595
Posted Monday, January 28, 2013 2:54 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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
Post #1412670
Posted Monday, January 28, 2013 3:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
So which is the better approach, scripted restore or mirrored database? Which has better performance, when is easier to maintain?
Post #1412684
Posted Monday, January 28, 2013 3:49 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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
Post #1412686
Posted Monday, January 28, 2013 4:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
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.
Post #1412689
Posted Monday, January 28, 2013 4:17 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 653, Visits: 3,909
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.
Post #1412691
Posted Monday, January 28, 2013 6:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
I got mirroring to work in the testing environment. Now how can I write queries against it?
Post #1412716
Posted Monday, January 28, 2013 10:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 12, 2014 6:25 PM
Points: 72, Visits: 434
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...
Post #1412753
Posted Monday, January 28, 2013 11:43 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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
Post #1412771
Posted Tuesday, January 29, 2013 3:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 2,013, Visits: 3,447
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
Post #1412851
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse