September 22, 2014 at 7:46 am
Hi all
This is probably going to be a long-winded post, but I'll keep it as brief as possible.
Here goes......
Where I work, we don't have a prod/dev servers, just two servers that hold almost identical data.
We also have an application server that holds 4 SQL databases (two of which are test). Call this one server A and it's running SQL 2005 (and won't be upgraded any time soon).
I want to set up prod and dev servers off the application server as we aren't allowed to report against it.
At the moment, one our "reporting" servers (call it server B) is a mirroring recipient from Server A. The other "reporting" server (call it Server C) has no mirroring/transaction log shipping on it, it gets its data from Server B (this is done using INSERT INTO/SELECT queries).
Server B is running SQL 2005, Server C is running SQL 2008.
When I set up the prod (Server D) and dev (Server E) servers, I want them to hold identical data (the dev server will be slightly less powerful than the prod server but otherwise identical). I've had the specs for Servers D & E approved already. Servers D & E will be running SQL 2012 (the earliest version we can now get licences for).
With SQL 2005 (as far as I know), you can only mirror to one location. I'm happy to be proven wrong on this.
I'm thinking (to keep things standard across Servers D & E) about using transaction log shipping (or maybe replication?) on both servers.
Now for the questions (in no particular order):-
1) Is transaction log shipping going to be the best way to do what I want (or, is/are there better ways)?
2) If transaction log shipping is the best way to go, how do I initially load the databases on servers D&E? Can I do backups of the relevant databases, copy them across to the servers and restore the backups?
3) Server A is in use 24/7. Will the database backups prevent people from accessing the application while the backup is running?
4) If we go down the transaction log shipping route, would that be set up before the databases are backep-up/restored or after (obviously, I don't want any missing data)?
I'm bound to have forgotten something, so any other pointers/reading material would be very appreciated.
September 22, 2014 at 7:58 am
If you log ship, then your dev database will be completely read-only. Mirroring's worse, the DB isn't accessible at all. Kinda defeats the point of a development server.
Why do you need development to have identical data to production? Is that even legal in your industry, for developers to see production data?
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
September 22, 2014 at 8:09 am
Hi Gail
As things stand, transactions are mirrored from Server A to Server B. Server B then snapshots the relevant databases and tables/views are created from the snapshots for people to run queries against.
(I'm assuming for now that the snapshots are read-only).
I want to merge servers B & C into the production server, but I also need the same data on the dev server (please, don't ask!).
In a nut-shell, I want the easiest/most robust method of getting the data from server A to Servers D & E as quickly as possible.
I'd be happy with the snapshots that mirroring uses (as that's what happens now) but you can't mirror to two different servers.
September 22, 2014 at 8:26 am
Snapshots are read only, log shipping secondaries are read only.
I have to ask, because the reason for needing up to date data will affect appropriate methods. If it's a reporting server, then read only is fine. If it's a dev server, then developers will be changing both schema and data and hence it has to be read-write and has to not break when they change things and has to be static enough that they can test.
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
September 22, 2014 at 8:44 am
I don't have a problem with the data being read-only (to start with at least).
As per my previous post, we use snapshots to create "secondary" tables with views off those (where necessary) for people to report against.
The snapshots are currently taken at midnight to cover the previous day (we don't need/use anything newer than that).
From what little I know about transaction log shipping, the logs would need to be shipped on a slightly more regular basis than once a day or they would grow out of control.
At a rough calculation, around 1000 records are actually added to the database(s) a day but there are a lot of updates (I haven't a clue how many to be honest, but I would guess at least 10,000).
I'm fairly certain the log shipping minimum is every 15 minutes so that might be a good place to start (I'm willing to start elsewhere to start with and alter the times accordingly though).
As far as I know, the schema won't be changing and there will only be minimal changes to the data.
September 22, 2014 at 8:47 am
So the server is intended as a reporting/analysis server (read-only access to production data), not a development server (where developers are actively working on new features and changing existing features)?
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
September 22, 2014 at 8:58 am
Both the new servers will be used for reporting purposes.
I suppose it would have helped if I'd mentioned that to start with.
In my head, we need to dev server to develop reports and make sure they run in an acceptable length of time before pushing them to production for the general populace here to run.
The dev server will also be used to run ad-hoc queries for those random information requests we know and love(!).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply