SQLChicken.com

SQL Server DBA Tips & Tricks

By

T-SQL Tuesday #48 Roundup

20121003200545.jpg

A big thanks to everyone who participated in this month’s T-SQL Tuesday (link) blog party. This month’s topic was to give your thoughts on Cloud. Lots of interesting reads after the break.

Read More

Share

By

T-SQL Tuesday: Head in the Clouds

20121003-200545
This month’s T-SQL Tuesday is hosted by yours truly. Our topic this month is simply the Cloud. If you work in IT there’s approximately zero chance that you’ve managed to avoid this word in some respect. Has your manager asked you to look into what cloud solutions can do for you? Are you ahead the curve and have taken it upon yourself to already start using and/or testing cloud solutions? This month I asked everyone to share what their thoughts were on the cloud.

Choices, Choices

When people talk about cloud solutions there are a myriad of options you could be talking about. Since this is a SQL Server focused blog, I’m going to focus on offerings specific to that. More specifically I’ll be talking about offerings from Microsoft’s cloud solution, Windows Azure, since that’s the platform I have experience with.

In regards to choices around SQL Server in the cloud there are two routes you can take: use Windows Azure SQL Database (WASD). This offering is known as Platform as a Service (PaaS). What this offering does is it offers developers a relational platform to develop against quickly and easily without the hassle and worry of the administrative overhead that goes with standing up a full SQL Server server. The drawbacks here are there are certain limitations around this option but I’ll drill into that in further detail below.

The second solution you’ll come across, and my personal favorite, is Windows Azure Virtual Machines. This offering is referred to as Infrastructure as a Service (IaaS). What this gives you is an on-demand, scalable compute infrastructure. In non-marketing speak it basically means you can spin up a virtual machine with SQL Server already installed, storage allocated, and customized number of CPUs and memory in minutes instead of waiting around for your IT department to go through its normal provisioning cycle. If it sounds like I’m advocating completely circumventing your company’s policies and going rogue, I’m not. More detailed thoughts on this offering below as well.

WASD: Hey DBAs, It’s Not For Us!

Ever since Azure came out and rolled out the various SQL Server offerings I’ve been trying to wrap my head around this particular facet of the solutions offering. Ever since it came out (and was still called Azure SQL Databases), all I could do was focus on its limitations what it couldn’t do.

Some of those limitations have changed/increased over time such as database sizes. At first the largest database you could create was 50GB. Now you can create databases up to 150GB in size and you can shard you data out so you can get beyond that 150GB size barrier if you need to. However sharding data like that requires different coding techniques that your development team likely isn’t doing today.

Additionally there are other restrictions like requiring a clustered index on every table, which isn’t necessarily a bad thing. Since this database is in the cloud another issue developers need to code for is network connectivity. Network connectivity can (and will) drop on occasion so it’s necessary to code retry logic for connectivity in your application. Finally if you write a bad query that causes the transaction log to “blow out”, your connection gets throttled for a time. For me, as a DBA, all these restrictions why would anyone in their right mind want to use this?! And therein lies the crux of the issue: I’m a DBA…this isn’t a solution meant for me.

It wasn’t until having some conversations with folks at this year’s PASS Summit that the whole use case, and my understanding, of WASD really clicked into place. After attending Connor Cunninham’s (Blog) pre-con on Azure Data Platform, attending Grant Fritchey’s (@gfritchey | Blog), having conversations with Lara Rubbelke (@sqlgal | blog ) and Eli Weinstock-Herman (@Tarwn | blog ) amongst others I came to a realization about PaaS: It’s not meant for me, so I really shouldn’t be bothered by the fact that it can’t do X, Y, Z. Just because it has the SQL Server label on it, doesn’t automatically mean I, the DBA, need to own it! “But Jorge, in my shop if it says SQL on it I end up supporting it anyways!”. Well that’s okay, because with PaaS the administrative side of things are handled (for most part) by Microsoft. These tasks include backups, hosting/provisioning of servers and day to day administration of the infrastructure that supports the solution.

Long story short, this is a solution aimed at developers. They just want a relational data store to develop against without headache of waiting for someone to provision them an environment to do so, nothing more. Think this isn’t happening today with devs? Check out this Twitter conversation I had with Scott Hanselman (@shanselman | blog) recently:

Scott not only uses WASD for development purposes, he wasn’t even sure what I was talking about when I asked him if he used WASD, that’s about how transparent they’ve made it for developers. The conversation was based around my discovery that not all administrative pieces of WASD had been ported over to HTML5 yet from Silverlight. He didn’t know because as a developer that’s something he never had to deal with or care about. In the words of Martha Stewart “that’s a good thing”.

OH NOES, CLOUD HAZ TAKEN MAH JOBZ!

Don’t worry, dear reader (totally ripped that from @sqlballs), not all is lost and no your job as a DBA isn’t going anywhere. If anything, your job stays intact and it’s going to evolve. Evolution is good, ask Charles Xavier. If anything the rise of cloud technology not only cements your role in the company but will actually upgrade you a bit as now you evolve into more of an architect role. Still like staying close to the technology? It’s still there and not going anywhere. We still have our on premise options. Not only that, we have pretty cool cloud options that are made to work hand-in-hand with our on premise environments. Which brings me to my favorite option…

Windows Azure Virtual Machines FTW

I love virtual machines. I especially love Windows Azure Virtual Machines (WAVM). Not only do they keep my job intact, in that you’re still doing everything you, as a DBA, do today in administering and using full SQL Server in an operating system but it also makes my job a hell of a lot easier in some respects.

One of the coolest things about WAVM is that Microsoft provides you with a nice selection of pre-built template virtual machines for you to choose from. SQL Server 2008 R2 + SP1 on Windows Server 2008 R2 + SP1, it’s there. SQL Server 2014 CTP 2 on Windows Server 2012 R2. Only a few clicks away. Not only that, you can fully customize these virtual machines’ resources such as number of CPUs, how much memory allocated to it and disk space. Disk space should probably be the best news anyone who has had to beg a SAN admin for disk space has heard. You also get the benefit of applying high availability options as well as backup protection options in a few clicks.

So if it’s just a virtual machine, just like you have today in your datacenter, what’s the big deal? Well there’s a few things. I just mentioned that self-service ability. Unless your enterprise has invested in a full blown Private Cloud solution then you probably don’t have anything like that available to you. Today you’re putting in a request, or opening a ticket, outlining what you want and writing up justifications for it. Then you get to wait for the network team, SAN team, sysadmins and DBAs to all do their part in setting up the machine then finally turning it over to you.

Fantastic…What’s The Catch?

I know, I’m painting a rosy, unicorn-laden picture. Well fact is there are certainly some things about WAVM you need to consider. First, it’s not connected to your network. Not a problem…maybe. There are ways to have your network extended out to the cloud through Windows Azure Virtual Network. If you were to extend your network out to Azure, you can also stand up a domain controller out there so any virtual machines you spin up out there look and feel just like any other server on your corporate network.

Okay then what about reliability? Each component of Azure offers its own SLA, which you can see here. As of time of this article the stated SLA for the virtual network is 99.9% and other cloud services (virtual machines with availability sets) at least 99.95%. Do you get that sort of SLA at work today? You might. Well compare that level of reliability and service compared to what you’d pay using Azure versus what your company paid to set up the infrastructure and staff to offer the current level of reliability.

What’s security like? Well I’ll be blogging and presenting more on Azure security this coming year but for purposes of this post I’ll condense it. It’s as good as you make it. Just like your current environment. Again, because we’re talking virtual machines it’s all the same as what you’re doing today inside your data center. In fact, I would bet that most of you currently work in places where your company’s datacenter is actually located outside your company and hosted by someone else (e.g. colo sites). In these massive datacenters you have racks and racks of servers and equipment that are bought and paid for by customers of the host but are physically located side by side. Azure is also a co-located situation but you have a little more dynamic control over where components of your solution are located.

Okay so we have our virtual machines “hanging out” in public for anyone to get to then? Not exactly. The virtual networks you configure, by default, essentially have their tin foil hats on and are not open to the world. Portions that you do open up you have to explicitly grant access through the firewalls in place. How about that data in storage? Again, how much do you secure it today? If you leave it unencrypted, at rest, in your data center today then you’re potentially exposing it to theft as well so technically this risk exists in both worlds. In the end, with security, there comes a point where it’s simply a matter of trust. Trust Microsoft to secure their data centers. Trust yourself to do your job correctly and secure what needs to be secured. This last point brings me to my final epiphany about the cloud, thanks to Grant Fritchey/Tom LaRock (@SQLRockstar | blog ) for this one…

The Cloud Forces You to Do It Right

This goes for both PaaS (especially) and IaaS. One of the best things I heard at Summit this year was Grant ranting on how WASD forces you to code correctly. Write code that forces a log to start blowing out and it kills your session? Well write it correctly to avoid that. Network glitches can and will occur. Have you written retry connection logic into your application? I guarantee you will now.

Like it or not we’re seeing a fundamental shift in how computing solutions are offered and used. We’re seeing a world of consumerization of IT (I hate myself for typing that marketing buzz phrase) where end users expect the freedom to pick and choose their solutions and don’t want to wait for the black hole that IT can be to respond to their needs. They will discover solutions like Azure, see how fast they can do stuff on their own, and potentially get themselves in a bind. Instead of coming off as the stodgy group that doesn’t want to help, embrace these solutions yourself and offer them up with guidance. In the end it’ll be a win-win for everyone.

How do you feel about this whole thing? If you didn’t write your own post this month I’d love to hear your thoughts in comments below.

 

 

Share

By

T-SQL Tuesday #48– Cloud Atlas

TSQL2sDay Logo
Welcome to this month’s (November 2013) edition of T-SQL Tuesday. For those not familiar this is rotating blog party that was started by Adam Machanic (@AdamMachanic | blog) back in 2009. Want to catch up on all the fun to date? Check out this nice archive (link) put together by Steve Jones (@way0utwest | blog). Thank you Steve!!!

Cloud: What’s Your Take?

Cloud. It’s the juggernaut buzzword in IT for the last couple of years now. By now you’ve surely been exposed to some aspect of it: Azure Virtual Machines, Windows Azure SQL Databases, Amazon EC2, Rackspace, etc. At this point in the game the cloud solutions are fairly mature and constantly evolving to better serve their customer base.

image
This month’s topic is all about the cloud. What’s your take on it? Have you used it? If so, let’s hear your experiences. Haven’t used it? Let’s hear why or why not? Do you like/dislike recent changes made to cloud services? It’s clear skies for writing! So let’s hear it folks, where do you stand with the cloud?

Rules

  • Your post must be published between 00:00 GMT Tuesday November 12th, 2013, and 00:00 GMT Wednesday November 13th, 2013.
    Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
    Trackbacks should work, but if you don’t see one please link to your post in the comments section below so everyone can see your work.

For the Horde! (Read also: letting everyone know about TSQL2sDay)

  • Include a reference to T-SQL Tuesday in the title of your post.
    Tweet about your post using the hash tag #TSQL2sDay.
    Volunteer to host a future T-SQL Tuesday. Adam Machanic keeps the list.
Share

By

T-SQL Tuesday #008: Gettin’ Schooled

Here we go again with another round of T-SQL Tuesday fun this time brought to you by recent SQL Certified Master (MCM), SQL University professor, and my brother from another mother Robert Davis (Blog | Twitter).

This month’s topic is actually a series of really good questions:

We return to our days of youth to take a fresh look at learning. How do you learn? How do you teach? What are you learning or teaching? Or the coup de grace post would be learning something new and telling us about it.

So given that I established and run SQL University this topic is right up my proverbial alley!

How do you Learn?

I love the old adage “Give a man a fish and he’ll eat for a day, teach a man to fish and he’ll eat for a lifetime”. My best learning tool is by doing, which I’m sure many of you can relate to. There’s only so much academic knowledge you can soak up before it starts spilling out. Honestly, if something isn’t relevent to me in terms of real-world application I have a much harder time grasping the concept. For instance a year or two ago when I first heard of PowerShell I looked at it and went “oh that looks cool…I guess, not sure what I’d do with it.” Fast-foward to a month ago and I finally get a chance to re-learn the basics and actually apply it before that lightbulb went off in my head that said “oh WOW, I GET it! I heart PowerShell!”. Without that opportunity to really apply the academic knowledge, the education itself goes to waste.

Thing is not every job you’re in will call for use of PowerShell, replication, mirroring, etc. so the next best step…do it yourself! The wonderful world of technology now allows us to build virtual machines on our laptops with relative ease. So if your shop doesn’t give you a proper sandbox to play around with advanced features then I highly recommend you install Virtual PC, VMware Workstation, VirtualBox, VMLite or whatever your preferred flavor of virtual platform and get to practicing! If installing and configuring virtual machine’s isn’t your cup of tea (but I highly recommend as you learn lots of lessons by installing/configuring machines from scratch) there’s a free sandbox environment you can experiment with a fully configured SQL Server 2008 virtual machine at http://sqlserverbeta.com . This is a free, no-strings-attached virtual machine complete with AdventureWorks databases installed ready for you to tinker with brought to you courtesy of PASS, Dell and MaximumASP.

How Do You Teach?

Ah the Golden Rule: we learn it young and it applies forever. When I learn I like to be entertained a bit as well as soak up good knowledge. I also like to interact as much as possible. This being the case when I do a presentation I try to keep it lively as sometimes straight technical content is…well…boring. I’m sure that sounds blasphemous to some of my fellow geeks out there but you know its true. You’ll be sitting in a session with content that can’t really be demoed (i.e. internals talk). The secret to teaching is making a topic engaging enough that you don’t lose your audience.

 Although there’s a fine line between entertainment and being annoying. I’ve been guilty of this myself wherein in your attempt to keep it light you come off looking like a clown. Make sure you remember your root cause for being there and that’s to teach. Clowning has its place so remember to toe that line carefully…unless you’re Buck Woody then all bets are off!

What Am I Learning?

Anything and everything! I’m like a shark and learning is my water. If I’m not learning, I’m dying. SQL Server is a HUGE product and currently I’m starting to lean towards learning the BI stack. Another key thing that I keep learning and playing with is PowerShell. In all seriousness there’s ALWAYS something to learn and it doesn’t always have to be something as huge as learning an entire product line or feature. Start with baby steps. One cool thing I’ve seen recently is a series of blog posts called “One Cmdlet At a Time” in which Jonathan Medd (Blog | Twitter) learns PowerShell one cmdlet at a time. This is a great example of how you can tackle learning something one little bit at a time. In his case not only is he learning by doing so hands on but he’s teaching as well via his blog. It’s a win for everyone!

Share

By

T-SQL Tuesday #007 – Roundup

New hotness

First of all I apologize for this roundup taking more time than usual to appear. This last week was my last week of work at the hospital and trying to get everything turned over as well as prepping for my move to Jacksonville my time’s been short to say the least. Without further ado, here’s this month’s #TSQL2sDAY roundup:

Stacia Misner reports in on her love for all things Reporting Services.

Brad Schultz fills in the gaps for us with a brilliant (as always) Intellisense post.

Glenn Berry gives us some great insight on core database engine improvements. If you don’t think R2 brought anything to the core product stop and read this post.

Andy Lohn merges several answers together with IntelliSense, Resource Governor and the MERGE statement! Also Andy’s new on my radar and has a witty Twitter name so time to add another person to follow on Twitter/add to Reader!

Jason Brimhall packs a ton of great information in his post about compression.

Seth Phelabaum talks about the new T-SQL and query writing improvements. This is awesome as I wasn’t aware of some of these new additions! Developers, this is a MUST read! Once again, another great blog added to my reader!

Robert Davis, one of the newest folks to attain the prestigious SQL Server Master certification, brings to our attention something you wouldn’t really think about: sysprep support! SWEET! Great walkthrough example using a virtual machine on Windows Server 2008 R2. Definitely an administrator MUST read.

Wayne Berry takes us for a trip to the clouds with the SQL Azure team and SQL Server 2008 R2’s SQL Azure integration. If you’re even thinking about putting your database in the cloud check out this post and subscribe to the SQL Azure team’s blog.

Kendra Little has plays the dating game in this post where she narrows down three great features (PowerPivot, Data Compression and The Data Collector) and finally comes up with her winning date. Read this post to find out who wins!

Bill Fellows hasn’t been able to play with R2 yet but gives a great and detailed walkthrough of how and why to use User Defined Table Types as Table-Valued Parameters. VERY cool! Added to reader.

Steve Jones, of Voice of the DBA podcast fame, gives us some great insight on StreamInsight. Honestly this feature also excites me so this is a really nice intro to the feature.

Gethyn Ellis is a man after my own heart by talking about Policy-Based Management. Also always great to discover another great blog from across the pond.

Ted Krueger, who did a fantastic job last week with SQL University’s HA/DR week, waxes poetic about all of the new changes in SSIS Data Flow Engine. If you care at all about SSIS performance check this out!

John Racer, aka @speedracer, could’ve written a whole book on all the new BI features but instead focused on Reporting Services. Nice breakdown of new hotness in SSRS.

Mark Blakey talks about PowerPivot and comparing it to a certain scene in Austin Powers (no, not the hot tub scene with Alotta). Also make sure to give him a follow on Twitter as well.

Nicholas Cain, aka @anonythemouse, despite claiming to cheat by referencing one of his old SQLServerCentral posts covers a real nice example of how Data Compression helps you save space.

Pinal Dave does a fantastic job of covering MERGE operations as well as showing us precisely how it improves performance over traditional methods. Very cool! Developers, definitely check this out!

Stef Bauer walks us through the Resource Governor feature and how it works. Pretty cool feature to help cull down those resource-hungry queries from ad-hoc queries.

Rob Farley throws us for a loop as his favorite part about SQL 2008 R2 doesn’t even have to do with the features! Check it out.

Bob Pusateri is excited by what’s NOT there with his overview of filtered indexes in 2008 and how they helped him solve a specific issue on a homegrown solution.

Share

By

T-SQL Tuesday #007: Summertime in the SQL

Invitation for T-SQL Tuesday #007

New hotness

Welcome back to the blog party known as T-SQL Tuesday. I’m honored to be hosting this month and since its Summer I’ve decided to talk about hotness…feature hotness. In the last few months we’ve had the release of SQL Server 2008 R2 which brought along with it a slew of new features. So this T-SQL Tuesday I ask: What’s your favorite hot new feature in the R2 (I’ll be nice and include 2008 in general) release? Got some code that takes advantage of a new feature? Post it! Got an example of how PowerPivot let you slice and dice data you simply couldn’t before due to contraints? Show us! Make sure to apply your SPF 1433 and get to writing.

Cabana Rules

This whole party started when Adam Machanic (Blog | Twitter) decided to start this awesome blogger party where one topic is chosen by the monthly host, everyone submits their posts, and those posts are then rounded up. Catch is these posts must all go up within the same 24 hour period. Here’s a list of the parties held so far:

  • #001 Adam Machanic asked for your favorite Date/Time tricks
  • #002 Adam followed up by asking for your favorite Puzzling Situations
  • #003 Rob Farley made the commitment with his topic: Relationships
  • #004 Mike Walsh kicked asked for your input on IO
  • #005 Aaron Nelson asked us to report on “Reporting
  • #006 Michael Coles wanted to know All About BLOB
  • So write up your post and make sure to link back to this post for your entry to count.

    To participate your post must go live between 00:00:00 GMT on Tuesday the 8th of June and 00:00:00 GMT on Wednesday the 9th.

    Don’t Run on the Pool Deck

    Here are the posted rules for this shindig:

    ATTENTION: RULES HAVE CHANGED, SEE BELOW

    1. Your post must go live between 00:00:00 GMT on Tuesday the 8h of June and 00:00:00 GMT on Wednesday the 9th. If your post doesn’t go live in the time limits, it won’t be included in the round-up post.
    2. Your post must link back to this post (by trackback or comment).
    3. “T-SQL Tuesday #007″ MUST be in the title of the post.
    4. The above rule has been CHANGED! The new rule is that the title of the participating post no longer has to reference T-SQL Tuesday (although it’s still recommended). The post still has to link back to the hosting blog, but the link needs to be anchored from the NEW LOGO (found above), which must appear at the top of the post.
    5. It is your responsibility to verify the trackback or comment appears here on this post. If you don’t see your trackback add your own comment with a link back to your T-SQL Tuesday post and it will be included in the roundup.
    6. Don’t feed Gremlins after midnight
    Lifeguard on Twitter Duty

    A lot of the T-SQL Tuesday bloggers and bloggees are on Twitter. Follow the hashtag #TSQL2sDay and when your post goes live, tweet a link to it with that tag.

    Hosting

    You too can host this event! Just have participated in at least two previous #TSQL2sDay and let Adam Machanic know you wish to host. That’s it!

    Share

    By

    TSQL Tuesday #005: Reports and Policy-Based Management

    It’s another month and so we have ourselves another T-SQL Tuesday event, this time hosted by Aaron Nelson (Blog | Twitter) with this month’s theme being Reporting. If you don’t know what T-SQL Tuesday is here’s a quick blurb from Aaron’s blog:

    You Don’t Know What T-SQL Tuesday Is Yet?

    Adam Machanic (Also on twitter as @AdamMachanic) had a great idea 5 months back – Invite new and existing SQL Server bloggers to post about the same topic on the same day. The results have been excellent – diverse skill sets and data related job roles all posting from differing perspectives on the same issue.

    Since this is still early in the game, a quick list of the post roundups from the first 4 topics:

    So given that this month we (Ken Simmons, Colin Stasiuk and myself) are proud to be releasing Pro SQL Server 2008 Policy-Based Management I figured this month’s TSQL Tuesday could tie in nicely. So how can one use Reporting Services in conjunction with the power of Policy-Based Management (PBM) in SQL 2008? Well, you’re in luck!

    Read More

    Share