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

Replicate 2008 Express table to 2008 database Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 2:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:53 PM
Points: 12, Visits: 67
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
Post #1545143
Posted Friday, March 7, 2014 10:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:15 PM
Points: 802, 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."
Post #1548813
Posted Monday, March 10, 2014 10:47 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 23, 2014 9:00 PM
Points: 611, Visits: 448
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 .

Post #1549381
Posted Monday, March 10, 2014 12:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:53 PM
Points: 12, Visits: 67
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
Post #1549428
Posted Tuesday, March 11, 2014 2:13 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 23, 2014 9:00 PM
Points: 611, Visits: 448
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
Post #1549931
Posted Wednesday, March 12, 2014 6:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
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)
Post #1550199
Posted Thursday, March 13, 2014 1:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:53 PM
Points: 12, Visits: 67
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
Post #1550891
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse