Sync only specific tables from one SQL 2008r2 to a 2nd SQL 2008r2

Good Morning everyone,

I need to sync specific table from one SQL 2008r2 server to another.   I was looking at SQL Alwayson but it look like that is primarily for clustering and it is all or nothing(I think).  What is the best way to set this up?

Thanks
Richard ComitoDirector of ITAsked:
Who is Participating?
 
Deepak ChauhanSQL Server DBACommented:
In this case replication is the best option. You can configure Transactional replication this way you dont need to provide production (publisher in replication) database access and other users will be pointing to secondary server (subscriber in replication).

No impact on production DB.
You can share only limited tables.
Realtime production data can be available immediately or as per configuration.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Can you add the second server in as a Linked Server?

Look under Server Objects, and open the Linked Servers. If that server is not already linked, right-click on Linked Servers, select New Linked Server, and in the Linked server: prompt, put the name of the SQL Server (include the \INSTANCE if it is a named instance).

Select SQL Server as the server type.

On the Security page, if you know a SQL userid, you can select "Be made using this security context", then enter in the remote login and password. That's the easiest and fastest way to link.

After that, you'd select from that server as follows:

SELECT * FROM [YourOtherServer].Database.dbo.TableName
0
 
Richard ComitoDirector of ITAuthor Commented:
thanks dscker,

Yes I can do a linked server.  I am thinking that after I link the servers, I can then create a job using merge to sync the tables I need.  Does that sound right or is there a better method?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
dsackerContract ERP Admin/ConsultantCommented:
That would be the right approach. Once linked, you can join them however you wish.

If you expect a lot of data, be a little careful with linked server joins. Sometimes whenever possible I'll bring the linked server data over (if I can filter it ahead of time) into a @TempTable, then join that.

In other words, you'll want to play with a few options if speed becomes an issue.
0
 
Deepak ChauhanSQL Server DBACommented:
Yes you are right AlwaysON basically for database Mirroring in SQL2012 and onward but supported on enterprise edition with Failover clustering.

if it is only one time activity then you can use linked server but in case it is repetitive, i would suggest to configure replication from source server to destination server for the selected tables or all tables.

other way using import\export wizard.
0
 
Richard ComitoDirector of ITAuthor Commented:
Thanks deepakChauhan,

Yes it is going to be repetitive.  We have a company that needs access to some data, but I don't want them on my production servers.
0
 
Scott PletcherSenior DBACommented:
There is certainly some impact on the production db if you are replicating it.  Replication adds a task that reads the log and the whole process can delay log truncation.
0
 
Deepak ChauhanSQL Server DBACommented:
GabicusC - what is your database size and transaction frequency in this database.
0
 
Richard ComitoDirector of ITAuthor Commented:
Hi deepakChauhan,

My DB size is 60 gigs and the transaction frequency is every 30 minutes.

Gabicus
0
 
Deepak ChauhanSQL Server DBACommented:
60 GB is not a big size you can configure Replication if you found suitable in your environment.

Can i assume transaction commit rate is 5-10 per minute in production DB?

If it is i don't think any harm.
0
 
Richard ComitoDirector of ITAuthor Commented:
deepakChauhan,

Not to ask a stupid questions but how do I find the transaction commit rate?

Thanks,
0
 
Deepak ChauhanSQL Server DBACommented:
I mean to say transaction per minute in the database. You can check it by using the below script. this script will give you the count of one minute transaction. Please change the server name and database name where mentioned.

DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$DEEPAK:databases' -- Change name of your server if you are not sure about server --name check the (select top 1 object_name FROM sys.dm_os_performance_counters where object_name like '%databases%'
AND counter_name = 'Transactions/sec'
AND instance_name = 'Test'; -- change your database name

WAITFOR DELAY '00:01:00'

SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$DEEPAK:databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = 'Test';  -- change your database name
SELECT (@Second-@First) 'TotalTransactions'
GO
0
 
Deepak ChauhanSQL Server DBACommented:
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$DEEPAK:databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = 'Test'; -- change your database name

WAITFOR DELAY '00:01:00'

SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$DEEPAK:databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = 'Test';  -- change your database name
SELECT (@Second-@First) 'TotalTransactions'
GO
0
 
Richard ComitoDirector of ITAuthor Commented:
Thanks for he script.  the Transaction rate is 1003 per minute.
0
 
Deepak ChauhanSQL Server DBACommented:
Its ok. You can configure Transactional replication for specific tables.
0
 
Richard ComitoDirector of ITAuthor Commented:
Fantastic!  Thanks for the help.  Once my network guy gets my server built, I will set this up.
0
 
Richard ComitoDirector of ITAuthor Commented:
Thanks deepakChauhan.  This worked out the best.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.