June 28, 2011 at 3:25 pm
First off, this blog/forum has been a wealth of information. I'm a fresh DBA in a small marketing/sales company and this site has been a great tool during my tenure here.
I've been playing around the idea of doing database mirroring with our two servers. We have our production server and we have a test server environment that are both SQL Server 2008. Our test environment is currently used for SSIS, SSRS, marketing, custom databases and general testing.
We copy the data over monthly from production to test to populate a sales/marketing database (since our SQL driven programs do not interact with each other) and use this database for reporting.
I have an SSIS package that will copy the sales data over, merge the records, and create the database.
What I want to do is have live data flowing automatically, and that seems like where mirroring would come in. Maybe I'm missing the point of how mirroring works, but wondering if it's possible.
With that being said, if I was to use mirroring in SQL Server 2008, can I perform transactions on the test environment while not affecting the production data and still maintaining the mirroring aspect? Or should I stick with what I'm doing and copy over data from production to test on a monthly basis?
If this has already been asked, I apologize in advance. I could not find my answer in the search box.
June 28, 2011 at 3:28 pm
You can mirror to test, but the mirroring database is inaccessible. You can create a snapshot over it (Enterprise only), but that's read only.
What's wrong with a backup-restore on a regular basis for refreshing test?
For the reporting, how about transactional replication?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2011 at 3:42 pm
I guess there's nothing wrong with copying data over, it'd just be more convenient to have "live" data being pushed to the test environment. I'll probably end up scheduling my packages to run every 4 hours or so to get my desired result (my boss is a big statistical nut, likes to see what sold during all hours of the day).
As for transactional replication, we don't have a need for any high level stuff here, so I'm unaware of how that works.
Thanks for summing up my initial question.
June 28, 2011 at 4:20 pm
A couple things.
First, replication isn't a high level thing. It works by copying over transactions from the publisher to the subscriber, which can be your production and test environments, respectively. It will move live data over. You might read about transactional replication as it can fit for you.
As Gail mentioned, in mirroring you are essentially always "restoring" the mirror a transaction at a time, so it's not accessible.
However.
If you run transactions against test, are you not invaliding the ability to use that data for reporting?
June 28, 2011 at 4:28 pm
joshd 1807 (6/28/2011)
As for transactional replication, we don't have a need for any high level stuff here, so I'm unaware of how that works.
It's far simpler, far less prone to problems than mirroring. in fact, I'd say mirroring's more high level than replication by far.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2011 at 7:07 am
Steve,
To answer your question about validity, I always copy tables to a seperate testing database that I need to test transactions on. That way data is consistent.
I'll take a look at transaction replication. I'm still learning SQL Server 🙂
Thanks guys!
June 29, 2011 at 9:36 am
In that case, it sounds like you have a reporting environment and you're testing there sometimes, building a dev environment others?
Why not just separate those out? Resource constraints?
Replication will get you more up to date views of the data. If you have datetime markers on each row, SSIS might be more efficient. The former is more administrative work along the way, the latter more development work up front.
June 29, 2011 at 10:54 am
Steve, I'll let you know our situation and the method to my madness.
We have no development happening. I'm managing production systems on the database and user level.
We have a CRM system, Quoting/Invoice System, and ERP system. Two of these systems run hand in hand (CRM and Quoting) but the ERP doesn't. Unfortunately our sales information is stored in our ERP and in our Quoting systems, that don't interact automatically. I just want a way to duplicate the transactions from our production server to our test server to eliminate the need for manual processes to create current reports (I just want current data in our test system without any manual processing required). I have an SSIS solution ready to go live, but we're in the process of migrating to new hardware. I figured I'd see if there was a better way of getting live data to our test system for the time being.
From what you two have explained (and what I've looked into as of your postings) is that transaction processing would be what I'm looking for.
June 29, 2011 at 11:31 am
You sound like you have a good handle on the situation. If it's limited tables (and for a limited time), replication should work. If it's all tables, might overkill, but play with it.
Replication is easy to setup (wizards) and for a single subscriber, not bad. Just one hint: if the dev server is down or not receiving transactions for some reason, the production log won't be cleared. It could be your main log, or the distributor log, depending on how things are configured, so be careful.
July 4, 2011 at 4:29 am
easier for pure reporting is log shipping. Easy to setup and maintain but read only (which is all your reports should need)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply