Replicate 2008 Express table to 2008 database

Question

Post reply

Replicate 2008 Express table to 2008 database

Nooch

SSC Veteran

Points: 262
More actions
February 25, 2014 at 2:45 pm

#304774

Hi,
I have a SQL 2008 Express table that I would like to replicate to a sql server 2008 database on a remote server. Since Express does not have the publisher function, is there an alternate method to achive this?
Thank You,
nuchbcc

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

What kind of changes do you need to replicate? Is you destination server Express or Std/Enterprise?

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

SQL Severe DBA Hall of Fame Points: 3579 More actions · Answer 2

Firstly how often you need to replicate and why you want to replicate . Is the data in required up to date all the time .

If not opt for SSIS package or simple solution replcation setup need distribution setup too . Add more details for better solution options .

Nooch SSC Veteran Points: 262 More actions · Answer 3

Thanks for replying.

The destination database is a Standard 2008 SQL

I would like to create a sql agent job that would run every 10 minutes. The job would check for new records in a table on a remote database (linked server already created), if exist the job would import the new records to destination database.

From destination server SSMS, I've tried connecting to remote Express database and selecting Task/Export to create an SSIS package, I would supply a manual select statement ("select top 1000 * from "servername".DBname.dbo.table order by Created desc). On finishing the SSIS job creation the job would run and updates the new records on destination DB. However, when I rerun the job from SSMS, it doesn't work.

Preferably I would like to just add the new records not latest 1000 rows and encountering duplicates.

Thank You for your time,

nuchbcc

SQL Severe DBA Hall of Fame Points: 3579 More actions · Answer 4

A convenient way to do is using MERGE as that works based on target server condition and you can insert , update and delete in the destination table .

Put that command in your job and hopefully that will work out for you .

http://msdn.microsoft.com/en-us/library/bb510625.aspx

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

You could use SSIS or even Merge replication by switching the roles.

Have the destination server as the publisher with an empty table, then insert the data into the subscriber. You will need to consider retention of meta data if rows get updated (to prevent conflicts when metadata has been cleaned up)

Nooch SSC Veteran Points: 262 More actions · Answer 6

I somewhat have it working by creating a SP on destination server to insert into local table the top 500 records from remote source table and running it as an sql agent job. However, now I’m finding out that I need to actually insert the new records to another database on a remote server, on another subnet which is firewalled. There is only a one way communication between the 2 subnets. The new remote server destination is running sql 2005 standard. When I publish replication from 2005 sql and tried to subscribe from 2008 sql (different subnet), SQL complained that subscriber version can’t be newer than publisher. I can’t switch publisher and subscriber between servers because of the one way communication.

This is getting more and more complex for my SQL experience (very limited), 3 sql servers running 3 different versions (Express, 2008 standard and 2005 standard), 2 subnets with one way communition between the 2. I need to put this on hold for now, I have another project that needs to get done soon.

Thank you for your help.

nuchbcc