Does anyone know if it's possible to mirror only data changes with SQL Server 2008's database mirror functionality. I suspect the answer is that it isn't possible, but wanted to see if I've missed something.
If not, does anyone know of any way to replicate only data and table schema changes from a source database to a reporting system. We don't want stored procs, views, functions, etc in the destination system. Replication doesn't need to be instantaneous, but what we're trying to avoid is having to do a full database copy followed by a DROP of all sprocs, views and functions (due to the size of the backup and the time this can take).
Replication (as in full-fledged SQL Server replication) isn't an option -- it has been investigated and would involve some changes to the database design, which would then take a number of weeks (if not more) to actually implement, test and support in the application above, as well as restricting and complicating how changes are applied to the database (i.e., although replication might technically be the right solution, for a whole host of process management and time constraints, it's probably not going to happen).
Cheers.
Change Data Capture, aka CDC, can CAPTURE the changes to the data, and then you could write something, like an SSIS package, to propagate those changes to another server, but that would take some effort, not sure if it would be more or less than setting up replication, it would depend on the number of tables.
Mirroring is definitely not able to do this, it can only mirror the entire database.
If SQL Server Replication is not an option, I think you will need to come up with a solution that is not built in to perform this task.
How "connected" are the 2 servers? The next thing I could think of would be to setup the destination server as a "Linked Server" within SQL Server. You can then use a database trigger on the source server to update the destination server using the linked server. You need to be careful in your trigger to get the naming convention down though, it would have to be something like
server.database.schema.object. I think the servers would need to be (network-wise) pretty close to each other as well for this to work with any sort of reliability.