| Author |
Topic |
|
Sitka
Aged Yak Warrior
571 Posts |
 Posted - 2010-03-05 : 09:33:54
|
| I don't understand the limitations of a DNS Record alteration in order to accomplish the same as a Server Rename when failing over to a log shipped server.I am getting new SQL Server hardware.It will have about 30 databases, serves about 300 people.Has one 20 Gig database as the backend for an ERP systemThe rest of the databases are mainly small application ones that support code from1998 until present day.I was on SQL 2000 STD and scripted log shipping to another buildings Warm standby SQL server.Upon failure of the new server, the move to the standby would involve a renaming and IP change of that server to the old, and a correction of the orphaned SQL logins.This seems pretty standard stuff, although the failover never HAD to happen. It tested well and I felt secure.The one flaw in the plan was the act of the rename and getting the server specific jobs and maintenanceplans running on the warm failover could not be understood by others. (The written step by step documents couldnot be followed by those who did not know what Query Analyser was.)So at the moment before I put in the new system I am wondering if a DNS alias switch and IP reassignmentcould be used instead to accomplish the failover.So does SQL Server 2008 STD with "ITS" suppport for log shipping handle the failover to the warm standby and the rename?What are the pitfalls of a DNS switching approach when going to the standby server?(4 part naming, Sharepoint links...etc)We can tollerate 1/2 hour downtime on our main apps and an hour on the less crucial ones.I can access almost the entire application space (except the ERP and Microsoft stuff) and configure connectivity to suit but I can't move all of them to the .NET SQL client.I'd consider other failover solutions but...Clustering is out, we could not afford shared storage, or have a clue as to how to make it work.The main ERP application lacks primary keys on it's tables."it's definitely useless and maybe harmful". |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-05 : 18:17:51
|
| Yes you can use a DNS alias instead. We are doing that on a few mission critical applications in production at the moment. The applications are pointing to a SQL Server that no longer exists, and we are unable to get them redirected to the new server due to lack of development resources. So we are instead using a DNS alias that redirects the old name to the new name.I don't know what "ITS" is. I'm not familiar with that acronym.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2010-03-08 : 09:31:44
|
| Thank you, I don't know why I typed "ITS", I just meant its..I'm not familar with 2008 yet, I was just wondering if there is a recovery interface from the 2008 SSMS on the secondary server instance that handles... 1.the move from Standby to Recovered, 2.the Server Rename 3.and maybe even some login help for orphans like the nice script you wrote a few years ago."it's definitely useless and maybe harmful". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2010-03-09 : 11:39:28
|
| Thanks again.I'm doing some scenerio testing now.DNS alias will probably be part of the new database connectivity / failover architecture.Log shipping still seems to suit my comfort level the best but I'm testing mirroring. it sets upsuper easy. On SQL 2008 std there is no option for High Perf(Assyncronous) mirror. Is this the same as SQL 2000 std does not support log shipping? Which it did if you wrote it yourself. I read some of the requirements of High Safety(Syncronous) mirror and the two stage commit has me suspect that mirroring may do more damage than good. I won't have equivalent hardware for the mirror. Our network is perfectly capable of routine disconnects ( it demonstrates that ability almost daily..., WTF ). While an automated failover seems great for the rest of the IT people. I don't know if High Safety Mirror presents best value. It may never get used and may be a burden on 99.999 percent of operations and could even make an outage and recovery more suspect depending on client connectivity.After reading some of your posts about fast connection WAN mirroring, I guess if I had an additional question it would be, How do I come to understand the operational costs of a High Safety Mirror?"it's definitely useless and maybe harmful". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-09 : 19:55:26
|
| I've only used asynchronous mirroring as the performance hit of the two-phased commit was much too high in our environment. We have huge performance requirements, so adding even just a few milliseconds is unacceptable. I wasn't aware that async wasn't available on SQL 2008. I'm only using mirroring on SQL 2005 thus far.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2010-03-10 : 07:46:35
|
| I wasn't aware that async wasn't available on SQL 2008. I'm only using mirroring on SQL 2005 thus far.Async (High Performance) is available on 2008 Enterprise Edition, not on Standard, or at least that is the claim.I'm going to have to script the mirroring and see if Async is just disabled in the GUI."it's definitely useless and maybe harmful". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2010-03-12 : 11:59:51
|
| Here is an approach I thought up, its weird but tested as working on a workstation.There is a single physical machine SQL1 that hosts the entire application space.It is Windows Server 2008 R2.SQL is set to do morning full backups and TLOG backups to a local drive every 30 minutesOnce (maybe twice, three) a day Windows Server Backup is used to a local drive dedicated for bare metal recovery.At lunch a script runs that uses disk2vhd from sysinternals, it is a physical to virtual hard drive converterthat is Volume Shadow Copy aware (VSS). The .vhd that it creates is then XCOPIED offsite to a network share.Each half hour cycle, the T-logs go to a USB drive (those small ones) tucked up on the cable manager shelf in the back ofthe server rack.The 30 databases are point in time capable during the day, from SQL alone.in the event of a BSOD and no boot condition, the Windows Server Backup repair is used.in the event of hardware failure (or buildings burning down) the VHD that was offsite is recoved to a Virtual machine.if there was no fire the most recent tlogs are recovered from the usb drive and applied to the recovered Virtual Machine.No other configuration or recovery action is required.Its like a daily ghost, that in the event of a recovery, changes from a physical install to a virtual machine and becausethe usb drive is separate from the original server any breakage does not effect the ability to get to the Tlogs.This is way less technocrat than a manual Log Shipping failover, and will elliminateall pitfalls that can show up for repair even after the databases are being served once again,(things like jobs, maintenance plans, logins, optimizations)Consideration was given to set the new SQL server up as a virtual machine from the start.Then the failover results in the exact same configuration, before and after.True that is more beautiful but Hyper-V only supports 4 logical CPU cores.I can see in a year or two years wanting 16 logical CPU cores (buy a second processor) with 24 Gig of ram. I don't think a compromise should be made for this.Restore that works and is easy should be enough, and performance compromises should not be made.How will this scale?A 175Gig .vhd creation routine takes 35 minutes to run.That is quite a big file to move to the next building during businesshours but I think we can isolate that communication.I believe the new server will be born using around 45Gig of disk space and growthhas been historically slow maybe up to a 10 Gig increase last year.I think losing the log shipping overhead will present a net service gain overall.Neat huh."it's definitely useless and maybe harmful". |
|
|
|
|
DNS CNAME Record Switch Vs. Server Rename