Category Archives: Virtualization

Virtualization

Read-Only View Permissions and Roles in VMware vSphere

Written on December 6, 2011 by in SQLServerPedia Syndication, Virtualization

When Ronald Reagan said, “Trust, but verify,” he was talking about DBAs working with their VMware sysadmins.

For the record, he didn't really trust the Soviets either.

"Trust, but verify." Ronald Reagan

As a consultant, I get to see a lot of SQL Server implementations – both successes and failures.  The successes have one thing in common: transparency.  The database administrators, VMware admins, and storage admins have clear, open discussions about the way their respective systems are configured.  They give read-only access to other teams so everyone can double-check to make sure everything is working well.  After all, these teams share a common goal – fast, reliable applications for end users.

You can easily get read-only access to the VMware vSphere Client, the tool VMware admins use to manage your virtualization environment.  Here’s how:

1. Walk over to your sysadmins and show them this blog post.

This is the hardest step in the entire process.

The rest of the steps are simple and predictable: click a button, get a result.  This step, however, involves complex, unpredictable meatbags.  Soft skills are the hardest one to master, and finessing this is outside of the scope of this particular article.  Start by brushing up with my Consulting Lines series.

You need to walk over there because you’re asking for access into their domain.  This implies that you don’t trust them.  You do, but like Reagan said, you just need to verify.

2. Open VMware vSphere, Home, Hosts and Clusters.

The vSphere Client is a thick front end that connects to Virtual Center, a service that keeps an eye on your hosts and guests.  In the vSphere Client, click Home, Hosts and Clusters.  Here’s a screenshot of what you’ll see:

Hopefully yours won't say Lab.

VMware vSphere Client

On the left side, click on the thing you want access to – which brings up the first question for your VMware admins: what do you need read-only access to?  Your VMware sysadmins may have built a separate cluster just for SQL Server use, or your VMs may be intermingled with other virtual machines.

On the right side, click the Permissions tab, right-click, and click Add Permissions.

3. Add the DBA domain group.

In the Add Permissions popup, click Add.  In the dropdown for domain, choose your Active Directory domain, and in the Users and Groups dropdown, click Show Groups First.  Your screen will look like this:

Active Directory Groups in VMware

In my lab, I’ve created an Active Directory group called “Database Administrators” that includes me, Jeremiah, Kendra, and Tim.  That way, anytime I need to grant additional permissions for the DBAs (like when I build a new SQL VM that they’ll need to administer), I can just use that domain group instead of individual user accounts.

Click your own DBA group’s name, click Add, and click OK.  On the right side of the Assign Permissions window, the Read-Only permission will be defaulted, and that’s fine – click OK.  Presto, you’re in.

If you click on child objects in the left tree (like one of your SQL VMs) and click the Permissions tab, you’ll notice that your read-only permissions have propagated to this object too.

4. Install the VMware vSphere client on your desktop.

The client is a free download from VMware.com as part of other products, but the easiest way to get it is to simply point your web browser at one of your in-house VMware hosts.  In my lab, here’s what that looks like:

Downloading vSphere From Your VMware Host

Click the Download vSphere Client link and the installer will come flying through the tubes.  Yes, the installer includes Visual J#, and yes, I laugh at that too.

5. Launch the vSphere Client and start poking around.

After the installation finishes, double-click the VMware vSphere Client icon.  You’ll need to enter the name or IP address of your Virtual Center server – ask your sysadmins for that.  Check the box labeled “Use Windows session credentials,” and thanks to the magic of Active Directory, you’ll be poking around in virtualization in no time.  (Actually, it takes about 30 seconds to launch the dang thing.)

To learn what you’re looking at, consider watching my Tuning SQL Server on VMware session:

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

1 Comment - Leave a comment!

Upcoming Webcast About Backups, SQL Server, and VMware

Written on July 8, 2011 by in SQL Server, Virtualization

Next week, I’m doing a free webcast called The Top 5 Mistakes in Backing Up Virtual SQL Servers.

Us SQL Server people think we know backups better than anybody else. We know to do full backups daily, transaction log backups for mission-critical stuff, and maybe even differential backups if we want to get fancy. We schedule it all through SQL Agent, and we know we can count on our restores. We don’t need to do anything different with virtualization, right?

Wrong.

Join me as I explain why backing up virtual SQL Servers really is different, including:

  • Why full backups are one of the worst things you can do to a virtual SQL Server
  • Which critical SQL Server objects you’re probably not backing up today
  • Three different meanings of the word “snapshot”, and which one doesn’t suck

Register for the webcast here.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

12 Comments - Leave a comment!

Top 10 Keys to Deploying SQL Server on VMware

Written on May 31, 2011 by in SQL Server, Virtualization

The most successful deployments of virtual SQL Servers have a few things in common.  When a company is doing all of these things, odds are they’re going to be very happy with their virtual SQL Servers:

10. Use vSphere 4.1, not earlier versions. vSphere 4.1 introduced major improvements in multi-CPU scheduling that vastly improve performance.  SQL Server will break queries out across multiple virtual CPUs, and it expects all of those vCPUs to perform identically.  vSphere 4.1 helps make that happen.  You can read more about the vSphere CPU scheduler here.

Lacie IAmAKey Drive9. When using blades, avoid overcommitting switch uplinks. Virtualization works great with blades – but only when the SAN and network teams monitor utilization rates between the blade chassis switches and the rest of the networks.  All too frequently, the SQL Server is thrown into a blade chassis with dozens of other servers all competing for the same small amount of storage and network bandwidth leaving the blade chassis.  Everyone looks at their monitoring systems and can’t understand why the backups, big queries, and maintenance jobs run so slow, and the culprit is the hidden small uplink.

8. Avoid 1Gb iSCSI for storage. While this cheap and easy-to-use storage works great for most servers, it doesn’t work as well for SQL Server.  If you’re happy with the speed of SQL Server running on local storage, you can be in for a rude surprise with 1Gb iSCSI.

7. Test storage performance before deployment. I’m partial to SQLIO, which might be the worst-named tool in history.  It has absolutely nothing to do with SQL Server – it doesn’t require SQL Server, and it doesn’t mimic SQL Server.  It just flat out hammers IO using whatever parameters you pass in.  Using a test file of the same size as the expected database, the storage should consistently exceed 100MB/sec whether it’s random or sequential, reads or writes.  Higher numbers than that may be required due to the application design or end user expectations, but that’s a good foot-in-the-door number.

6. Compensate for slow storage with memory. If you can’t get the storage performance you need, you can help by caching as much of the database as possible in memory.  If the projected database size is 50GB but the virtual server only has 16GB of memory, then it’s not going to be able to cache the entire database.  Perhaps the users won’t be querying old data, in which case you might be able to get by with less.

5. Ensure CPU power saving is turned off. While Intel’s latest Xeon processors provide impressive power savings, they won’t ramp up to full processor speed unless the CPU is under heavy load.  SQL Server will rarely push the processors that hard, which means they stay slow – sounds good in theory, but in reality, every query takes 70-100% longer than it did pre-virtualization.  If you care about query performance, turn this setting off in the hardware BIOS.  You can read more about the power-saving CPU issue here.

4. Coordinate VMware reservations and shares with SQL Server settings. VMware vSphere has a great set of options to ensure that a guest OS gets the right amount of resources for its needs.  These settings need to be set in concert with SQL Server’s min and max memory settings.  There’s plenty of bad advice out on the web saying things like, “Just disable the balloon driver and give SQL Server all the memory” – that’s not right either.  There’s a happy medium in letting SQL Server and VMware cooperate to balance resources across multiple guests, but it only works when these settings are aware of each other.

3. Use Resource Pools to track SQL Server licensing. Microsoft’s virtualization licensing for SQL Server is notoriously complex.  Starting with SQL Server 2008R2, only Datacenter Edition ($60k/cpu socket) provides unlimited virtualization rights.  Enterprise Edition ($30k/cpu socket) provides just four VMs.  Tracking these closely with VMware Resource Pools can result in huge cost savings.  If SQL Servers are allowed to move to any host in the VMware cluster, then a licensing audit can produce staggering costs.

2. Use VMware HA for high availability. If your users can tolerate a SQL Server outage of 2-3 minutes, VMware HA is much easier to manage than a SQL Server cluster.  If your users require less than 30 seconds of downtime, consider implementing a physical SQL Server cluster instead.  SQL Server clusters are tricky enough to manage on their own, and doing them inside VMware adds an impractical level of management on servers that can’t be down for more than 30 seconds.

1. Virtualize small SQL Servers first. Start by gaining experience with 1-2 vCPU servers with under 16GB of memory.  As the company’s sysadmins grow accustomed to how SQL Server uses CPU and memory in a virtual environment, they’ll be more confident virtualizing larger servers.  If the DBAs and sysadmins don’t get along when trying to pin down performance problems on smaller servers, they’re going to be very adversarial when dealing with larger servers.

Wow – That’s a Demanding List!

I know what you’re thinking: “This outside consultant is greedy and expects everybody to dump tons of money into their SQL Servers.  He’s asking for the moon.  Nobody does this in real life.”

Let’s rewind back to the beginning of the recommendations where I said, “When a company is doing all of these things, odds are they’re going to be very happy with their virtual SQL Servers.” You can certainly skimp on some of these items and still stand a pretty good chance of being happy with your SQL Server performance.

The more items you skimp on, the worse your chances become.  If you implement a virtual SQL Server with 32GB of memory trying to cache 250GB of databases on RAID 5 storage, hooked up via 1GB iSCSI, with no prior experience virtualizing SQL Servers of that size, odds are you’re going to be miserable.  Users will scream and complain, and you’ll bring in an outsider who will track the problems back to these types of recommendations.  Keep my contact info handy.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

41 Comments - Leave a comment!

How I Make Coffee and Servers

Written on September 15, 2010 by in PASS Virtualization Syndication, SQL Server, Virtualization

I’ve got a disturbing array of coffeemaking gear:

My Coffeemakers

My Coffeemakers

From left to right:

  • Bialetti Moka – stovetop espresso maker.  Great for making powerful hot chocolates in the dead of winter.
  • Burr grinder – the best way to get very consistent grinds.  The grinders with spinning blades just randomly slice and dice your beans, but burr grinders get all of the pieces exactly the same size.  Probably the best $100 I ever spent to improve the quality of my coffee.  I linked to a different one since mine’s discontinued.
  • French press – produces very flavorful coffee, but it’s a complete pain in the rear to clean.  They have to go through the dishwasher after each use.  Since it doesn’t have a built-in heating element, I have to microwave water first, and the resulting coffee isn’t as hot as I’d like.
  • Krups Moka Brew – the easiest way to brew something that tastes like an Americano, which is an American-style coffee made with espresso and hot water.  Pretty easy to clean, produces a lot of coffee at once, and it has a built-in heater.  That’s a pro and a con – it means the coffee can taste burnt after it sits on the hot carafe for a few minutes.
  • AeroPress atop a Microsoft mug – the AeroPress looks like a gimmick when you first see it, but it produces the very best coffee I’ve ever had outside of a cafe.  Nothing else in my arsenal even comes close.  Big props to Adam Machanic (Blog – @AdamMachanic) for telling me about this.  Fast brew times, absurdly easy to clean, great coffee – but only one cup at a time.

The AeroPress produces by far – by FAR – the best coffee, but when I get up in the morning, I fire up the Krups Moka Brew and make a pot.  I sacrifice flavor for ease-of-use, because I need more than one cup and I’m trying to get a lot done in a short amount of time.  I need to feed the dog, take her for a walk, check my emails, cook breakfast – okay, well, assemble breakfast, which usually consists of granola and yogurt.  I want to brew enough for me to have a couple of cups, plus Erika needs a couple of cups, plus a cup to-go for work.

Every now and then, in the afternoon, I’ll brew a cup of coffee with the Aeropress and savor every sip.  The rest of the time, though, I just can’t afford to micromanage my coffee one cup at a time to get it absolutely right.

That’s Why I Love Virtualization, Too

Most of the time, I need to get a database server up and running as easily as possible.  I don’t want to completely sacrifice all semblance of security, performance, and manageability, but I don’t have all the time in the world.  I can’t usually fine-tune every hardware setting like SAN multipathing, network card load balancing, failover protection with clustering, and so forth.  I just need a core set of basic hardware up and running to service the company’s needs.

I deploy a virtual server with a few mouse clicks, and presto, we’re off and running.

If I need high availability, disaster recovery, snapshot backups, or any number of common datacenter features, I just tell my virtualization sysadmin, and they’re handled.  Even better, they’re handled exactly the same way across all of the company’s servers, which makes management easier.  Don’t get me wrong, I still double-check what they do – like Ronald Reagan said, “Trust, but verify.”

When I need to wring every last bit of flavor performance out of a SQL Server, then sure, I build a finely crafted physical server for it.  But the rest of the time, I use virtualization by default, because it’s gotten good enough.

I’m kicking things off on my SQLskills blog with a virtualization series.  Visit me there, or subscribe to my SQLskills RSS feed, or subscribe to my SQLskills posts via email.  Enjoy!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

16 Comments - Leave a comment!

Register for SQLBits in England – Space Running Out!

Written on September 6, 2010 by in PASS Virtualization Syndication, SQL Server, SQLServerPedia Syndication, Virtualization

If you deal with VMware, Hyper-V, SANs, and SQL Server, come check out my pre-conference session at SQLBits in York, England on Thursday, September 30th.  My all-day session is called, “Virtualization, SAN, and SQL Server: The Perfect Storm.”

This will be a fun, intimate session – it’s limited to 40 people max, and we’ve got a few spots left.  Here’s the abstract:

Virtualization, SAN, and SQL Server: The Perfect Storm

Each of these things alone is tough enough to manage, but when you start to combine them, the forecast calls for rogue waves of bad performance.  Metrics like CPU % and disk queue length just don’t mean anything anymore.  No matter what numbers you hand your virtualization sysadmins, Windows admins, or SAN admins, they all say the same thing: “It’s a SQL Server problem.”

Learn how to fight back as Brent Ozar, Microsoft Certified Master, exposes some surprising truths like:

  • What metrics can prove it’s not a SQL Server problem
  • Why you might not want to separate your data and log files onto different drives
  • How to double-check to make sure the other guys are doing their job
  • The crucial configuration choices that can doom your SQL Server performance from the start

By the end of this session, you’ll be able to walk back into your office, gather statistics and configurations, and give your sysadmins and SAN admins the information they need to fix performance problems on their end.

For £450, you get my all-day pre-con session on Thursday plus full days of training on Friday and Saturday too!  Check out the SQLBits agenda, which includes sessions like:

  • Understanding SARGability – Rob Farley is one of the funniest guys you’ll ever meet, and his sessions get rave reviews.  Once you understand SARGability, you’re much more equipped to troubleshoot slow queries.
  • SQLDiag and SQLNexus – these two completely free tools will change the way you do performance tuning.  Microsoft Certified Master Christian Bolton, the lead author of Professional SQL Server 2008 Internals and Troubleshooting, will explain how to use ‘em.  His company, Coeo, is even giving away a free Fusion-IO drive during SQLBits!
  • Introduction to Extended Events – this new tool in SQL Server 2008 doesn’t get much press because it’s a pain in the rear to get started with.  Jonathan Kehayias is the man to show you how because he wrote the free SSMS Extended Events Manager, a tool that’s been featured in SQL Server Magazine.

This event has speakers from around the world including Brad McGehee, Buck Woody, Kevin Kline, Thomas Kejser, and more.  This is the cheapest way to see the best people in one place in Europe, and it’s in a fun, relaxed atmosphere where you can have plenty of discussions with the speakers.  To see what the shorter sessions look like, check out one of my sessions from my last SQLBits:

Register now and choose Full Conference, and you can pick my session as your pre-con.  Register fast – we’re running out of seats!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

6 Comments - Leave a comment!

SQL Server 2008 R2 Review

Written on May 13, 2010 by in SQL Server, SQLServerPedia Syndication, Virtualization

In August 2008, Lady Gaga released her premier album The Fame. This 13-track work produced several hit singles including Just Dance, Poker Face, and Paparazzi – but it also produced plenty of controversy. Her exaggerated use of fashion, musical cliches, and sex gave her plenty of media attention – both good and bad. If you’ve never heard one of her songs or seen a video, the first 30 seconds of Poker Face will give you a pretty good idea of her style (musical and otherwise). Actually, you don’t even really have to hit play – the first frame showing her poolside between to two Great Danes probably gives you a pretty good idea where things will go next.

Anybody can get famous doing an Internet donkey Great Dane show. The big challenge for suddenly-famous artists isn’t the first release, though – it’s the sophomore release. The second album shows if the star has the potential to keep cranking out new material, or if they drop off the face of pop culture as a flash-in-the-pan.

A little over a year later, Gaga released a followup album called The Fame Monster, but there’s some debate over whether it should even be considered her sophomore work. Due to the short time between releases, its shorter length (only 8 tracks), and its sales method (often packaged with The Fame in a single box), it was seen as an extension of her debut album. This effort didn’t win over any new fans; if you didn’t like The Fame, then The Fame Monster wasn’t going to turn you into a Little Monster, as Gaga fans are known.

It’s a lot like SQL Server 2008 and 2008 R2.

Microsoft SQL Server 2008: The Fame

It’s impossible to review this month’s newest release from Microsoft, SQL Server 2008 R2, without reflecting back on the recent release of SQL Server 2008. R2 comes quickly on the heels of 2008 and tells more of the story that was first started in 2008.

SQL Server 2008′s hit singles for production DBAs included:

  • Backup compression – this instant-winner of a feature has been available in third party software like Quest LiteSpeed for years, and 2008 Enterprise Edition users got it built into the engine.
  • Transparent Data Encryption – if you need it, you desperately need it. TDE makes it ridiculously easy to protect your company’s data from headlines about stolen backup tapes.
  • IntelliSense – wildly popular at first, but quickly fell off the charts as users discovered it didn’t work with previous versions of SQL Server.

The less popular B-side songs were:

  • Data compression – like backup compression, this feature can give an instant performance benefit under certain circumstances. Even better, the data is only compressed once, and then all backups are smaller and faster without repeated CPU work. I think this is the most underrated song in the album, although filtered indexes & statistics are a close second.
  • Policy-Based Management – a cult favorite that hasn’t managed to crack the charts for widespread adoption. Everybody’s heard about this song, but nobody knows the words.
  • Resource Governor – originally touted as the ability to stop runaway queries or ease consolidation pains, this didn’t actually work as intended due to its inability to throttle IO. Storage is usually SQL Server’s biggest bottleneck.
  • Spatial data – programmers have worked around the challenges of storing latitude/longitude data in RDBMS’s for years. With support built into the engine, now developers could use spatial indexes to execute tough queries much faster.
  • Filestream storage – answering a question DBAs everywhere kept asking, “How can we make our databases larger and more difficult to back up?”

The release was chock full of upbeat songs, but the beats were just a little too tricky for mainstream dance clubs. SQL Server 2005 was good enough, and companies didn’t have the money to roll through quick upgrade projects, so I didn’t see a widespread adoption of 2008. In the recent PASS Summit poll, 52% of responders said less than half of their servers were SQL Server 2008. I haven’t heard anyone say they dislike SQL Server 2008 – they just didn’t care quite enough to deploy it to every server in the shop.

SQL Server 2008 R2: The Fame Monster

Real music critics seem to have a somewhat positive view of The Fame Monster, giving it 75 points out of 100 on Metacritic. Gaga clearly resonates with the purchasing public, having earned over 17 platinum records as of this writing. People between those two extremes have a much more negative reaction, however – my music-critic friends (people who think they know a lot about music, yet don’t have jobs in the business) uniformly hate her. They say she’s low-brow, populist, and oversexed.

SQL Server 2008 R2 will have a similar reputation in the database world. Analysts, the IT industry’s equivalent of music critics, love SQL Server. On the other extreme (paying customers), Microsoft sells the heck out of SQL Server, so it seems to be popular with the folks with money. People in the middle are more divided, though – I hear plenty of people on other platforms referring to SQL Server as not good enough for all kinds of reasons. It’s too expensive, it’s not full-featured enough, it’s too-full-featured (yes, that’s the NoSQL claim), it’s not open source, the list goes on and on.

People who liked SQL Server 2008 will like 2008 R2, but for different reasons. It brings some really new material to the party, albeit with exactly the same theme as 2008.

PowerPivot Caters to the Paparazzi

Lady Gaga Fan Art

She's no Donald Farmer, but she'll do

Microsoft used sex to sell SQL Server 2008 – and that sex was Business Intelligence. BI is by far the sexiest topic for businesses today. It’s in every magazine, every book, and companies are dying to figure out how to get more actionable information out of their raw data. SQL 2008′s material read like a pinup calendar – “We’ve got all your forms of data right here, big boy! C’mon over and analyze me.”

R2 takes the sex appeal even further – BI isn’t in the hands of pros anymore, but rather amateurs. Any sleazy middle manager with a fetish for data can whip out an Excel spreadsheet and get his PowerPivot on, doing multi-million-row analysis on his laptop.

This high-quantity, low-quality analysis comes with its own challenges. Who rates the data? Are you sure the numbers in that loose schema are right? Is that juicy profit figure really profit, or a big, fat, hairy loss masquerading as something else? Who let that underage data into the corporate report? And in the middle of a steamy analysis session, does anyone even care?

PowerPivot doesn’t appeal to buttoned-down database administrators accustomed to pure, cleansed data stored behind locked doors, but that’s not the point. It sells to managers and executives who want fast, easy access to decisionmaking numbers, and they’re going to love it – especially when combined with SharePoint.

StreamInsight: It’s Money Honey

Packaging StreamInsight in the SQL Server box will be controversial, but the feature is great for developers. StreamInsight helps developers analyze massive amounts of incoming data – think stock trades, Twitter, or clickstream data for web sites. Developers can then build rules (no, not with T-SQL) to handle and parse that data.

Notice how I said “developers” three times in three sentences?

StreamInsight doesn’t really have anything to do with SQL Server. The coding is done in Visual Studio and the data doesn’t go through the SQL Server engine. SQL Server just happens to be a really expensive box – especially the Datacenter Edition, where StreamInsight is best used – and Microsoft needs to monetize this product. Presto: StreamInsight becomes part of SQL Server.

Database administrators can safely ignore this feature because they won’t be deploying it, managing it, backing it up, etc. CIOs, however, need to pay close attention to what features are bundled into what editions, because buying SQL Server is getting a little more complicated.

CIOs are Seeing Microsoft’s Poker Face

My poker face, let me show you it

My poker face, let me show you it

SQL Server 2008 R2 brings a new top-end SKU, Datacenter Edition. Microsoft breaks down features by edition, but here’s my simplified explanation:

Express Edition (Free) has:

  • Databases up to 10GB
  • Completely free

Standard Edition (roughly $6k/socket) adds:

  • No limits on database size
  • Backup compression

Enterprise Edition (roughly $30k/socket) adds:

  • Up to 8 CPU sockets
  • Management features for up to 25 instances
  • Virtualization rights for four guests – on a two-socket server, you can buy two sockets of SQL Server 2008 R2 Enterprise Edition, then run four virtual servers on there with SQL Server.
  • Data compression, online index rebuilds, and other engine features that help with databases over around 100GB

Datacenter Edition (roughly $60k/socket) adds:

  • Unlimited CPU sockets
  • Management features for over 25 instances
  • Unlimited virtualization rights – on a two-socket server, you can buy two sockets of SQL Server 2008 R2 Datacenter Edition, then run an unlimited number of virtual servers on there with SQL Server.

With R2, prices went up, and I’m totally okay with that. I’m not a Microsoft shill here – I think the market has changed, and Microsoft’s prices needed to reflect it. Virtualization is on fire. Microsoft has to price products not just for today, but for now through the next release. Intel’s packing more and more cores per chip, Cisco’s got blades that handle an insane amount of memory, and I routinely see virtualization hosts with more than 4 guests on them – usually several times that many. It’s not inconceivable that we’ll see hosts with more than 4 virtual SQL Servers on them soon as well, and Microsoft licensing revenues would plummet.

The price increase also sort of reflects the new tools like PowerPivot and StreamInsight – but only kinda. Some of the new features don’t have polish, and there’s a risk of them being one-and-done.

I Like It Rough: DAC Packs

Maybe not that rough.

Maybe not that rough.

The iTunes release of Fame Monster includes a bonus track, “I Like It Rough,” that depicts a relationship where both sides do a lot of giving and taking. It’s insanely catchy, but with the subject matter, I can see why it didn’t get widespread release.

DAC Packs are R2′s bonus track. I’ve written about DAC Packs before, and I believe they have an awesome long-term potential. The version shipped in R2 is very rough for all kinds of reasons – it does side-by-side deployments (copying your entire database just to change a single stored proc), it doesn’t support a lot of database features, there’s no tooling whatsoever for DBAs, and it does a worse job of just about everything than Data Dude did. (And nobody bought Data Dude, either.)

Microsoft says that DACs are a down payment on a bigger vision – and I do believe in that vision – but Microsoft has a pretty bad credit score. They make an awful lot of down payments, and then when the first monthly invoice comes in, they seem to have left their wallet in their other pants. Want to see a credit check?

  • SQL Server Notification Services
  • English Query
  • DTS (how do you like rewriting all those packages into SSIS? Good times, eh?)
  • HTTP SOAP access (I’d almost forgotten about that one, but it was even one of Microsoft’s Top 10 Features for DBAs in 2005. Quote: “…eliminating the need for a middle-tier listener, such as IIS, to access a Web services interface that is exposed by SQL Server 2005.”)
  • Vardecimal (yes, we actually had a one-and-done datatype)
  • Service Broker (yes, it still works great, but there haven’t been any investments here whatsoever)

Ouch. That’s a lot of things they bought on layaway, and then never paid for.

In my Top 10 Developer Mistakes That Won’t Scale presentation, my #1 thing to avoid is relying on v1.0 features. Sometimes Microsoft follows through and improves the feature over time, like SQL Server Integration Services. Sometimes, though, they forget their wallet and look the other way. When I’m making architectural decisions on where to invest programmer resources, I follow Microsoft’s own lead – if they didn’t care enough to invest the time it takes to do something right, then I won’t invest my own time in it either. Let’s see if they make the next payment first.

The old adage used to be, “I won’t deploy it until Service Pack 1, because there might be bugs.”

Now I tell my developers, “Don’t code against it until V2, because there might not be one.”

Like Fox Mulder, I want to believe, but Microsoft needs to pour a whole lot more work into DAC Packs – fast. Otherwise, server virtualization is going to bypass database virtualization as a better way to solve management problems.

SQL Server 2008 R2: Not a Bad Romance

If you’re into dance music, you couldn’t help but hear The Fame when it came out. If you liked it, you’re going to like The Fame Monster, but if you didn’t like it, The Fame Monster won’t change your mind about Lady Gaga. The Fame Monster is really just more of the same songs in the same style.

Most SQL Server professionals who liked SQL 2005 are going to pick up this latest release, throw it into their playlists, and keep right on working as if nothing changed – because for the most part, it hasn’t. SQL Server’s engine hasn’t really changed since 2005 came out. Unlike The Fame Monster, though, I think R2 has a chance of winning over people who weren’t fans of SQL Server before. Microsoft is extending its range with StreamInsight, PowerPivot, and Parallel Data Warehouse Edition, and these might bring in a new audience.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

30 Comments - Leave a comment!

Virtualization and SAN Basics for DBAs Webcast Today

Written on April 29, 2010 by in PASS Virtualization Syndication, SQL Server, Virtualization

At 11AM Eastern, I’m giving a free webcast on Virtualization and SAN Basics for SQL Server database administrators.  Here’s a few links for attendees:

By the end of the day, the archive will be available here – at the bottom of the page, next to our race car book, there will be a link that says View Recording.  Click it.  Become smarter.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

16 Comments - Leave a comment!

Listen Up! I’m on the Virtumania Podcast

Written on April 28, 2010 by in PASS Virtualization Syndication, SQL Server, SQLServerPedia Syndication, Virtualization

Pause that Lady Gaga for a minute and head over to Virtumania.  It’s a casual, hour-long conversation podcast that usually focuses on virtualization and storage.  This week, I’m along for the ride with:

We discussed SQL 2008 R2′s new DAC Pack, why I think you shouldn’t cluster virtual SQL Servers, and why you should virtualize your SQL Server 2000 boxes.  Head on over to listen!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

6 Comments - Leave a comment!

What do you want to know about virtualization?

Written on October 6, 2009 by in PASS Virtualization Syndication, SQL Server, SQLServerPedia Syndication, Virtualization

The PASS Virtualization Virtual Chapter is working with virtualization vendors to build an FAQ, and we’re going to hand it out at the PASS Summit in November.  We’re also working on a unique Q&A panel event talking about SQL Server & virtualization – more on that shortly.

We’ve put together a one-page (well, one long page) survey asking:

  • Have you virtualized dev, production, DR, large database servers?
  • If you’ve virtualized SQL Server, what’s been the biggest benefit?
  • If you haven’t, what’s the biggest barrier?
  • What information do you need to confidently virtualize SQL Server?
  • What significant problems do virtualization vendors still have to solve?
  • Have you got any databases you’d like to virtualize, but the 3rd-party-vendor won’t support it?
  • What webcast topics would you like to see covered?

You can take the survey here.  We’d really appreciate your help.  Thanks!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

2 Comments - Leave a comment!

Laptop Virtualization Best Practices – And a Contest!

Written on September 7, 2009 by in PASS Virtualization Syndication, Virtualization

Whether you’re using Windows or a Mac, if you’re thinking about using virtualization for the first time on your laptop to test out new operating systems like Windows 7 or Windows 2008 R2, there’s a few things you should know.

Get Another Hard Drive

Drive Bay

Drive Bay

Laptop drives aren’t quick to begin with, and running two operating systems simultaneously doesn’t make life any easier for your pokey drive.

Your laptop probably has a removable CD/DVD drive, and that drive bay slot is designed to hold more than just optical drives.  You can pick up a hard drive caddy that slides into that same slot.  Check your laptop’s hardware manual for the exact part number, and then search Ebay for that part number.  Drive bay caddies are usually available for around $20-$40.

Apple Macbook users can swap out their internal drives with the MCE OptiBay drive adapter too.  This voids the daylights out of your warranty, but it’s not as hard as it looks.  I just went through this process with my own Macbook Pro, and I recommend it highly – I have a review of that coming soon.

Pay close attention to the caddy bay specs, and then order a hard drive to match.  Most laptop bay caddies take either a PATA or SATA 2.5″ laptop drive.  Some high-capacity 2.5″ hard drives are a non-standard 12.5mm high instead of 9mm, so make sure you don’t get a drive that’s too thick to fit inside your caddy.  Buy the fastest (not the largest) drive you can afford.  I use the 2.5″ performance test charts at TomsHardware for reference, and the current king-of-the-hill on performance per watt is the Seagate Momentus 5400.6 for around $90.

USB Piggyback Drive

USB Piggyback Drive

If your laptop doesn’t have a drive bay adapter or if you’re not willing to give up that trusty CD/DVD drive, you can also use an external USB hard drive.  Just make sure to get one of the 2.5″ models that doesn’t require external power – the less cables you have to carry, the better.  Then mount it on the back of your laptop display using Velcro tape.  Presto, you can detach it and reattach it whenever you need to pack the laptop into a tight case.

After installation, Windows will see this as just another hard drive that you can partition and format.  When you build virtual machines, store them on this secondary hard drive.  Bonus points for backing up your important files there, too.

Get As Much Memory As Possible

The more memory you have, the better.  I’d consider 4gb the minimum to comfortably run two Windows OS’s simultaneously no matter what virtualization software you’re using.

To find out how much memory your laptop can handle, use the memory configuration tool at Crucial.com.

When It Comes to Virtual CPUs, Less Is More

When building your guest OS’s, always set them up with just one CPU.  Virtualization CPU scheduling has a gotcha: if your virtual OS is set up with two CPUs, then the hypervisor’s scheduler will wait until two cores are available before doing any work in the guest – even if the guest only needs to do one core’s worth of work.  This same concept holds true at the server-level too – don’t set up your ESX guests with 4 CPUs just because you can.

Understand Virtual Networking Modes

The various flavors of hypervisors have three basic network modes for guests:

  • Bridged Networking – aka Home Office Mode. This is the one you’re going to think you want, because each virtual machine gets its own TCP/IP address directly from your home router just like your host machine does.
  • Network Address Translation – aka Starbucks Mode. The hypervisor acts as a little router, and it assigns unique TCP/IP addresses to each guest.  The guests aren’t on the public network directly, but they can access network resources just fine.  This is my favorite because I can switch back and forth between different networks without the guest servers wigging out.  I can suspend them to disk at home, then wake them up at Starbucks and nothing changes.  I highly recommend this mode, especially since it’s easy to switch back and forth between this and…
  • Host-Only Networking – aka Tin Foil Hat Mode. Like Starbucks Mode, each guest gets its own internal TCP/IP address on your laptop’s private network, but there’s no communication with the outside.  This is good for testing software that might have conflicts with other stuff on your network, and it’s also good when you’re on a slow network.  When I’m using my aircard and I don’t have a good signal, I’ll boot up my guests in Host-Only mode so that they don’t try to connect to Microsoft to download updates or anything else that might suck up my precious bandwidth.

Back Up Your Virtual Hard Drives

Installing the Windows guest, configuring it the way you want it, and patching it takes hours.  After you’re done – but before you install any third party software – shut it down and copy the flat files to another directory.

When you want to spin up a new virtual server, just make another copy of those flat files and import them into your hypervisor.  The methods are slightly different depending on what virtualization software you’re using, but all of them are much easier than reinstalling Windows from scratch and patching it.

That’s it for my tips – if you’ve got more to share, feel free to leave ‘em in the comments for other folks to get started easier.

Hard Drives, Get Your Hard Drives Here

Hard Drives, Get Your Hard Drives Here

Oh, And the Contest! Want a Free 120GB 2.5″ USB Drive?

I’m moving to Chicago soon, and I need your help.  I need to clean out my home office, but I hate throwing gadgets away.  Time to give away some of my extra goodies.

Today, since we’re talkin’ USB drives, let’s give those away.  I’ve got two self-powered USB 2.5″ hard drives, both 120 gigs, both exceedingly stylish.  One is a speaker’s gift from the PASS Summit last year, and one is a leather-bound hard drive.  (I have very strange tastes in USB devices.)

To win, just leave a comment here.  Only one comment per person, please.  I’ll draw two comments at random on Saturday Sept 12th at 7AM EST and announce ‘em here.  Good luck!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

84 Comments - Leave a comment!
« Older Posts