SQL Server Tips & Tricks
Okay so I already posted a round up post for this month. You can see it here. Tonight one of the participants, Rob Farley (Blog | Twitter) made me aware of the fact that I missed a HUGE opportunity in making the round up in a Power BI report! *face palm* With that I proudly give you the interactive version of this month’s TSQL Tuesday round up!
This month I challenged the blogging community to share their own creations in Power BI. We got a ton of great entries this month, thank you everyone who participated! My overarching goal for this month’s topic was to get folks who may not normally play in the BI space to use this fantastic solution and maybe get some ideas flowing on how they may be able to apply it in their everyday work.
UPDATE: Thanks to Rob Farley for the idea, I’ve made this round up into Power BI report! (Direct link)
Thanks again to everyone that participated this month. Even if you weren’t able to participate in the official event, please feel free to share your Power BI experiences in comments!
So this month I’ve decided to bring every participant in this blog series kicking and screaming into the BI world. Power BI, if you haven’t heard or seen it yet, is Microsoft’s analytics and visualization solution. This month I challenged everyone to create their own Power BI report and share it with the world via our new Publish to Web feature. So far I’ve seen some great contributions from fellow bloggers, and those will shared in the upcoming round-up post. You can also check out the winners of the recent Power BI Report Contest (Congrats Jason and James!).
For my contribution to this contest I’ve decided to share with you a work in progress. If you know me, I’m a huge lover of Policy-Based Management. In fact, I’m actually part of the Enterprise Policy Management Framework (EPMF) project on Codeplex. T-SQL Tuesday event is normally a DBA-centric event so I figured I’d help the DBA crowd wrap their heads around how a BI solution can help them in their day to day.
What I did to kick start this effort was to create this Power BI report that allows you to explore the database repository that contains the EPMF policy evaluation results. The current EPMF project uses Reporting Services to deliver its reports. This won’t change. If anything I’ll be exploring new capabilities with SQL Server 2016 and R-integration. Here’s a screenshot of what the SSRS dashboard report looks like:
While this report is nice, it can be a little cumbersome doing interactive analytics against this data set. The reports are somewhat “interactive” in that you can click on a visualization to drill down in to a detail level report behind it, but it’s not really interactive. Enter Power BI! Below is my first swag (read also: none of this is set in stone for final EPMF release, any/all feedback is appreciated) at creating a Power BI version of this dashboard. And yes, I’ll be doing away with pie chart. Dataviz people rejoice!!!
I’ve included a lot of the existing elements from existing report including seeing detail level information of policy results breakdown by server and policies. You also get interactive elements such as choosing policy results, server names, month. On the trending and details page (second tab), I’ve used some custom visuals from Power BI’s Custom Visuals Gallery such as the tadpole spark grid plus to show trending over time.
The best part of using Power BI to visualize on your operational data? You can access your reports and insights anywhere, anytime through our mobile apps! We’ve got apps on all major platforms (iOS/Android/Windows Mobile) as well as a universal Windows 10 app. Think about this. Many large organizations pay thousands upon thousands of dollars for monitoring software that includes capabilities to push to mobile, and even then they’re usually shrunken mobile websites. This is a native application functionality in the palm of your hands at a fraction of the price, so cool!
What else is in the future for EPMF? I’ll be rebuilding the data model from the ground up to make it more efficient for reporting. The current model is very simplistic, and works, but as enterprises adopt EPMF and monitor more and more, the current model has certain scale issues. What do you think? Are you excited for new developments in EPMF? Are you using EPMF today? Let me hear it in the comments!
Hello everyone and welcome to this month’s T-SQL Tuesday. This 
month’s topic is all about Power BI! If you’re reading this and thinking “crap, I’m not a BI person!”, don’t you fret. My intention is to make folks who normally don’t use BI on a day to day basis try their hand at creating cool new visualizations and reports with Power BI and seeing how the other side lives. Maybe it can spark some ideas on how you can make use of it in your operational day-to-day. For the BI folks, let’s see your creative side! What cool things can you show us with Power BI?
Your challenge, if you choose to accept it, is to create and publish your very own Power BI report! Great news, one of the latest features of Power BI to come out is the ability to publish your report to the web so when you create and publish your report, embed it in your T-SQL Tuesday post! Here’s a couple of great examples of this feature in action:
If you’ve never used Power BI before it’s REALLY easy to get started. Simply head over to PowerBI.com and click on the ‘Get Started Free’ button. Download and install Power BI Desktop tool. It’s a free download and this is what you’ll use as your authoring tool. Click ‘Sign Up’ button. You’ll need to sign in with an organizational account (e.g. you can’t use personal email such as @gmail,com @live.com, @Hotmail.com, etc.). If you have a custom domain (e.g. SQLChicken.com), you can use your custom domain email address, otherwise you’ll have to use an organization/work email.
Once you’re up an running, check out the Power BI documentation page to help you get started. For this post, feel free to connect and use any data set you want that can be shared without issue. For example don’t publish your company’s data, customer data, etc. There’s a ton of data out there to use that is safe. In fact, the tutorial from the above link lets you download a sample CSV file if you can’t find a proper dataset. Also, Jen Underwood (Blog|@idigdata) actually posted a great list of public data sources you can take advantage of. Want more great Power BI information? Make sure to check out Adam Saxton’s (Blog | @guyinacube) Guy in a Cube YouTube channel and subscribe for awesome bi-weekly, content.
Want some inspiration on what you can do? Check out the Power BI Best Report contest that’s currently going on. Be sure to vote for your favorite report before voting closes on February 14th, 2016.
T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was founded by Adam Machanic (blog|@AdamMachanic). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic.
I look forward to seeing everyone’s reports!
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.
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.
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.
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:
@SQLChicken I use SQL as the back end all the time. But I’ve never had to use the management tools. I use EF Code first and migrations.
— Scott Hanselman (@shanselman) October 31, 2013
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”.
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…
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.
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…
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.
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. 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.
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?
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!
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.
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!
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!
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.
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.
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:
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.
Here are the posted rules for this shindig:
ATTENTION: RULES HAVE CHANGED, SEE BELOW
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.
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!