Below steps to setup this sync job:
1. Use BCP to export & import data
On production server, run bcp command to export data, for example:
#bcp "SELECT * FROM log_table" queryout "C:\log_table.bcp" -N -S "PRODUCTION_SERVER_NAME" -d "DB_NAME" -U "USER_NAME" -P "PASSWORD"
After that, upload this log_table.bcp file to your slaver server, create a log_table having same structure as on production server, then run a command like example below to import data to your log_table:
#bcp "log_table" IN "E:\log_table.bcp" -N -S "SERVER_NAME" -d "DB_NAME" -U "USER_NAME" -P "PASSWORD"
2. Make Powershell script to bulk copy new data from production server to slaver server
Below is my sample script:
#Set-ExecutionPolicy Unrestricted
#2 params needed to provide for the script
Param (
[parameter(Mandatory = $true)]
[string] $LogTable,
[parameter(Mandatory = $true)]
[int] $Syncrows
)
$SrcServer = "PRODUCTION_SERVER"
$SrcDatabase = "PRODUCTION_DB"
$SrcUser = "USER_NAME"
$SrcPwd = "PASSWORD"
$SrcTable = $LogTable
$DestServer = "SLAVER_SERVER"
$DestDatabase = "SLAVER_DB"
$DestUser = "USER_NAME"
$DestPwd = "PASSWORD"
$DestTable = $LogTable
#create connection string
Function ConnectionString ([string] $ServerName, [string] $DbName, [string] $User, [string] $Pwd)
{
"Server=$ServerName;uid=$User;pwd=$Pwd;Database=$DbName;Integrated Security=False;"
}
# executes a query and populates the $datatable with the data
function ExecuteSqlQuery ($ConnectionString, $SQLQuery) {
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Reader = $Command.ExecuteReader()
$Datatable = New-Object System.Data.DataTable
$Datatable.Load($Reader)
$Connection.Close()
$Connection.Dispose()
return ,$Datatable #work arround to get right datatable after unrolling arrays/collections
}
#get max id of last synced rows
$DestConnStr = ConnectionString $DestServer $DestDatabase $DestUser $DestPwd
$Query = "SELECT MAX(Pid) FROM " + $DestTable
$resultsDataTable = ExecuteSqlQuery $DestConnStr $Query
$LastRow = $resultsDataTable.Rows[0][0]
#bulk copy
$SrcConnStr = ConnectionString $SrcServer $SrcDatabase $SrcUser $SrcPwd
$SrcConn = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)
if($Syncrows -eq 0) {
$CmdText = "SELECT * FROM $SrcTable WHERE row_id>$LastRow"
}
else {
$CmdText = "SELECT TOP ($Syncrows) * FROM $SrcTable WHERE row_id>$LastRow"
}
#Write-Host "$CmdText"
$SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)
$SrcConn.Open()
[System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
Try
{
$logmsg = "$LogTable : Bulk copy completed!"
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
$bulkCopy.DestinationTableName = $DestTable
$bulkCopy.BatchSize = 100 #rows
$bulkCopy.BulkCopyTimeout = 1800 #seconds
$bulkCopy.WriteToServer($SqlReader)
}
Catch [System.Exception]
{
$ex = $_.Exception
$logmsg = $ex.Message
}
Finally
{
$currenttime = Get-Date -format s
Add-Content D:\logstatus.txt "`n$currenttime $logmsg"
$SqlReader.Close()
$SrcConn.Close()
$SrcConn.Dispose()
$bulkCopy.Close()
}
The above script will use BulkCopy to copy rows having id > id synced. You can limit number of rows for syncing via $Syncrows param. If $Syncrows=0, it will get all new rows to copy.
3. Create task schedule for copying new data
Use Task Scheduler to create a new task for running the script file, you can select your schedule as your demand. Remember to add below line in Action setting for passing Execution Policy:
-ExecutionPolicy Bypass D:\synctbl.ps1 -LogTable log_table -Syncrows 10000
That's all. Happy coding! Any questions?
