Category Archives: SQL University

SQL University: Virtualization Basics

This week we’re going to talk about a topic that has been gaining steam in the last few years and as it has it has started impacting database administrator’s worlds more and more: virtualization. Why do I make this statement? Well since the economy currently sucks, shops are finding ways to consolidate and make their dollars stretch a little further. Back in the day when you had a new application you pretty much went out and bought yourself some new servers and went on your merry way. Now, when money’s tight, folks are a little less likely to go out and simply buy new equipment for each individual application. Not only is this option expensive, there are other factors to think about such as space (data center may not have capacity for new servers), electricity and cooling.

Enter virtualization. Virtualization allows you to consolidate this server sprawl issue by buying a physical server, filling it with tons of your typical resources such as CPU, memory and drives, and from this single box be able to create virtual servers on this single piece of hardware that look/act/feel like independent servers. This week we’re going to cover some basics of virtualization and stuff you need to know about if you’re going to be going that route in your shop.

Continue reading

Share

Small Business Hardware

[NOTE] My blog post scheduling-fu is weak, so this post didn’t go out Friday as planned. My apologies.

This is the final installment of our Small Business series. So far we talked about how to get the software, and we’ve talked about the different options of SQL Server available to you. Today we’re going to talk about what hardware you’ll need as a small business to setup your database environment for success.

Continue reading

Share

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

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.

Continue reading

Share

SQL University: Precedence Constraints

Welcome back, class! In our last class, we setup a parent-child package configuration and showed how you can pass variables between the two in order to complete a unit of work. In today’s class we’re going to continue exploring that data load package and take a look at another critical piece of SSIS that you should know about: precedence constraints.

So what exactly are precedence constraints? They are the connectors that link together tasks in the Control Flow, and they define the workflow of your package. When two tasks are tied together with a constraint, the destination task will execute based on two things: The final state of the task that precedes it and special rules you can define on the constraint using specialized expressions.

Constraint Types

You can have different types of constraints between tasks. You can read more about constraints in detail from MSDN article (Link). I’ll briefly cover each of the constraint types in an abbreviated list and then we’ll discuss how we used these constraints in our parent-child package from our previous lesson.

  • Success – Workflow will proceed when the preceding container executes successfully. Visually indicated in control flow by a solid green line.
  • Failure – Workflow will proceed when the preceding container’s execution results in a failure. Visually indicated in control flow by a solid red line.
  • Completion – Workflow will proceed when the preceding container’s execution completes, regardless of success or failure. Visually indicated in control flow by a solid blue line.
  • Expression/Constraint with Logical AND – Workflow will proceed when specified expression and constraints evaluate to true. Visually indicated in control flow by a solid color line along with a small ‘fx’ icon next to it. Color of line depends on logical constraint chosen (e.g. success=green, completion=blue).
  • Expression/Constraint with Logical OR – Workflow will proceed when either the specified expression or the logical constraint (success/failure/completion) evaluates to true. Visually indicated in control flow by a dotted color line along with a small ‘fx’ icon next to it. Color of line depends on logical constraint chosen (e.g. success=green, completion=blue).

image

Note: In these screenshots there are labels next to the precedence constraints indicating the type of constraint chosen. This is not a default behavior. To enable this click on Tools menu, go to Options. Under Business Intelligence Designers, go to Integration Services Designers and under the Accessibility section in the General menu, check the box for ‘Show precedence constraint labels’. This is helpful for folks who are color blind and are not able to differentiate between green/red/blue lines in designer. Big thanks to Dan English for this great tip.

image

Constraints in Action

Now that we’ve seen the different constraint types, let’s examine how they’re used in conjunction with parent-child package setup. Our first use of constraints comes at the top of the child package from the script task to the sequence containers. We’ve used an empty script task as an “anchor” task. This is used as a starting point to continue on to the corresponding workflow. As we learned in our last class, we have a variable being passed from our parent package with the value of the name of the file we are currently processing.

In this first example, we’re going to look at the constraint leading to the sequence containers for the Supplier table. We’ve used and Expression and Constraint here and chosen the value for Success. We’re also looking at the value of the variable being passed to the child package. For this particular workflow, we are waiting until the value of the variable ‘Parent_TblName’ is set to “supplier”. Once both of these situations evaluate as being true, we will execute this container.

image

Now that we’re inside our sequence container, we have another set of constraints. Once again we’re using an empty script task as an “anchor” for our precedence constraints. This time we’ve got two possible paths to go down. The first is to execute an Execute SQL task. This task checks for the existence of the table (in this case, the supplier table). If it exists it will drop the table and then recreate it. The other path leads directly to a data flow task which simply loads the table specified from the flat file.

I’ve created another variable on this package called ‘AppendFlag’ which is a boolean value. The purpose of this flag is so that you can choose to load the tables with a fresh load from the flat file (the Execute SQL task path) or you can simply append an already existing table’s data with data (data flow path). The default value of the variable is false.

The first path to the Execute SQL task uses an expression and constraint which is looking at the value of the ‘AppendFlag’ variable. In order for us to go down this workflow both value of ‘AppendFlag’ must be false AND the previous component executed successfully. The other path from the script task leads directly to the data flow task which actually loads the table. For this path, I’ve set the precedence constraint to look for the value of ‘AppendFlag’ to be true. In this path, however, we’ve chosen to use a logical OR. The reason for this being that the Execute SQL task, once complete, also leads to the data flow task. Due to the data flow having two different input paths, we must use the logical OR (if you try to choose logical AND, BIDS will quickly yell at you).

Conclusion

When we bring it all together, we now have a parent-child package that passes variable values. These values are used to execute specific workflows based on the value of the variable passed. Precedence constraints are an extremely helpful and invaluable tool in your SSIS toolkit. Using precedence constraints can help you create very dynamic workflows within your packages.

Share

SQL University: Parents Just Don’t Understand

Welcome to the second week of SSIS this semester at SQL University. Today we’re going to talk about the relationship between children and parents. Ever had communication issues with your kids when you ask them to complete a chore? When they’re done, wouldn’t it be nice if they always came back and let you know they took what you said, applied it, and completed the job? What does that have to do with SSIS? Read on and find out!
Continue reading

Share
SQL_University_Web1

SQL University Lecture Series: Women in Tech

This week SQL University is on Spring Break but we’ve lined up some activities to help keep students busy (you know what they say about idle hands and whatnot!). In continuation of Women’s History Month, and properly coming off of the heels of the 24 Hours of PASS event, we’re proud to have our next talks in our Lecture Series this Friday at 1pm EST featuring the ladies of WIT Week here at SQL University.

Our Live Lecture will be happening over at SQLLunch, and as always it’s free, so make sure you go register for the event. If you enjoyed reading all of the fantastic posts from WIT week you’ll love this event. The session is going to be a round table discussion about what WIT means to them as well as discussing some of the issues they’ve faced and would like to address in the field. Audience participation is encouraged via Q&A in LiveMeeting so come join the panel. See you at the SQLLunch!

Share

SQL University: State of the Union

The new SQL University Logo

Well the new year is here and SQL University is back and better than ever! I just wanted to take a minute to bring everyone up to speed on what’s going on with SQLU.

First off the last semester we had (Spring 2010) started rather late, which pushed the rest of the schedule quite a bit. One of the unique facets of SQL University is having our coach Tom LaRock (Blog | Twitter) posting on EVERY topic, EVERY week which is quite the impressive feat! That being said, since the last semester ran a few weeks late it not only made a lot of work for him so I wanted to give him ample time off as that is a TON of writing he’s doing, which I think we can all agree, is pure awesomesauce. Also Tom has undergone a job transition, as well as I have, so it’s been a little hectic on that front as well. Due to the schedule shifts, job changes, moves and generally hectic life we decided to skip the Fall semester for 2010, hence you’ll find it missing from the overall SQLU main page.

Another reason we went quiet for awhile was we were busy putting together another major project: SQL University – The Book! No, sorry, no movie deals in the works but I think you guys will like this even better. What we’re doing is compiling all of this awesome material our professors have put together for you guys into an organized e-book companion! My hope is that we can it formatted properly for distribution via Amazon’s Kindle store but if that doesn’t work out we’ll probably just PDF it and let you guys go to town! As with the rest of this wonderful project, this is absolutely free to everyone and will be released as SQL University: Volume I, Freshman Year which includes the first two semester’s worth of blog content! This is taking a lot of time to put together so bear with us as we get that worked on.

Another big change you may have noticed, and one of the most exciting parts about this new year, is our re-branding! Our new logo comes courtesy of the wonderful folks at Revealed Design Inc. (Facebook| Twitter) and a big thanks to Aaron Nelson (Blog | Twitter) for hooking me up with them. This is a much cleaner design and look than my atrocious attempt at designing a blogger badge from before. When you visit each professor’s sites this time around you should see the new badges displayed.

Finally the other huge addition this year is our partnership with SQLLunch.com to bring you the live lecture series. We had our first one featuring Josef Richberg during SSIS week. We’ll be bringing you more this semester with some big names so stay tuned! The best way to keep up to date on all the latest news and additions to SQL University is to join our newsletter.

One More Thing…

If you’re enjoying SQL University and learning from all of these great folks in the SQL community you’ll get a chance to experience all of this in person! This Spring at SQLRally we’ll be hosting a Lightning Talk session featuring the professors of SQLU as well as some other special surprise guests. SQLRally runs from May 11-13 in Orlando, Florida and only costs $299  and $199 for pre-conference sessions (optional). Hope to see you there!

Share

SQL University: Spring 2011

The new SQL University Logo

Welcome back students! We’re very excited to start up a new semester and SQLU is back and better than ever! This semester we’re lucky to have 7 MVP’s, 7 Women in Tech (most ever!) and a Microsoft Certified Master (MCM) in SQL Server presenting topics. In addition to our regular lessons we have the SQL Rockstar himself, Tom LaRock (Blog | Twitter), hosting weekly DBA Coaching lessons on his blog as well. Our staff is also hard at work putting together an e-book compilation of the first two semesters-worth of content. We’re calling this compilation SQL University Vol 1: Freshman Year. As soon as we finish putting it together we’ll announce it via the mailing list along with communications on Twitter (Follow us @sqluniversity). What mailing list you ask? Well if you want to make sure you get all the latest news and updates for SQLU please sign up for our mailing list here.

Continue reading

Share

Creative Writing Pt II: Blogging

In the first part of this week’s documentation series we covered documentation in the traditional sense. In today’s lesson we’re going to talk about a new avenue which many are using as a sort of documentation repository and that is the personal tech blog.

Now I could use this lesson as an entire post on how to get started blogging but I won’t. Instead I will re-direct you to professor Brent Ozar’s series on How to Start a Blog. Consider that your homework assignment for this class. So if I’m not going to talk about starting a blog then what are we going to discuss? How about the significance of blogging or writing for your own personal development and growth? I’m very fortunate in that I have managed to have Tom LaRock (Blog | Twitter) as part of the faculty and this semester he is playing the role of DBA Coach and he touches on some of these points in this week’s lesson as well.

Continue reading

Share