Bill Fellows got an increasingly specific set of requirements about data collection:
This post is another in the continuing theme of “making things consistent.” We were voluntold to help another team get their staging environment set up. Piece of cake, SQL Compare made it trivial to snap the tables over.
Oh, we don’t want these tables in Custom schema, we want them in dbo. No problem, SQL Compare again and change owner mappings and bam, out come all the tables.
Oh, can we get this in near real-time? Say every 15 minutes. … Transaction replication to the rescue!
Oh, we don’t know what data we need yet so could you keep it all, forever? … Temporal tables to the rescue?
Yes, temporal tables is perfect. But don’t put the history table in the same schema as the table, put in this one. And put all of that in its own file group.
Click through for a helpful script, and tune in next time, when the other team has Bill move their furniture around. Maybe move the couch just a hair to the right…no, a little more, oops, too much…