I have an in-house database (that's actually part of a third-party product) running on 2008R2 Express. We'd like to display some of the data on a new website we're working on (running 2008R2). I'm more developer than administrator, so I'm wondering the best method for getting data from one to the other. Data doesn't have to be available real-time, daily replication is acceptable.
Again, this is display only, so two-way replication is not necessary.
I know of replication as a concept and have heard of Sync Framework, but have no experience with either and don't know where to start. I also don't have access to install anything on either server, so to my understanding this rules out Sync Framework.
I've been reading up on replication, but when I looked into creating a publication on the LAN database, I didn't have the option to create one (I'm assuming this is because it's Express). I've also looked into mirroring and snapshots, but I'm not sure where to start.
I'm able to create a linked server from the website database to the LAN database, but for security and performance reasons I can't query directly, which is why I need to replicate the data.
Best Answer
There are a variety of ways you can do this.
If you only need a portion of the database, you can have copies of the tables which you refresh on the destinations periodically. The idea is that you have your users reading one copy of the data, then you have an identical table in a different schema, and you reload that copy while the users are reading. Then in a very quick transaction, you switch the copies of the table between schemas, and it's essentially a metadata operation. I describe this in detail in the following two posts:
If you need most or all of the database, then it is going to be much simpler to use log shipping to facilitate readable secondaries. It just so happens that today I published a very thorough walk-through of creating readable secondaries with Express:
Essentially, you play round-robin with which instance gets read from at any one time; since you want multiple active simultaneously, it would only be a little bit more complex. You might have two or four instances of Express on each web server, and every 15 minutes you switch the standby on all of the servers to have the most recent data.
Since you're on Express for both source and destination, in either case, you will need an agent of some kind to schedule the daily (or more frequent) pushes.