SQLChicken.com

SQL Server DBA Tips & Tricks

By

SQL University: SQL Server Editions for Small Businesses

In our last session we talked a little bit about the different options you have in regards to SQL Server in your environment. We talked about HOW to get the software but now we’re going to talk about what the different editions are and what they offer you. For a quick comparison between editions you can use the Edition Comparison page at the Microsoft site. As a small business you’re looking to get the best bang for your buck. You also want to make sure that whatever decision you go with, you can easily scale up so your infrastructure can grow as your business does. Thankfully, SQL Server’s growth scale is pretty easy. We’re going to cover editions from the lowest level up.

Express Edition

This edition of SQL Server is free, which is great especially if you’re looking to get your feet wet developing on the platform and don’t have/not willing to put down the cash for a license just yet. Express edition is great if you’re looking to build small-scale applications or small desktop applications. Great thing about this edition is its also portable so as an ISV (Independent Software Vendor) you can actually create an application using Express Edition and bundle it with your own software.

The drawbacks for these lower editions come in the restrictions. For Express Edition you are restricted to 1 CPU, 1 GB memory utilized and the databases have a 10 GB size restriction. If you do decide to go with this and you find that it is too restricting for your needs, you can easily upgrade your instance to the next level up which is Standard Edition.

Compact Edition

Compact Edition is also a free edition of SQL Server however you won’t ever be running anything (read also: infrastructure) from your business off this. Why did I include this then? Well, there are small businesses out there that develop software for clients that are mobile or occasionally connected employees (think travelling salespersons). Compact Edition gives you the ability to create software and embed a database inside the application. This edition gives you the ability to use SQL Server on a small-scale and distribute with your applications for free.

Workgroup Edition

Workgroup edition is much less restrictive than Express edition, however this is edition you have to pay for. At this level you start getting more protective features such as Log Shipping, something that Express doesn’t offer. Also your restrictions go way up here as this edition supports up to 4 CPUs, 64 GB of memory and the database size limitation goes up to 524 PB! Also on the administrative side, you get the ability to use the SQL Agent which allows you to automate jobs on a schedule. This also allows you to create maintenance plans, use Database Mail and use Performance data collector (2008 R2 feature).

If you look at the pricing for this edition it is pretty close in price to Standard Edition, which makes me wonder why they even bother offering this as an option since they’re clearly trying to steer you towards Standard! This may not necessarily be a bad thing as Standard Edition is going to have everything you’ll need to get started but it just makes things kind of confusing for everyone when you offer so many options to begin with.

Web Edition

This is geared more towards the shops that will be using SQL Server on a public-facing website. This is important distinction to make as the licensing terms between this and some of the other higher-level editions (i.e. Standard, Enterprise) are different. For instance if you’re using SQL Server Standard edition internally and only 10 people/devices will be connecting to it, you can purchase individual CALs (Client-Access Licenses) at a much lower cost than a per processor license. A per processor license allows for unlimited connections to the target server. The Web edition’s license is only available on a per processor basis but its per processor cost is much lower than that of a Standard Edition but still offers pretty much the same feature-set as that of Standard. These types of distinctions are important to make as you want to make sure your company is properly aligned license-wise for your uses. Again, you can check out the Licensing page to get a quick look at each edition’s list prices for comparison.

Standard Edition

This is more than likely where most companies end up starting off at as far as choosing SQL Server for infrastructure use. Standard Edition offers a pretty comprehensive list of features, which you can check out here on the Features Supported page. Standard Edition is going to give you not only the full power of SQL Server but also the protection you need as a business in that it offers protective features such as database mirroring, log shipping, backup compression (the other editions do backups but don’t do compression, if you’re looking to save on disk space this is nice option to have), replication, automation, much more.

As a small business there are a few things you need to make sure you’re doing to protect yourself, namely backups (if you don’t do anything else, please PLEASE setup backups!!!) and general maintenance. Not sure what you need to do for either of those? Make sure you check out the Administration and High Availability/Disaster Recovery posts at SQL University to help give you some direction.

SQL Azure

SQL Azure is the new kid on the block and brings a whole new dimension to how you can use SQL Server in your enterprise. So far all of the editions we’ve discussed require you to not only purchase the software, but you’ll need hardware to set it up on! With SQL Azure those headaches are removed as Microsoft hosts your database instances for you in their data centers so you get the power of SQL Server plus the peace of mind that your databases and applications (they offer Windows Azure as well) is backed by an extremely robust and scalable infrastructure. In addition, they’ve started rolling out other nice features like Reporting Services and Data Sync. Data Sync is really cool as it allows you to sync your cloud databases with those already in your local environment. Please note both of these features have not been rolled out to production in Azure yet.

The other nice thing about Azure is that the pricing model is much different from what we’ve discussed so far. With Azure you pay for usage/data consumption rather than worry about processor licenses and all of that stuff. You can check out their FAQ page to answer a lot of questions you may have about this exciting new emerging offering. You can also check out their cool Pricing Calculator to help you figure out what your costs would look like.

As cool as Azure is, it’s not all unicorns and rainbows folks. Unfortunately there are feature limitations in Azure that may make this a deal-breaker for your company. You can see the full list of Feature Limitations here. Now before you read the list and freak about Backup/Restore and Replication not being supported hear me out: Azure is already doing both of those things for you in the back-end, hence you don’t need to handle it yourself. Buck Woody (Blog | Twitter) will be covering Azure more in-depth this semester or can check out this blog for everything you need to know.

Conclusion and Homework

Bottom line is there are many options available to you as a business to use SQL Server, the big question is what works right for you. Also bear in mind that you don’t have to just have one edition, you can mix and match to your heart’s content. Have a web application but you need to host it on-site? Get a web edition license and put it on a web server in your office. Need that same website to scale and not have to worry about infrastructure? Move it up to Azure. Have multiple applications that require SQL Server but each application has certain patching restrictions? Buy a standard edition license and create named instances all on one piece of hardware.

Your homework for this assignment is listed below. To complete this assignment post your response here or write up your own response blog post and link your post in the comment section below.

  • Figure out a licensing scheme for this fictitious startup company that has the following requirements -
  • Public-facing website that will handle light to moderate traffic at first. Must be able to scale in case demand ramps up rapidly.
  • This company develops software so would like to have a development, QA and production environment if possible.
  • Sales division has people that have mobile devices with partial copy of sales database on them. They need to be able to sync data up when they come back in to the office from the field. If possible, sync while on the go.
  • Need to be able to automate routine maintenance such as backups for all critical databases in-house.
Share

By

SQL University: Architecture for Small Businesses

Welcome back to SQL University! This week we’re going to be discussing architecture. In previous weeks we’ve discussed architecture at a high level (Capacity Planning Week) but this week we’re going to get a little more focused. SQL Server is used by businesses of all sizes from local grocery stores to large-scale sites like MySpace (Case Study) or GoDaddy (Case Study). So why are we focusing on architecture for small businesses? The reason is that if you’re expecting for your grow and expand, making the right architectural choices up front can help reduce the headaches that come with potentially rapid growth.

Read More

Share

By

SQLSaturday #85 Orlando: Pragmatic Works BI Pre-Con!

I’m very proud to announce that Pragmatic Works will be presenting a Business Intelligence Workshop pre-con for SQLSaturday #85 in Orlando on Friday September 23rd! In this session you’ll have not one, not two, not three but FOUR Pragmatic Works consultants walking you through how to develop a full business intelligence solution from the ground up! Your instructors for this awesome pre-con are:

Jorge Segarra (Twitter | Blog)

I’m a BI Consultant for Pragmatic Works and a SQL Server MVP. In addition to being a member of the Jacksonville SQL Server User Group (JSSUG) I’m also a PASS Regional Mentor for the U.S. Greater Southeast region. I have also co-authored the book from Apress “SQL 2008 Pro Policy-Based Management“. Redgate Exceptional DBA of the Year 2010 Finalist.

Mike Davis (Twitter | Blog)

Mike Davis, MCTS, MCITP, is a Senior BI consultant at Pragmatic Works. He is an author of a few Business intelligence books. Mike is an experienced speaker and has presented at many events such as several SQL Server User Groups, Code Camps, SQL Server Launches, and SQL Saturday events. Mike is an active member at his local user group (JSSUG) in Jacksonville, FL.

Brad Schacht (Twitter | Blog)

Bradley is a consultant at Pragmatic Works in Jacksonville, FL. He was an author on the book SharePoint 2010 Business Intelligence 24-Hour Trainer. Bradley has experience on many part of the Microsoft BI platform. He has spoken at events like SQL Saturday, Code Camp, SQL Lunch and SQL Server User Groups. He is a contributor on sites such as BIDN.com and SQL Server Central as well as an active member of the Jacksonville SQL Server User Group (JSSUG).

Kyle Walker (Blog)

Kyle is a Business Intelligence consultant for Pragmatic Works. His current and past work experience includes Integration Services, data warehousing concepts, Reporting Services, and Crystal Reports. He is active in the local and online community as a speaker for SQL Lunch, Jacksonville SQL Server Users Group, and past SQLSaturday events, as well as a blogger on BIDN.com.

Abstract:

In this full-day workshop, you’ll learn from the author team of Jorge Segarra, Mike Davis, Brad Schacht, and Kyle Walker how to build a data warehouse for your company and support it with the Microsoft business intelligence platform. We’ll start with how to design and data model a data warehouse including the system preparation. Then, we’ll jump into loading a data warehouse with SSIS. After SSIS, you’re ready to roll the data up and provide the slice and dice reporting with SSAS. The team will walk through cube development and data enrichment with things like key performance indicators, which are essential for your future dashboards. Lastly, we will cover how to report against the data warehouse with SSRS including a primer in how to write MDX queries against the SSAS cube.

Take Home Skills:

  1. Practical knowledge of building a Dimensional Model
  2. Designing a simple ETL process using SSIS
  3. Designing a Cube
  4. Designing simple SSRS Reports
  5. Building an integrated process that fully leverages the entire MS BI stack to load a Data Warehouse

See you all in Orlando!

Share

By

OMG I’m an MVP!

I can’t believe it, but I can finally say that I’m now officially a Microsoft SQL Server MVP! Last week I got the great news from my MVP lead, Ryan Bolz (Twitter) in…a peculiar way. Allow me to explain. Typically MVP renewals and new awardees are notified four times a year, once every quarter. This past quarter’s notifications went out on July 1st. Like so many in the community, when the quarterly MVP day rolled around I was guilty of refreshing my inbox every 5 minutes for 24 hours hoping for that magic Wonka ticket into MVP-ville.

Felt a little something like this...

Alas, July 1st came and went with no email. Disappointed turtle was disappointed. Now I say I was disappointed since I knew I’d been nominated the last few quarters but hadn’t heard anything yet. And yes, I know “it’s the award you’re not supposed to want”, but still it’s stressful knowing you’re up for it but not hearing anything one way or the other. This time around I figured “oh well, keep it up and eventually…maybe”. Anywho, last week as I was at the JSSUG meeting when I got a DM from Ryan with the following: “Hi Jorge — Congratulations! You are a MVP. Call me so I can explain it all to you.”

Say WHAT?!? I checked and double checked the message to make sure it wasn’t some sort of sick prank. As soon as I got out of the meeting I gave Ryan a call to find out what was going on. I’d heard of folks getting awarded out of cycle before but I was still thoroughly confused as to what was going on! As it turns out, apparently I HAD been awarded MVP status in the July 1st batch but something went wrong with getting me the notification. For those not familiar with the process, Microsoft sends out the MVP emails en-masse to folks which seems to trip every spam filter around. Since this is the case, folks are told to check their spam folders on MVP day just in case. In my peculiar case it wasn’t a matter of spam filter black hole but rather an error in my contact information. Near as I can tell they had an invalid email address on file for me so they weren’t able to send me my notification!

Fast forward a few weeks and Ryan had gone on vacation and gotten sick during that period, when he finally got back to the office and catching up he somehow discovered that I had no idea I was awarded. That’s when he dug around and sent me a DM on Twitter as that was a guaranteed means of getting to me directly.

The Mushy Part

Story aside, I want to let you all know how truly grateful I am for this honor. It’s been a pleasure and privilege being a part of this amazing SQL community and this award just compels me to keep working even harder to deserve it. I want to thank everyone for the warm congrats and support, it means a LOT! I’d also like to send a special thank you to those who nominated me and sent me words of encouragement every quarter.

Speaking of working harder I got another piece of amazing news last week as well: I’ll be presenting for the first time at PASS Summit this year! What makes THAT even cooler is that the session is a Community Spotlight session, meaning you guys were gracious enough to cast your vote for me so I can bore educate the masses on Policy-Based Management at the Super Bowl/World Cup of SQL Server events! That being said thank you all so SO much for all your love and support and I look forward to continue serving this community for years to come!

Share

By

Second Chances: PASS Community Choice Sessions

Recently I found out that my PASS presentation submission ‘Policy-Based Management’ has a second chance to be accepted for the conference (which I’m really grateful for!) but I need your help! Please take a minute to head over to the Community Choice Sessions page and cast your vote (up to 5 selections). As long as I’m pandering for votes, please cast a check for some of my fellow co-workers as well!

In keeping with my annoying use of LOLcats pictures…

Thank you!

YOU CAN HAZ THANKS!

Share

By

Make It Simple With Policy-Based Management

At work recently, I explained to a coworker the reasons and benefits of having databases in Simple mode in your development environment. Funnily enough, a few hours later, I see my friend Andie Letourneau (Blog | Twitter) posted a blog on how to adjust your recovery modes in development. While Andie’s homework assignment is to substitute it with a WHILE loop, I have MUCH simpler solution using Policy-Based Management!

First, let’s create the condition and policy. As always you can either use this T-SQL or you can simply download the XML policy and import it:

Download Policy Here

TSQL:

Now that you’ve created your policy, you can either evaluate it directly on that server or we can leverage the Central Management Server feature to evaluate this policy against one or more servers in your environment! Since we’re looking at this from an enterprise-level standpoint, we’re going to go over the CMS way of doing this.

Once you’ve established your CMS, you’re going to want to register servers to it. You could register everything directly under the CMS but then it becomes one giant list and that’s not very nice. One method I like to implement in my CMS is to create new server groups for each environment (e.g. DEV, QA, PROD). Within each group, I also create additional groupings for each version of SQL Server (e.g. 2000, 2005, 2008). Once the groups/folders have been created I then register my servers in their appropriate spots.

The advantage of breaking groups up like this is that CMS allows you to query against multiple servers at once. For instance if you were to right-click the folder for DEV, which in my case contains two registered servers in groups beneath it, SSMS will connect to both instances so that you can query them at the same time. It is this mechanism that we’ll use with Policy-Based Management since that feature allows us to evaluate policies against multiple servers in the same way.

To evaluate this policy, right-click the DEV folder and select Evaluate Policies. For source, click on the ellipses button and select either the folder location of the XML file or select the SQL Server instance which you imported the policy to. Once you’ve selected the policy’s location, you’ll see the Evaluate Policies screen with a list. Tick the box for the Simple Mode Check and then click the Evaluate button.

Check that out, you just evaluated policies against all of the databases in DEV! The databases that are NOT in simple mode (per our condition check) fail the policy evaluation and show up with red X’s. To quickly switch those to simple mode simply check the boxes for those that failed the check, then click the Apply button. This will enforce the policy on those databases and switch them for you to Simple mode! Imagine doing this that quickly and easily against hundreds of databases!

If you’re wondering if you can automate this process, the answer is absolutely! Check out the great open-source project Enterprise Policy-Management Framework over at Codeplex. This project allows you not only automate this process and policy enforcement but it also offers some really nice reporting of all this as well!

UPDATE: After chatting with Andie it looks like I forgot to mention a few things. Namely that for those wondering if this works on down-level servers (2000, 2005, etc.) the answer is YES! So long as you have a SQL Server 2008 server acting as your CMS, you can evaluate (certain) policies against down-level servers. Given this confusion I’ll write up a post on how all this works and what the caveats are.

The other point to make is that CMS uses Windows authentication only. If you have multiple domains, and there is no trust established between the domains, then this solution won’t work. If you DO have multiple domains and trust established, so long as your credentials allow you to traverse domains and the proper security authorizations are in place on the target SQL Servers then it will work.

Share

By

Automating Web App Deployments with SSIS

This is just a quick note to let everyone know I’ve uploaded the project files from my Pragmatic Works webinar on Automating Web Application Deployments with SSIS. You can find the link on my Presentations page. If you missed the presentation you can view the recording of it here.

The zip file includes a readme document that highlights some of the important things to setup for project as well as an explanation of the different solution files inside the zip file. If you have any questions or problems feel free to leave them in the comments below!

Share

By

SQL Server Denali CTP3: Policy Based Management

Whether you know it or not, Policy-Based Management is an integral part of the new release of SQL Server “Denali”. Did you know that the HADR feature actually leverages PBM for checks? Yup, that’s right folks. If you’re an administrator and you’re NOT using Policy-Based Management yet, I highly recommend you pick up a book and check it out!

So what’s new in SQL Server “Denali” CTP3 for Policy-Based Management? I just got the shrink wrap off of it (I’m not quite the go-getter that Aaron Nelson is) but at a quick glance here’s the changes I see. These comparisons were made with listing of facets between SQL Server 2008 R2 and the new “Denali” CTP3 release.

 

We now have 84 facets (as opposed to 76 in 2008 R2). These are our new facets and their applicable target types!

Availability Database – Exposes properies of the Availability Database object
Applicable Target Types: AvailabilityDatabase
Availability Group – Exposes properies of the Availability Group object
Applicable Target Types: AvailabilityGroup
Availability Group State – Exposes properies of the Availability Group
Applicable Target Types: AvailabilityGroup
Availability Replica – Exposes properties of the Availability Replica object.
Applicable Target Types: AvailabilityReplica
Database Replica State – Exposes the properties of physical database replicas participating in an availability group.
Applicable Target Types: DatabaseReplicaState
Search Property List – Exposes properties of the Search Property List object
Applicable Target Types: SearchPropertyList
Sequence – Exposes properties of the sequence object.
Applicable Target Types: Sequence
Server Role – Exposes properties of the ServerRole object.
Applicable Target Types: ServerRole

 

Again, this still has that new install smell so I haven’t had time to mess around with it but I’ll soon be posting some new policies using these facets so you can get an idea of what you can do administratively using Policy-Based Management and these new facets.

Share

By

LulzSec: Why You Should Care

Updating Resume is NOT Lulz

Wow, it’s been a crazy last couple of months with LulzSec running around doing what they do. Oh, what’s that? You’ve never heard of them? Well for many outside of IT this is probably the case. For those of us who ARE in IT have more than likely heard of them as well as their high-profile hacking exploits over the last couple of months.

This weekend TechCrunch posted a pretty good discussion piece on how the media has handled LulzSec’s exploits. To summarize in my own words, the author states that the general media cowered in the coverage of what this group was doing by hacking and leaking info of high profile targets such as the CIA (website), AT&T (internal data leaked) and Arizona Department of Public Safety (internal documents and sensitive information leaked). Carr goes on to say that rather than report on the seriousness of the group’s crimes and activities media would rather cheerlead them due to fear of retaliation from the group itself.

So what does this have to do with SQL Server? Well, if anything, I hope this rash of high-visibility targets has raised your awareness about something that far too many people slack in: Security. When was the last time you did a true security audit of your database servers? Are your web applications authenticating with the sa account (read also: “God” rights)? Are they authenticating with Windows accounts that are backed by stringent and contained groups via Active Directory? If you’re not certain of any of those questions I highly suggest you take a look at Brian Kelley’s (Blog | Twitter) SQL University Security Week posts from this past semester and start to at least formulate some kind of plan.

Security shouldn’t be an afterthought, it should be a base. As data professionals we hold are tasked with protecting the most vital piece of any organization: its data. Do you want to answer to your supervisor, manager and Executives when someone walks away with sensitive information from YOUR databases? Do yourself a favor and if you’re not already discussing security in your offices, start it. How do you handle security in your organization? Afterthought? Hardcore? What’s security? Let me hear your thoughts in the comments.

Share

By

Pragmatic Tools: DTSxChange (Or DTS Must Die!)

Ah DTS packages…they suck. Seriously, it’s 2011. It’s been over a decade and we still have SQL 2000 and DTS packages lingering around our shops…sitting there…taunting us…asking to be upgraded but HOW? There’s SO much work having to learn SSIS, figure out what the current DTS package even does, figure out what shiny new components in SSIS are equivalent to that legacy crap we created a decade ago and recreate it on the new platform! Well fret not, we at Pragmatic Works got you covered with a nifty tool called DTSxChange that will actually convert your DTS packages to SSIS for you!

DTSxChange is a powerful tool that converts your legacy DTS packages to SSIS (can convert to SQL Server 2005 or 2008 SSIS) for you with minimal effort. In addition to saving you a ton of work, the tool actually tells you how much time (and money) you’re saving using this tool over a manual effort via the DTSxChange Profiler feature. This tool allows you to scan all of your current packages and it generates a really nice report (read also: bosses/managers/CFOs love this)

DTS sucks
Ooh shiny!

So now that you know the scope of the work you’re looking at, let’s get to the nitty gritty and start converting! The conversion portion of the software uses an easy step-by-step navigation that lets you customize all sorts of options for your conversion efforts such as destinations, checkpointing, logging options, auditing framework options, connection manager consolidation, converting child packages automatically associated with a package, etc. The nice thing about this is you can convert single packages or hundreds of packages at the same time! If you want to see some of this stuff in action you can watch the video here.

We converted the stuff and now we’re done right? NO WAY! With DTSxChange you also have the option of adding on our custom Auditing Framework which gives you tons of useful reports (Note: Use Report Viewer Application to view auditing data) so that you can easily and quickly view the state of your SSIS packages once their deployed. Here’s a listing of all of the different reports you get:

  • Which packages are currently running and which task is running inside the package
  • Historical package execution detail for selected date range (i.e. Run time, Errors, Warnings etc.)
  • Error and Warning by Task and Package
  • Run time by Task and Package
  • Extracted and Loaded Records along with their source and target information (e.g. Table/View, Sql Query, File Name, Component Name, Data Flow Name, Connection String etc.).
  • Run time Trend for several days/weeks/years by Package and Task
  • Error/Warning/ Trend by Package and Task
  • Extract/Load Trend by Package, DataFlow
  • Extract/Load Trend by Data Object (e.g. File, Table/View or Sql Query)

There are seven inbuilt reports are shipped this version and more will be added in the future release.

Report Name Description
SSIS Execution Dashboard This dashboard gives one place view of most common counters of SSIS package execution.
Package Execution Trend This report provides many useful information about trend of package runtime, task runtime and errors/warnings.
Errors/Warnings Provides detailed error/warning report including package level errors (package level errors not listed in any other report)
Recent Executions Provides information of running packages/tasks and already completed executions.
Running Packages Provides real time information of currently running packages and tasks.
Extract/Load Detail Provides extract/load detail (e.g. row count, source/target information, query, file name …) about each dataflow in each package execution.
Extract/Load Trend Provides graphical view of extract and load trend over several years, weeks, months and days.

DTSxChange helps you migrate away from those legacy DTS packages and gets you on to the latest SSIS platform in minutes as opposed to days or weeks! Don’t believe me, try it out yourself. When you download a trial version of our product it comes with 3 conversions for you to play with. Would you rather have one of the experts at Pragmatic Works do a live demo for you and your company? We can set that up as well for you by sending us a quick email. So what are you waiting for? Kiss DTS goodbye today!

Share