sql servertransactional-replication
I have a need for a test server that hosts a small subset of data from our production systems. Is it possible to setup a SQL Server Replication Job that only keeps a week's worth of data so developers can develop reports?
Keep running 7 days of data, keeping the storage need small is the goal.
Can this be achieved with replication?
Yes, Transactional replication can be used in your case.
Can we get subsets of data from a subset of tables from the source, into our new replicated database?
Yes you can just replicate the tables (articles) that you want along with its subset of data.
e.g. where PersonTypeId = x --> You need to use static row filter as it uses a WHERE clause to select the appropriate data to be published.
selecting specific articles (tables) :
Filtering what data to publish :
Would it be something like creating a VIEW on the source, and replicating the results of that view, as a table in the replicated database?
No. See above answer to use static row filter when you are publishing the articles.
I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database? Is this remotely feasible?
SQL Server Express cannot serve as a Publisher or Distributor.
SQL Express can only be Subscriber. Refer to : Replication Considerations (SQL Server Express) for more details.
EDIT : To make my answer more meaningful, I am adding more details
Test any scenario that you are going to implement to avoid any surprises !!
In doing my research it seems the recommended approach would be to have the Distributor on a different server than the Publisher DB?
This is true up-to certain extent only. Its a best practice, but depending on how much data you are replicating and how busy (in terms of activity or transactions) is your publication database and the network latency between publisher-distributor-subscriber, this setup will affect you.
In your scenario, if your database is --
having less or moderate activity then
highly active then
Other thoughts:
Since you are already using Enterprise Edition, why not use Database Snapshots - with caution !!.
From Looking at Database Snapshot Performance
When using database snapshots, even in SQL Server 2012, there is an overhead associated with the additional writes required for copying data pages to the sparse files for the snapshots. If using database snapshots is a part of your general configuration, I would really be careful about planning the I/O subsystem to meet the workload requirements for concurrent I/O activity to the database snapshot sparse files.
So database mirroring is another option as well and you can create snapshots on the mirrored database and have it as reporting database.
Also, if your business does not want real time data, then a custom solution can be designed that can Extract, Transform and Load the data to another server using SSIS (or any tool of your choice) and that can be used as reporting.
Best Answer
It has been a couple years since you asked this. If you found a solution you should answer your own question.
There is some good info about a similar (but different) issue at Moving Transaction Data To Another Database For Reporting Purpose
For your question "SQL Server Replication - Only weeks worth of data" the solution is going to depend on your design. Does every row of every table have a date field that indicates it is fresh this week?
I suspect the answer is no...
If you can't identify what is new in the last week, it is not possible to meet your requirement.
You don't say in your question, but I suspect lack of space on the test server is what drives your question. This means you don't have room to copy everything over and then delete what you don't want.
I would look to creating a view that has the data you need, then I would use a variant of this solution to copy the data to the test server. Do the copy off hours, once a day.