The DR Cloud: An Example

Disclaimer: The enterprise Infrastructure as a Service(IaaS) that I will be using is for offered by the company I work for. I have been there 10 years and I am proud of our product set so I may be a little bias. However, these steps should be similar for most IaaS offerings like that of Amazon, GoGrid and RackSpace. To learn more about the product that is demo’d from visit http://theenterprisecloud.com We also have a similar hour based service at http://vcloudexpress.terremark.com. All that said, pay more attention the concepts not so much the platform. This is not meant as a commercial.

In the first part, I detailed the benefits and reasons for DR in the cloud. I ended with a high level sample architecture.

In this post, we will look at an example of doing DR to the cloud with SQL Server. We will be using Database Mirroring but you could use home grown log shipping, replication, incremental nightly loads, or third party products that do block level replication to an offline instance. So lets get started.

Part one: Create the DR SQL Server

1. Hit the create server button in the web interface.

2. Select the OS and template. Note: If you want to use SQL Server Enterprise Edition, just use a Windows template and install SQL from an ISO. You will need to do this if you want to use asynchronous mirroring and compression.

3. Name and size the Server. You can skimp on the resources and add more RAM and VPU’s in the event of failover.

4. Set the network. It will need to be DMZ but we will lock it down at the firewall.

5. Organize as you wish.

 

6. Add disk space and power on.

Part two: Configure network access.

1. Set up an internet accessible port to the server. Do not fret. We are going to use a certificate and data will encrypted on the wire.

2. Create a node for the Mirror.

 

Part three: Configuring mirroring.

1. Backup your database and get it to your mirror. (I am just using RDP but if you DB is large, setup a more robust copy method)

2. Restore the database with norecovery at the mirror.

3. Send diff(if needed) and at least one log.

4. Restore diff(if needed) and the log with norecovery

5. Set up database mirroring on port 1521 using a certificate.

Please refer to this article for directions on how to set up mirroring with a cert.

Disaster Recovery in the Cloud

Disclaimer: The enterprise Infrastructure as a Service(IaaS) that I will be using is for offered by the company I work for. I have been there 10 years and I am proud of our product set so I may be a little bias. However, these steps should be similar for most IaaS offerings like that of Amazon, GoGrid and RackSpace. To learn more about the product that is demo’d from visit http://theenterprisecloud.com We also have a similar hour based service at http://vcloudexpress.terremark.com. All that said, pay more attention the concepts not so much the platform. This is not meant as a commercial.

DR is important. However, it is worth a presence in a separate data center(DC)? That is a business decision that will not be covered in this article. Let’s say that the business cannot justify the cost based on the likelihood of the risk but they want to do something. In the past, your options short of a full DR presence ranged from offsite tapes to logshipping within the same data center to DR services like Sun Guard. All of these have shortcomings but if the business is ok with the risks and you set proper expectations on the level of protection and time to recover then that is all you can do.

Enter Cloud Computing(Specifically IaaS)

With IaaS, you can keep an online copy of your data in the cloud with minimal latency. The main advantage is cost and the savings can be huge. You do not have to invest in the infrastructure that goes along with a DR site like cabinets, network gear, and support services like AD, DNS, backups, etc. There is also the CapEX that goes with the actual server hardware.

The speed of implementation can also allow you to have many less virtual machines than you would have to have if they were physical machines. You can just keep a master copy of a web and each kind of app server. If you fail over to the DR cloud, you can spin up 10 or 20 of these really fast. Of course to have low latency with your data, your DB tier will have to be fully implemented.

This just a quick diagram of what you could do. In the event of catastrophe, you can flip DNS to the DR cloud in be up in much less time that not having a DR presence. In the second and final part of the series, we will look at the actual steps in setting up DR in the Cloud with SQL Server Database Mirroring.

The Biggest Cloud Computing Pitfall

So what is the biggest cloud pitfall? Security, you say? Nope. That is too big not to be solved and it is in everyone’s best interest to do so. Performance? Nope. That is too big not to be solved and it is in everyone’s best interest to do so. Ease of use\programmability\cost\color\flavor? Nope.

Imagine if you built your house but the fundamental design only allowed for “proprietary electric” from a specific utility *AND* this system was built into the foundation of the house. If this electric company provided craptastic service after they got you locked in like only providing power every other day, what are you going to do? You put up with it or you rip out your entire electrical system and put in another one.

Cloud computing = Utility Computing

Platform LOCK IN is the biggest pitfall for both consumers and providers on many different levels in my opinion. We will touch the surface in this post but use your imagination to see the possible implications because this chapter has yet to be written.

It can’t be that bad, can it? Is this “Scared Straight”?

Think of Apple before the iPod between 1995 to 1999. They were sucking wind BAD! Microsoft even had to invest in them. The general consensus of this period is the closed hardware platform is what hurt them so bad. 3rd parties did not legally release apple HW which lead to software developers to writing fewer Apple apps. The ecosystem was weaker and the PC has pwnd for the last 14 years.

On the flipside, Mac OSX has been able to focus. OSX has known hardware. It doesn’t have to worry about bad device drivers(unless they write bad code themselves). Eventually, those “in the know” figured out that pastels compute faster Macs do not blue screen. The ease of use and reliability of a closed platform became desired by some. Can you ride out that period of weak ecosystem and small community support while the cloud becomes solid as a closed system? The internet moves faster so maybe so. However, if you are a vendor, listen to this, otherwise, your customers may pass you up for a more open system at that internet speed.

Yep, Calling out Microsoft and Google.

Software as a Service(SaaS) is a lock in by design. Ask someone who lives and dies by SaleForce.com. This is changing though. Now you can host your own, go with a service provider or go with the actual vendor with some of these apps. Some apps that were previously self hosted only are going SaaS. Exchange and Sharepoint for example. I am not talking about Microsoft or Google in this space. They are both competing and being portable in the cloud office app space. Kudos.

Infrastructure as a Service(IaaS) is open by design open. You run your app on whatever platform using whatever code. CPU\Memory\Disk\Network is the utility. If you do not like the service you are getting, you can change providers nearly as easy as electric companies.

Platforms as a Service(PaaS) is closed right now. It is why I write this evening. Right now, the big boys are Google App Engine and Microsoft Windows Azure. Both are in beta.

My Request

My simple request is release the platform for any company to use. This could be open source or commercial. This will stop the LOCKOUT mentality, decision makers will move to it faster if they know they can bring it in house if need be, 3rd parties will add on to it, and the baby jeebus will squeal with joy.

Google and Microsoft are currently holding out with the hopes that they can fill cubic miles of data center space. They could very well do this. However, I think the platform lock in is going to slow progress.

Full disclosure: I work for a company that is an IaaS provider but I do not be believe the opinion in this editorial is slanted because of that.

Private Clouds and the RDBMS

Private clouds are all the rage right now. The concepts in the post applies to all virtualization products and all of the major enterprise databases servers so I am going to stay vendor neutral. My experience is mostly with VMWare and SQL Server so you may have to convert to the terminology of your platform.

So what is a “Private Cloud”? Right now, it is simply a large virtual machine cluster with management software. I expect it to mature to include more value added software like content deployment, dynamic load balancing of guests and other % as a service. It is often used during a tech refresh and\or a consolidation project. You can almost take two datacenter rows of 5 year old hardware and get it into two racks of current hardware chopped into virtual machines.

Let’s say our private cloud cluster consists of 5 servers having 4 sockets & 6 core with 96GB of RAM. Each virtual machine gets 2 cores and 2-8GB of RAM. This gives us about 60 virtual machines. Some platforms allow for oversubscription but we will keep it simple.

Here is a simple logical picture.

 

As you can see that if you can replace 60 physical servers and get as good or better performance then this is a no brainer. You save on power, space, CapEx hardware costs, licensing(depending on platform), and administration.

However, you also have 10 various Database Servers. They range from mission critical to dev\QA boxes. Where do these belong? It depends.

You will always get better performance out of bare iron because there is some overhead with virtualization. Some workloads suffer more than others. For example, a CPU bound workload will suffer the most and an IO bound workload will suffer the least(assuming you can give the VM enough memory.) Another consideration is the size of a VM. A database server with two cores and 2GB of memory will see less of an impact of virtualization than an 8 core VM with 16GB of RAM. Adding cores to VM’s does not scale linearly. Having said all of that, it make sense to leverage consolidation features of the database server and use bare iron for most private cloud applications.

There are still good applications for private cloud database VM’s. Here are a few:

• Development and test environments
• Supporting legacy environments that have old RDBMS installations
• Support databases that require separation due to security or compliance
• Sandbox environment to prevent resource intensive databases from impacting mission critical databases.

Here is the racks again with the database servers.

The ability to mix physical devices for virtual machines is one of the biggest benefits of a private cloud. Most public clouds do not offer this functionality. At least not yet. Use this design to get the most out of your environment.

Note: While I am recommending physical hardware for the DB server, they do run well virtualized sized up to 4 or 8 cores. However, since we would most likely need more power than that in this scenario, bare iron would be the best.

The Bomb has been Dropped

SQL Data Services will be a relational database that has most of the features of SQL Server.

To quote David Campbell:

Tables?…Check

Stored Procedures?…Check

Triggers?…Check

Views?…Check

Indexes?…Check

Visual Studio Compatibility?…Check

ADO.Net Compatibility?…Check

ODBC Compatibility?…Check

Read about it here.

The New Version of SQL Data Services

Up late making some changes on a large SQL 2000 publication and hoping like hell that I do not have to push the full snapshot.

Anyway, just a link post but Dave Robinson dropped a real teaser on the forthcoming “rewrite” of SDS here. As always, Mary Jo Foley has the rumors. Pay attention to Mix which is #mix09 on twitter.

MVP Summit 2009 After Hours Part 1

What happens in Seattle doesn’t stay in Seattle. I could not take pictures during the day because <nda> and <nda> cannot be announced yet. However, database diapers is fair game.

Anyway, here is part 1. Video next.

 

Click.

Here is the link the the original post if it doesn’t syndicate right.

Lessons Learned with SQL Server on VMWare

This past weekend I asked the community(or at least my twitter feed) what they thought about virtualized database servers. Lots of people use it in DevTestQA. Some in prod. Some thought it was pointless. Before I go over my lessons learned, let me address the “pointless” point.

It could be argued that it adds another unneeded level since you could do this with instances. For that matter, you can use one instance and permissions. This is true. If you are consolidating just a few instances, the cost and overhead probably doesn’t justify virtualization. However, there are other benefits like VMotion or adding capacity horizontally. You can’t easily move two instances off an over utilized box. With ESX, you can do this online while transactions are in flight. Added redundancy is also a benefit.

So here are my lessons learned:

Beware of CPU bound workloads

Most database workloads are IO bound even if it is logical IO’s. However, if your database is really small but you do lot of complex calculations, lots of business logic or string manipulations etc, your performance may suffer more than you would expect.

Use x64

This is just a vendor recommendation that suggests a 10% gain.

Don’t trust the CPU counters

Another thing I learned from the whitepaper. The CPU ready counter in Virtual Center is very useful.

Set a memory reservation

I would at least reserve half of the memory given to the guest especially if the host is busy. ESX has a balloon driver that will take memory from guests if it needs it and it thinks the guest is idle. This usually is not good for database servers.

The Microsoft Windows Server 2003 Scalable Networking Pack is evil

This is enabled by default with Windows Server 2003 sp2 and it doesn’t play well with SQL Server, VM’s and especially SQL Server on VM’s. See this post.

You have to sector align TWICE

First you have to sector align the vmfs and then at the OS level. Here is VMWare recommendations. I agree with Kendal’s recent finding of 128k offsets and 64k NT allocation unit based on my own testing.

If you have a lot of SQL VM’s on a host, see if EE makes sense

With Enterprise Edition, you only have to license the host. If you use standard edition, you must license each guest. If no EE features are needed, “lock pages in memory” alone, may be enough. In addition to other problems it solves, you can enable large page support.

Bad code usually runs worse on a VM

Man, the weather has been nice. How about them cowboys!

 

Anyway, do you have any lessons learned running SQL on VMWare..

Community Thoughts on Database Server Virtualization

Trying out a new tool from Chris Pirillo called Twickie. It basically lets you blog one of your tweets and all of the replies. It missed about half of the replies but it is a nice idea and hopefully it gets better.

I posed this question to my Twitter followers on a late Saturday afternoon. I was happy with the results. Keep in mind that most of my followers are Microsoft centric DBAs, developers, admins and general geeks. I will try this again with a different question Monday when my tweeps are paying more attention. Only the geekiest of geeks are perusing Twitter(or blogging) on Valentines’ Day. See below

Credit to Scoble for the idea.

I will post tips and tricks I have have learned running SQL Server on ESX tomorrow.

 

 statisticsio: Hey you. Yes, you. Are you virtualizing your db server? In prod? How big is it? Pitfalls? Winfalls? ROI? Performance? Pro’sCon’s?

update:

1. crisatunity @statisticsio the problem I see with db on vm is that all of the db products are geared in very specific architecture not to be on vm.about 2 hours ago from web in reply to statisticsio
    

2. jmkehayias @statisticsio almost all of our production servers are VM based for DR scenarios. I have physical servers for dev. How backwards is thatabout 3 hours ago from TwitterGadget in reply to statisticsio
    

3. Efelito @statisticsio and as @mrdenny said, all dev, qa, and uat are very happy in VMs.about 3 hours ago from web
    

4. Efelito @statisticsio works well for us on instances with low memory requirements SCOM, VCenter, SharePoint, and SSRS Have more detail if you wantabout 3 hours ago from web
    

5. SQLDBA @statisticsio virtualizing, yes…but not in prod, only in dev & stage. Very easy to spin up new servers for developers.about 4 hours ago from twitterrific in reply to statisticsio
    

6. idolan @statisticsio Ours has been virtual about 10 mos. Suspicious there may be a performance hit but maintenance advantages are a big win.about 4 hours ago from web in reply to statisticsio

The Doomed DBA(or The Sky is Falling)

Tony Bain(RSS, Twitter) has written a very nice article comparing relational databases to “cloud” databases. Not only is it well written, it has a HUGE audience at ReadWriteWeb. Props Tony. Suggested reading for Database Professionals.

The full article can be found here. Here is one thing you should see this even if you do not follow the link.

Next Page »

• My RSS

  

• 

• My LifeStream

• My Presence

• My Friends

• My Games

  
  

• Recent Topics

  • Imagine a 3500 Page Book…
  • What is Professional Development?
  • Filtered Stats to Counter Data Skew Issues
  • Sampling Problem with Filtered Statistics
  • Life
  • Professional Development: Internet Image
  • OT: Music – What the Fork?
  • The DR Cloud: An Example
  • Can you be too Thorough?
  • Disaster Recovery in the Cloud

• My Cloud

  • automagic bad practices beta Blogging bugfix Capt. Varchar certification cloud Clustering codeplex community conferences Crybaby cxpacket Data collector Database Mirroring DBA disaster recovery DMV's DMV’s editorial Feature pack filtered indexes flamebait friendfeed fud geek geek pr0n google reader HA Hardware Heroes Happen Here humor IaaS indexes lamp lifestreaming MCITP meme memory Mirroring Mixed Workloads music mysql NTSSUG Off topic Offtopic OLTP opml Oracle pbm performance powershell procedure cache professional development profiler query optimizer query tuning RC0 RDBMS Reporting rss rumors SAN scale out vs. scale up scaling screencasts SDS Security social computing SQL Connections sql heros sql injection sql pass SQL Server 11 SQL Server 2000 SQL Server 2005 SQL Server 2008 sql server web edition SQL shorts SQLCLR sqlheros SQLPASS sqlps SSD ssms SSRS Statistics teched tsql twitter Upgrade utility computing Visual Studio 2008 VMWare Windows Windows Server 2008 WMI x64 xquery