March 28, 2010 at 8:08 am
I am looking for some inputs from seniors here in the forum in changing my existing environment.
Current:
Our production server has all read only databases and we have another server where we load data (no updates) and then backup it up and restore on to production with different name and then switch over the databases which is a 5 min downtime for production databases.
sql 2005 Ent,Win 2003,litespeed,databse size range from 300GB - 2 TB.
Projecttion :
Looking for the best alternative to reduce workload for backup and retore to production with the best procing and easy maintanance.
some thoughts:
1. mirroring but may have to change recovery model to bulk from simple.
2. Clustering?
3. replication?
I can also have option to migrate to 2008 if possible but the options may still remain same but have to look for the benefits in migrating.
March 28, 2010 at 12:06 pm
Without knowing a lot more about how you are setup and configured, my first thought would be to investigate the capabilities of the SAN you are using.
Each SAN has different capabilities, but most have the ability to perform hardware mirroring, snap clones, snap shots, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 28, 2010 at 5:41 pm
Yeah i was thinking about those options but was wondering how they work without moving any drive manually from one server to another, could you please breif me benefits and drawsbacks, may be kind of comparision between Mirroring,Snap Shot and Cloning, PLease....
March 28, 2010 at 5:45 pm
It really depends upon the SAN. For one particular SAN, we were looking at mirroring the database volumes and daily performing a snap clone of the mirrored volume. This would involve dropping the existing LUN's from the destination, performing the snap clone, remounting the LUNs and attaching the database. The whole process would take at most a couple of minutes - with the snap clone taking up to several hours in the background, but still available to the system.
Again - it really depends on the SAN and what utilities you have access to.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 31, 2010 at 7:36 am
I was thinking of having additional identical server may be a stand by server but how can I swith server names with in no time by which even users should not realise the change at their end.
May 4, 2010 at 2:29 pm
Tara-1044200 (3/28/2010)
Yeah i was thinking about those options but was wondering how they work without moving any drive manually from one server to another, could you please breif me benefits and drawsbacks, may be kind of comparision between Mirroring,Snap Shot and Cloning, PLease....
I have 2 production servers (2005 Enterprise) and 1 Dev Server (2005 Ent) . I do add data every quarter on Dev server and backup and restore on 2 production servers and rename the databases.
Now i want to improve sometime for doing this process and so i would like to have single disk where 2 of my production servers point to and swap the Dev server disk when the new data is ready, Is it possible to do it in 2005 without the help of network admin or i have to choose 2008 ?
May 5, 2010 at 5:03 am
Tara-1044200 (3/31/2010)
I was thinking of having additional identical server may be a stand by server but how can I swith server names with in no time by which even users should not realise the change at their end.
Hey there,
SO if i understand right, you have a ro copy in prod (which presumably users access and query), then in another network/environment you have rw copy of the same database, which is updated with fresh data. You then backup the rw and restore it over the ro for your prod users.
This being the case, sql mirroring would be no good as the mirror is offline (restoring), you cant have two servers share the same san lun or access the same physical files at the same time either.
Id aggree with Jeff that the snap-shotting seems the way to go. You may find that you can take sql offline, unpresent the old drive, represnt the new one (using the old drive letter), start sql and it may work. My guess is though that some GUID's would be different and sql would throw a fit. That being the case, you'd have to dettach old and atttach new.
For a low tech option, you could go down the replication route with the replica being your ro prod copy. That way the only downtime should be while the actuall data load and subsequent sync is taking place (though depeding on your data and network links this may take longer then the current 5 minutes).
Either way its an interesting project.
Adam Zacks-------------------------------------------Be Nice, Or Leave
May 5, 2010 at 5:29 am
for the 1st part you understood it correctly but i am not sure after that.
Totally i have 3 servers 2 (ro) and 1 (rw). Say i have 50 prod databases, i will have 25 each on 2 of the production servers(ro) and 50 on Dev server(rw). We managed to divide depnding on their size and user activity, hoping to balance the load.
My thought was ,as the prod servers are read only why cant i merge the disk for both to share a single big drive keeping other resources independent so as to not lose any processing power. and i may have to swith disk from prod to Dev everytime i append fresh data but to do so i dont want to take Network admin help to swap the LUN manually, is there a way i can control from sql server may be in SQL 2008?
May 5, 2010 at 5:55 am
why cant i merge the disk for both to share a single big drive keeping other resources independent so as to not lose any processing power.
The only way to do this would be to place the volume (with all your dataabases and maybe logs) on a NAS kind of setup. This would/could work but is not really recomended from a thoughput point of view. Also sql cant run a database over a unc (it has to be 'seen' as a local drive, so you would also have to get around that. Also if you could make it work you would potensially i have a security risk (both servers working in the same volume).
and i may have to swith disk from prod to Dev everytime i append fresh data but to do so i dont want to take Network admin help to swap the LUN manually, is there a way i can control from sql server may be in SQL 2008?
Simple answer is NO. What you are suggesting is not best practice, ideal or even advised. What you would be suggesting is using the NAS styly solution described above, then detaching databases (from prod) attaching those same databases to a third server (to update) then dettach and re-attach to prod.....
Doesnt that seem dangerous? My advice would be to keep it as simple as you can. Run you prod and dev copies. Update dev then backup and restore to prod. Try and do the restore ooh (you could even script it) and that way down-time for users should be to a minimum.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply