<first…….go grab yourself a 6 pack….this is a long one>
<beer 1>
I admit it…..I am a SQL n00b…..So I am taking a SQL 2072 course next week in Oklahoma City.
Go ahead…..I know you want to…..have your chuckle about how Oklahoma sucks and you know it does cuz that is the only thing that keeps Texas from falling into the Gulf of Mexico.
I like that joke btw…..BUT!
The fact is, I will be learning more about SQL in one week…..in Oklahoma…...than I have in the past 10 years.
<beer 2…..you know you chugged the first one right after you saw “Oklahoma” and “Texas”>
On a personal level, I have had zero use for SQL. For that matter I have had zero use for databases of any kind for the last 10 years or so. I used to have an Access (95) DB years ago. The only reason I had that one was to make an attempt at moving my fantasy football league over from AppleWorks and my Apple //gs to my brand spanking new AMD 486–120 (I still have that machine and I still use it now and then….it posts and boots to a Win95 OSR2 desktop faster than most of my gHz boxes get to WinXP) ((I also have my Apple //gs still which comes in handy for playing Wizardry!))
Wow…..I feel like I just jumped into the way-back machine with my pet boy, Sherman…..but I digress….
I tried to port my fantasy football league over from AppleWorks (which I loved) to Access (which I hated). I didn’t know I hated it until after trying to figure it out. I never did get comfortable with Access but I blame the simplicity of using AppleWorks on that.
Now I find myself in the position of having to learn SQL for my job….which isn’t a bad thing. It’s not like I haven’t been in a similar position about every two weeks for the last 8 years with the monstrous number of products and technologies we churn out. I believe anyone at Microsoft will tell you “If you don’t like learning new stuff, you work at the wrong company”.
I have just never had a use for or had to learn SQL…..till now.
In 1994, I never imagined that a database application – one that could essentially run on a home PC – would be capable of storing and querying the massive amounts of data that SQL can today. Of course, in 1994 I never imagined that I would need more than 200 megs of hard drive space or that my 5 1/4 floppy would be useless in just a couple of years. Back then, it would take my Apple //gs (which had an Applied Engineering TransWarp GS Accelerator) 30 minutes to parse the AppleWorks database and create the scoring report for my 10 team fantasy football league. It took well over an hour if I turned the TransWarp “off”. That database only held a few hundred players with weekly – not cumulative – stats in it. Today www.fanball.com supports hundreds of individual leagues, each determining their own scoring method, players rosters, play off schedules and ALL with the ability to provide immediate scoring information.
And that is just the football portion of their site. They also do baseball.
All that accessibility……all of those stats…….all of that FOOTBALL!!!!!….because of a database….
<beer 3…..come on…..we have a football related topic here>
*Note* – I don’t know what database Fanball uses…..for all I know they have linked a couple thousand TransWarped Apple //gs machines together and have Wozniak on the payroll….The point I am trying to make it how far we have come……
Now…..I was semi–advanced back then. On my Apple //gs I actually had two 5 1/4 floppies AND two 3.5 floppies. I even had two SCSI hard drives but I used most of the hard drive space for my BBS. The fantasy football stuff was pretty small and I wanted to make use of the 5 1/4’s as long as I could so I ran AppleWorks from the 3.5’s and stored the football stats on the 5 1/4’s. I learned a few tricks in those days…..you could notch the floppy disks with a $5 disk notcher (or a pocket knife if you were lazy) and use both sides. This allowed me to use both sides of the floppy – doubling storage capacity – AND it saved me money which I desperately needed for beer. But it always seemed to reduce the reliability of the floppy disks. It certainly shortened their life spans. But even on an un-notched disk, sneeze the wrong way and those old floppies would scramble data faster than a Waffle House cook does eggs. That meant manually backing up data to another floppy. I usually made yet another copy just in case (I almost always sneeze in pairs…) THAT was how I backed up my data and guaranteed it was accessible when I needed it.
Now, with SQL 2005, we have things like log shipping and peer-to-peer replication. Magical, wondrous, computer voodoo to someone who manually made 3 copies of their data just in case it was allergy season.
But my favorite way to guarantee that SQL 2005 guarantees data is protected (and ALWAYS available!) is - mirroring with automatic failover.
If, like me, you are new to SQL/Databases (or just haven’t been exposed to SQL 2005 yet), let me offer a very simplified explanation of what is involved.
For database mirroring with automatic failover to work, you need….take a deep breathe…..relax……<grab your beer so you don’t commit alcohol abuse>…….three (3) SQL Servers……
<finish beer 4, grab beer 5 but don’t open yet…..>
….I know you winced. You may have blown beer out your nose. Some of you may have even decided to quit your job as a SQL DBA and (gasp!) become a SQL Developer…..
Let me finish and then I will tell you a little secret that may save you some beer money…..
….breathe……relax…..
<bathroom break>
<beer 5……nurse it>
Server 1 is the “primary” or “principal” database server. It is the machine you want up 24/7. It is the “all the eggs in one basket” machine. It is online and active and is the DB server that all of your applications are writing to and reading from. The apps have no concept of any additional servers in the mix.
Server 2 is the “backup” or “mirror” database server. It *must* start off with an exact copy of the primary database data. Simplest thing is to just restore a backup of the primary to this machine. There is some planning involved here to make sure this DB has the exact same data as the primary….but I only had you grab a 6 pack. For the 12 pack version come to a Live Technet briefing or find a local SQL 2005 Roadshow or SQL Launch Event or read this.
As our applications are reading and writing data on Server 1, we will commit the transactions at both servers at the same time (technically we don’t commit to the primary until we can commit to both at the same time). This can introduce some latency so monitor your network especially if you have bandwidth concerns. The trade off is availability and redundancy.
Server 3 is called a “witness” server. The witness server is essentially the innocent by-stander at the scene of the server failure crime. It’s purpose is to just keep an eye on both servers and determine if one or the other goes offline. if one goes off line it notifies the other that it is now the primary. If the original primary comes back it notifies the servers and we can switch roles again after data is back in sync.
So you decide to implement this solution. You go purchase a SQL 2005 Enterprise license. Cough of the same amount for the Backup server license, Then you grab SQL 2005 Express edition as a witness server.
<waitadamnminute….. beer muffs must have distorted my hearing>
Did I just say you could use SQL 2005 Express Edition as the witness server to a SQL 2005 Enterprise mirror?
Yes I did.
No need to have an Enterprise class server twiddling it’s thumbs and keeping an eye on two other servers. Use it’s little brother to do that job…..you can even fudge things a little and have a second instance on the primary or backup server act as a witness but if that dual-role server goes offline you won’t be failing over automagically. Just so ya know….ANY version of SQL 2005 can act as a witness.
How far have we come in just 10 years…..
<beer 6>
Enjoy at your leisure……
Cheers!