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!
In SQL Server one way we optimize code is by adding indexes. Indexes help queries by essentially letting the engine know “hey, here’s the fast way to get directly to the data you want”. The alternative is doing full table scans to read all the data, which sucks.
When reviewing code in SQL Server Management Studio (SSMS) we have a similar built-in tool to help save you time: Bookmarks! Bookmarks aren’t quite like indexes but they do help you mark locations in code that you can quickly jump to. This is especially helpful when reviewing code in large scripts.
For this example I’m going to use the amazingly useful Diagnostic Information Queries script (which you should be using if you’re not already) from Glenn Berry (Twitter | Blog). This script is jam packed with all sorts of amazingly insightful information on your server and databases. However I often need to jump around to different parts of the script.
For example, when performance tuning I like to run Glenn’s code for getting back the top wait types on the server. After that I jump down to the section on top cached queries/procedures to get a sense of what’s happening. If I do this manually there’s about 300 lines of code separating the two sections. That’s a lot of annoying scrolling!
Instead what you can do is create bookmarks at these locations. You can do this one of two ways. First set your cursor at the desired location in code. You can set bookmarks by going to the Edit menu –>Bookmarks->Toggle Bookmark. The other way is when you have your cursor set you can use the keyboard shortcut of Ctrl+K, Ctrl+K to add a bookmark. When you’ve created a bookmark you’ll see a small grey/white box in the left side window. Now scroll to other locations in code you’d like a bookmark and repeat this process.
To navigate between Bookmarks you can use the keyboard shortcut Ctrl+K, Ctrl+N to go to next one or Ctrl+K, Ctrl+P to navigate to previous. Now you can quickly and easily jump between sections of code! Happy coding.
Cross posted at my new MSDN blog: http://blogs.msdn.com/b/sqlchicken_coop/archive/2014/11/06/using-bookmarks-in-sql-server-management-studio.aspx
The Enterprise Policy Management Framework (EPMF) team is proud to announce the updated release of “Enterprise Policy Management Framework 4.0 for project EPMFramework. This is a major release with lots of performance tweaks and
Note: an upgrade script for all the relevant database objects is provided, supporting direct upgrade from v3. Please check the documentation for further information.
Not to try and steal Satya Nadella’s thunder today at WPC but I’ve got some exciting news of my own to share. I’m excited to say that I’ll be fulfilling a lifelong dream and going to work for Microsoft!
This news is kind of a bittersweet though as I’ll be leaving Pragmatic Works after almost four amazing years. It has truly been an awesome experience. In the last four years I’ve had the opportunity to work with some of the best folks in the business and had opportunities I would never have dreamed of. Here’s a highlight list of some of the best parts:
I’d like to give a special thanks to Brian Knight (Blog | Twitter), Adam Jorgensen (Blog | Twitter) and Bradley Ball (Blog | Twitter). My time at Pragmatic Works was great and I couldn’t have asked for better people to work for. Thank you guys for everything.
The good news is that in my new position I’m afforded the opportunities (and encouraged!) to continue being involved with the community. You’ll still see me presenting at SQLSaturday events, picking up the slack on my blogging, webinars, and hopefully presenting at future PASS Summit (buahaha, now I get to submit under the Microsoft call for speakers!) and other conferences.
I’ll also be delving into some other areas, mainly in the development space. You’ll be seeing some new content from me in areas such as BI development, Big Data, Cloud development, and Windows/Windows Phone development. Should be fun!
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?