SQLChicken.com

SQL Server DBA Tips & Tricks

By

EPM Framework v4 Released!

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

The EPM Framework 4.0 includes the following updates:

  • For enhanced support of large environments
    • Reviewed database design, including views and indexing
    • Redesigned data load procedure
  • PowerShell execution now deletes XML files as soon as load is done – improves space usage on temp folder
  • Redesigned reports
  • Tested/verified against SQL Server 2005-2014

Note: an upgrade script for all the relevant database objects is provided, supporting direct upgrade from v3. Please check the documentation for further information.

HUGE thanks to the hard work from Pedro Azevedo (Twitter | Blog) who developed and tested this new version. Do you use Policy-Based Management (PBM) at work? Give this new version of EPMFramework a spin and let us know what you think.
Share

By

So Long and Thanks For All The Fish

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!

Every Beginning Has an End

matrix3This 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:

  • The amazing people I’ve had the honor to work (and karaoke) with
  • Learning the ins and outs of SQL Server from some of the best in the world
  • Opportunities to write on major SQL Server book titles
  • Learning and teaching exciting new technology like Parallel Data Warehouse/APS
  • Developing and delivering exciting training content
  • Helping others through mentoring and teaching in the Pragmatic Works Foundation
  • Ability to shape and have input to software offerings
  • Company culture built around giving back to SQL Server Community
  • Literally scaling mountains

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.

 

So What’s Next?

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!

And like that…he was gone

Share

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

EPM Framework v4 Released!

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

The EPM Framework 4.0 includes the following updates:

  • For enhanced support of large environments
    • Reviewed database design, including views and indexing
    • Redesigned data load procedure
  • PowerShell execution now deletes XML files as soon as load is done – improves space usage on temp folder
  • Redesigned reports
  • Tested/verified against SQL Server 2005-2014

Note: an upgrade script for all the relevant database objects is provided, supporting direct upgrade from v3. Please check the documentation for further information.

HUGE thanks to the hard work from Pedro Azevedo (Twitter | Blog) who developed and tested this new version. Do you use Policy-Based Management (PBM) at work? Give this new version of EPMFramework a spin and let us know what you think.
Share

By

So Long and Thanks For All The Fish

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!

Every Beginning Has an End

matrix3This 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:

  • The amazing people I’ve had the honor to work (and karaoke) with
  • Learning the ins and outs of SQL Server from some of the best in the world
  • Opportunities to write on major SQL Server book titles
  • Learning and teaching exciting new technology like Parallel Data Warehouse/APS
  • Developing and delivering exciting training content
  • Helping others through mentoring and teaching in the Pragmatic Works Foundation
  • Ability to shape and have input to software offerings
  • Company culture built around giving back to SQL Server Community
  • Literally scaling mountains

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.

 

So What’s Next?

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!

And like that…he was gone

Share

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

Upcoming Presentations

Just a quick note to let you know about a few upcoming presentations I’ll be doing. This week I’ll be presenting remotely for the Washington DC PASS User Group, PASSDC (event link). I’ll be presenting “Do More With Less: Consolidate and Virtualize“. Since all the stuff with Prism came up recently, and I’m presenting for a group in DC, I think I’ll be modifying my deck to have some “fun” with it. :-)

Next week I’ll also be presenting in person for the Tampa (Pinellas) SQL User Group (event link) where I’ll be presenting my “What is a BI DBA?” talk.

Also, I’m proud to say I’ll be presenting at this year’s PASS Summit conference in Charlotte! I’m honored to have secured a Spotlight (90-minute) session for the conference where I’ll be doing an extended version of “What is a BI DBA?” presentation.

Share

By

Internet Security Advice For The Family

Alistor Moody says

Constant Vigilance!

For today’s post I’m channeling my inner Brian Kelley (Blog | Twitter) and talking about security, in particular Internet security. The post today is actually an email exchange between myself and my younger sister. She asked me for some general advice and I wrote a long response and figured I’d share it with you readers (which maybe you can in turn use to send to your own family seeking similar advice).

Read More

Share

By

Business Objects on Linux and SQL Server

This is just a quick post to share a lesson learned while I was on an engagement where the client’s reporting environment was using SAP Business Objects (BO)running on Linux for reporting. We were doing a test to move the underlying data warehouse from another database platform to SQL Server 2008 R2.

As we changed connections over, however, we quickly ran into a roadblock. It seems when we tried to make a connection to SQL Server via BO we got the error of ‘Unable to bind to Configuration Objects WIS 10901′. What made this situation strange is that from a Windows box you could connect but from Linux itself, it wasn’t having any of it. After some digging around we found we needed a third party ODBC driver to make this connection work.

It was suggested to us by the folks at SAP that we use a third party driver for ODBC connectivity. We were pointed to drivers by DataDirect (NOTE: This is not an endorsement for said product, this is simply the solution we tried and went with. There are several third party vendors that offer Linux ODBC connectivity so please evaluate and choose what works for your environment). Have you run in to this issue before? How’d you handle it? Feel free to share your solutions in comments.

Share

By

Speaking at PASS Summit 2013!

I’m happy to officially announce that I’ll be presenting at this year’s PASS Summit in Charlotte, NC! I’ll be presenting my talk “What is a BI DBA?” as a Spotlight Session (90 minutes). This is the second time I’ll have presented at the Summit and I’m honored and beyond excited to have a Spotlight Session so we can cover more material!

This year the conference runs from October 15-18. I hope to see you at the Summit this year! http://www.sqlpass.org/summit/2013/

Share

By

Magnify SQL Text with SSMS 2012

This is just a quick tip to help with folks who present SQL code at events such as SQL Saturday. While most presenters use tools like ZoomIt (which if you present, please please learn to use this wonderful, free tool) sometimes it can get nauseating for attendees to watch you constantly zooming in and out, especially on code.

A quick way around this is by using the magnification feature in SQL Server Management Studio 2012. To do this simply hold down the Ctrl button on your keyboard and with your mouse scroll the mouse wheel up to increase the magnification and scroll down to decrease it.  Alternatively you can simply click on the magnification dropdown, which is located at the bottom left of the query window (by default) and select your desired level of magnification.

That’s it! Now you can quickly magnify your code to make it easier for your audience to see and you can reserve the zooming to highlight other areas as needed.

Share