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


Replicate 2008 Express table to 2008 database


Replicate 2008 Express table to 2008 database

Author
Message
Nooch
Nooch
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 72
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
The_SQL_DBA
The_SQL_DBA
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2548 Visits: 935
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."
RatanDeep Saha
RatanDeep Saha
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1162 Visits: 693
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
Nooch
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 72
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
RatanDeep Saha
RatanDeep Saha
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1162 Visits: 693
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
MysteryJimbo
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4987 Visits: 15346
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
Nooch
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 72
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
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