SQLServerPedia

Inspired by common emails and questions I  see, I figured I’d do a series of blog posts on common mistakes folks make with SQL Server called Monday Morning Mistakes (or #sqlM3 for short, since we all love quick hashtags these days). These are meant as quick fixes, nothing too comprehensive. Also since I just made up a hashtag, feel free to share your own #sqlM3 tips on Twitter anytime! Without further ado…

Today’s quick issue: Can connect to SQL Server locally but can’t connect from other server or computer.

Quick answer: Remote connections (read also: any connections that are not local) to SQL Server are disabled by default. This behavior is default in SQL Server 2005 and higher. You have to manually enable TCP/IP protocol to instance to allow connectivity. This requires a service restart to take effect.

1. Open Connection Manager, go to SQL Server Network Configuration.

2. Select the Protocols for the instance you’re wishing to allow remote connectivity.

3. Enable TCP/IP by right-clicking it and selecting Enable.

4. Restart the SQL Server service.

Additional tips: If you continue to have access denied problems don’t forget to check the SQL Server error log for clues as to why connection isn’t working (i.e. incorrect password error). Also check to see if firewall might be affecting connectivity.

I’m in the middle of a database migration and thought I’d quickly share a script I threw together to show estimated time of completion for a database restore in progress on SQL Server. The script will also show you estimated time for database backups to complete as well.

Please don’t take this script as gospel, the best way to truly know how long restores will take is to actually perform a restore! Remember folks:

Backups are worthless, restores are pricess

SELECT
	session_id,
	start_time,
	status,
	command,
	percent_complete,
	estimated_completion_time,
	estimated_completion_time /60/1000 as estimate_completion_minutes,
	--(select convert(varchar(5),getdate(),8)),
	DATEADD(n,(estimated_completion_time /60/1000),GETDATE()) as estimated_completion_time

FROM    sys.dm_exec_requests where command = 'BACKUP DATABASE' OR command = 'RESTORE DATABASE'

NOTE: Due to the fact this script uses DMV’s, will only work on SQL Server 2005 and higher

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.

First things first, we need to familiarize ourselves with some basic terminology. These concepts are the same no matter what brand of virtualization so don’t worry about specifics. Later on we’ll dive into the different platforms/vendors and what they offer, but for now we’ll stick with general concepts and terms.

Host

Earlier I talked about buying a physical box to house your virtual machines on. This physical server is referred to as a host. The host contains all the physical resources that we will be allocating to our virtual environments including memory, CPU, networking and disks (I/O). Granted you can attach alternate methods of storage to your host, such as a SAN or NAS (which is common) for your storage needs, for these lessons we will refer to storage as being direct attached storage (DAS).

Hypervisor

The hypervisor is essentially a special type of Operating System, also referred to as a virtual machine manager/monitor, that is installed on the hardware (host) and its purpose is to present a platform between the hardware and the guest to allow multiple operating systems to share a single host and its resources. In a very simplistic way, think of the hypervisor as the traffic cop between each guest and the resources on the host. If multiple guests are asking for memory or CPU the hypervisor is the one that doles out the goodies to everyone in a quick and efficient way. The hypervisor is the “secret sauce” for virtualization and what makes all the magic happen.

Guest

When you create a virtual machine on your host, it is referred to as a guest. A guest, or virtual machine (VM), runs as an independent machine. The beauty of virtualization is that you can create a multitude of guests on a host, all running different operating systems. Once configured a guest VM looks/acts just like a regular server or machine on the network. Each guest can be independently configured with its own resources such as virtual processors, memory and virtual disks.

This is where you will be running SQL Server. When you remote desktop into this machine and go to control panel/device manager keep in mind you’re not seeing real hardware, you’re looking at virtual hardware that is presented to you via the hypervisor.

Abstraction

Technically this isn’t a virtualization term but it’s a concept you’re going to need to need to be really familiar with when virtualizing. Abstraction essentially means when something is presented in a simplified format but underneath it you have more complexity that is involved but you don’t necessarily have to worry about for your use. An example of this in the database world would be a view. You create a simple view that you can select from that looks like you’re selecting from a single table. In reality, that view’s definition is actually the joining of one or more tables together to get the result set for the view. By creating a view, you simplify the work for the end user by letting them query one “table” instead of having to do the work of joining several tables to get what they need.

Abstraction has you, Neo...

This concept extends over to the world of virtualization quite a bit. When you create a guest on a host, you create it with a certain set of resources. For example you create a virtual machine guest with 1 virtual processor that is 2.5 GHz and 2 GB of RAM (keeping this simple for now). The abstraction occurs when the hypervisor creates the guest, it creates it saying ‘you have X amount of memory and you have Y CPUs that are Z fast’. Behind the scenes, however, the host is doing something else. While the operating system on the guest says “I have 2 GB of memory”, the host is really only allocating as much memory as the guest needs at the time. So in reality the host is only allocating 128 MB of memory to that guest at that given time. The 2 GB you “gave” the guest, can almost be viewed as a more of a max memory option.

If the guest becomes really active and requires more resources, the hypervisor gets this request and allocates those resources from the host and subsequently passes it to the guest, up to the maximum of what you allocated to the guest. During all this process, the guest is never made aware of any of this shenanigans going on behind the scenes. It’s simply a server with 2 GB of memory doing it’s typical routines! Now, on a simple system you may never notice any performance issue with this, and a good hypervisor makes this situation seamless as you should never really see the effects of this process affecting your performance…until you do.

This also occurs with processing power as well. We gave our guest one virtual processor that runs at 2.5 GHz, so we’d expect if we were to run a process that is CPU intensive, that CPU would be running at speed of 2.5 GHz. Again, an administrator has the ability the throttle these resources so from the hypervisor so while activity monitor within your guest can show 100% utilization but on the host side, the CPU can really only be 25% utilized.

This is where understanding what resource allocation and abstraction becomes crucial in architecting a proper virtualization environment. Now that you’ve understood some of the core concepts, in our next lesson we’ll talk about how SQL Server fits into this whole picture and what you need to account for to ensure your virtualization project goes well.

Well this was quite the little surprise this morning. Microsoft announced a new edition to the SQL Server lineup for 2012 – Business Intelligence edition. In addition to a new edition (funnily I don’t see Datacenter in that lineup) we also have a new licensing scheme for SQL Server. In SQL 2012 it looks like Microsoft is finally moving to the core-based licensing model. Ladies and gentlemen, start your grumbling! Okay, seriously, the new licensing scheme shouldn’t be that big of a shock to anyone. I think most of us have been expecting this for quite some time as it only makes sense as newer processors are coming with more and more cores.

As for the new edition of SQL Server, I think it’s an interesting move to say the least. As SQL Server adoption in the enterprise keeps going up, it kind of makes sense that they’d make a dedicated edition for the BI stack. The last few releases of SQL Server have been BI-feature heavy and when you’re architecting your setup, you should be setting up dedicated boxes (if possible) for the BI stack anyways. In my eyes this is a pretty smart move, although I’m sure some will disagree. With the separation of church and state Engine and BI you can now have a little more flexibility in your choices, especially regarding licensing.

So what does the new licensing change mean for you? Should you be worried? Well if you’re not sure how your licenses are currently distributed or what you have out in your enterprise deployed right now, I HIGHLY suggest you download and use the MAP Toolkit. This free tool will not only discover instances in your enterprise (not only SQL Server!) but it will give you some really great detailed information including usage information (this is a must-use tool if you’re considering consolidation), editions, number of cores, etc. Run it against your environment and then have a chat with your local Microsoft rep about how the new changes might affect your existing infrastructure.

What are your thoughts on the new changes? Like it? Hate it? Don’t care? Let me hear it in the comments.

Same functionality but obvious differences

If you’ve worked with SSIS for any amount of time, you may quickly come to find that the native way of deploying packages can be…cumbersome. The native tools, while helpful, aren’t exactly the most intuitive to setup or use. This post will show you how you can quickly and easily deploy and configure packages using BIxPress.

Old and Busted

Before I show you how to deploy packages, I should probably quickly explain how to deploy packages in SSIS using native methods. I won’t go into every single detail here on how to deploy packages natively, however, if you’re interested in doing it step-by-step the built-in Help in Business Intelligence Development Studios (BIDS) has a complete walkthrough tutorial for you to check out. To access those tutorials simply press Ctrl+F1 from within BIDS (or click on Help menu and select How Do I from menu). From the ‘How Do I?’ list click on Find Tutorials, Integration Services and then select your tutorial. The one I’m referring to in this post is the Tutorial: Deploying Packages.

The condensed version of the tutorial is this: in order to deploy packages you have to go through a series of steps that aren’t exactly obvious from the interface. First, you have to manually enable the ability to even deploy. You get to this by going to the properties of the project, go to Deployment Utility and set the CreateDeploymentUtility option to True. Once you’re done doing that, you have to build (or rebuild) the project for it to generate what is called a Deployment Manifest file. This file is saved to the file path configured for DeploymentOutputPath where you set the properties for the Deployment Utility. This part alone reeks of user-interface fail to me, but I digress.

Once you’ve created your deployment manifest you’ll need to copy that manifest file out to a share on the target server. After you’ve copied it there, you double click it to launch the Package Installation Wizard. This wizard is pretty typical of Microsoft wizards and is pretty straightforward as far as walking you through your various options. For complete details on deploying using the wizard, refer to the tutorial in the Help. By the time you’re done with the wizard you’ll have deployed the package but your options for customization of deployment are limited.

New Hotness

After learning SSIS over the past year, one of the things that BIxPress has absolutely spoiled me with is the ease of deploying packages. In BIDS just right-click on your package and select Deploy SSIS Package (BIxPress) from the context menu. This will launch the BIxPress Package Deployment wizard. The first screen that comes up gives you a few really cool options such as copying folder structures (if needed), deploy XML files for you if you used XML configurations, you can change the location of those configuration files on your target server, and even change the package level protection from here. These options here have made deployments a breeze for me as on QA servers I had clients putting configs in D:\SSISConfigs and on production it was something different like E:\SSIS_Configs. Being able to quickly and easily change these options on the fly has saved me tons of headaches.

The next screen is the real meat of this feature. Here you can actually select more than just the one package you right-clicked initially for deployment. Additionally you have lots of options for deploying to and from a server. You can deploy to/from your regular options of File System, SQL Server or SSIS Package Store but here its easily laid out for you for ease. Speaking of ease, ever wanted to deploy in the opposite direction (i.e. production to development)? Simply check off the box ‘Enable 2-way deployment’ and you can quickly deploy bi-directionally quickly and easily. Pretty slick, eh?

Once you check off the packages to deploy and select your deployment destination options, simply click the deploy button in the middle and it quickly deploys your packages. Once it is complete you get a summary of the deployment results which you can save for change management purposes. That’s it, you’re done!

If you want to try out BIxPress you can download a trial copy from the Pragmatic Works website.

Today I was planning on writing a summary post of PASS Summit experience but something happened last night that caused me to change up the queue for blog posts and quite frankly bothered me. Today I’d like to address a few things regarding the Community, behavior within it and just general thoughts about stuff. I apologize ahead of time for the word vomit you’re about to read.

So last night a certain individual began ranting to certain folks on Twitter about how what he thought about the MVP Award and how it seems like they “hand it to anyone now” based on “printing out a card for after hours events”. This person (whom for the time being I’m simply refusing to mention) had an opinion, which is fine. When myself and some others started reading this we began defending whom they were talking about, which in this case was Jen McCown (Blog | Twitter) of MidnightDBA fame. What was funny to me was that this person didn’t seem to want to have a reasonable conversation, they seemed to have an almost personal grudge. Even in email format (yes, some of us tried to reason with him in private as well), he kept up the childish name-calling and outlandish behavior.

Now granted, if you don’t like the MVP program or who is awarded, that’s fine and dandy and you can let the folks at Microsoft know (Blog | Twitter | Facebook). Everyone is entitled to their opinion, but when you put your opinion in a public forum and others challenge you on your statements don’t whine about it and throw a fit. Don’t break down into childish attacks. Don’t start attacking everyone with ridiculously stupid statements and then claim people are attacking YOU. Yes, all of this happened and more last night. Missed the fun? This guy got a new hashtag generated for him aptly named #sqlidiot.

Another interesting point came up during our “conversations” with this guy, namely he made a statement about us whining and we were taking Community vs Real Life. Let’s think about this for a second. The SQL Community is not exactly huge, and if you attended PASS Summit last week you get the sense that it’s more like a global family. Most of us know each other offline and a lot of us have never met but when we do finally meet in person you know each other so well you actually FORGET the fact that you’ve never met! We celebrate our triumphs together. We share our pain together. We pray together. We lift each other up and support each other. Hell, we even officiate each other’s weddings! This is Community. This is Family. To think that our interactions are limited to digital medium is both nearsighted and flat out wrong, it’s only a small part of a very large (and global) picture.

As witnessed last night you can see how protective we are of each other. Noticed I have not made mention of status at all. That’s because something like the MVP award, while cool, doesn’t mean you can’t/don’t belong in this family. From the person looking to start writing their first SQL query, to the professional speakers, to the folks writing the engine for the products we all know and love we are all One. Yeah, sounds a little over the top existential but I truly feel that way about this Community. When someone goes on a public forum and starts tearing others down, for no apparent reason whatsoever, don’t be shocked when you have quite a few folks fighting back. You may be brave behind a keyboard but I’d love for someone to try that nonsense at a SQLSaturday event or PASS Summit. It’s not Community vs Real Life, Community IS Real Life and I will defend it, and the people that make it up, until the very end.

Finally there’s general conduct. We’re all entitled to our own opinions but how you express those opinions, especially in a public forum like Twitter, is critical. I can have a conversation with someone and not see eye to eye with them, that’s fine. Resorting to childish name-calling and tired/pathetic ‘your mother’ comebacks just makes you look like an absolute idiot and you lose any and all credibility you may have had to start with. Some people tend to forget the acronym PASS stands for the Professional Association for SQL Server, emphasis on professional. While we do tend to have our after hours and colorful fun, you’ll rarely see someone all-out break that professional decorum. That’s a matter of respect, for both yourself and the people you interact with. Think about the consequences of your actions, ESPECIALLY in a public forum. The Internet, as they say, is forever (and Google Bing Bingle has a long and easily searchable memory).

I know some of you followed along closely last night and even chimed in with this guy, some of you lurked, some are probably hearing about this for the first time. What are your thoughts?

CLOUD! BIG DATA! EXCEL! CLOUD! CLOUD! Okay, recap done. Not really…sort of. In all honesty, while delivered in a fairly terrible fashion, there were some pretty big announcements made in today’s keynote. First let’s start with the one a lot of folks have been waiting on….

Official Names Revealed

The release of SQL Server we’ve known as “Denali” for the last 12 months now has an official name: SQL Server 2012! I know, not exactly exciting but at least it’s nice to have an official name. Also, since according to Mayan calendar the world ends this year anyways, this is THE LAST VERSION OF SQL SERVER YOU’LL EVER NEED!!! In addition to Denali SQL Server 2012 getting a name, we also got the official name for project “Crescent”, which is now officially known as PowerView.

BIG Data on Windows/Azure

Those worried about the NoSQL movement and how Microsoft would play in that space? No more worrying, now you get best of both worlds with the announcement of Microsoft’s support for Hadoop on Windows and Windows Azure! This is actually pretty exciting even though, in this blogger’s humble opinion, this kind of scale doesn’t matter for 99% of the folks out there. With this announcement, however, Microsoft has made huge strides in make the Cloud more relevant for big businesses. Want a multi-terrabyte system that scales? Windows Azure can handle that for you now. Want to handle that internally? Local options also supported. Or create a hybrid solution, the possibilities are actually fairly cool here.

The other story that was sold is that you can use Microsoft BI stack against your data in Hadoop. An example of this was shown by using PowerPivot to connect to Hadoop on Windows via the new ODBC connector. This connector will be available sometime in November as a CTP download. Speaking of connectors, Microsoft recently released connectors for PDW as well so you can connect big data with big iron for those who need that kind of data firepower.

Project “Data Explorer”

They also showed off a new tool which allows you to explore and merge data from Azure marketplace and various data sources. They spent a good chunk of time demoing bringing together data from Azure Marketplace, SQL Server and some other sources. Honestly I started tuning out a bit at this point since the #sqlpass stream became “interesting” at that point.

The rest of the keynote consisted of a rather downplayed series of demoes in Excel/PowerPivot/Power View. If you’d like you can check out the keynotes yourself here.

Today on Twitter my friend Jes “Run Forrest Run” Schultz Borland (Blog | Twitter) asked the Community “How do you clean up your dev environments? Let DBs sit out there forever? Delete after X months? Other? This seemed like an interesting issue to tackle and me being the PBM freak that I am, immediately I had a light bulb moment for a policy. In this post I’ll show you a policy you can run against your databases (can work in dev or whatever environment suits you) and will tell you which databases are older than 30 days old. As an added bonus, I’ll also show you how to add a custom extended property to set a custom expiration date.

Custom Expiration Date

First we’re going to modify our existing databases to define an expiration date. How are we going to do this? We’re going to add an extended property to our database. To do so on one database use the stored procedure sys.sp_addextendedproperty. The following code uses this stored procedure to add an extended property called ‘ExpDate’ with a value of ‘11/11/2011’.

EXEC AdventureWorks.sys.sp_addextendedproperty @name=N'ExpDate', @value=N'11/11/2011'

Why are we only doing this on one database? Well it will make more sense later when we run our policy against all our databases in our instance. Next up, the policy itself. As always I’m providing both the T-SQL for the policy or you can download the XML and import the policy directly.

Download ‘Expire Development Databases’ Policy Here

T-SQL:

--Create condition first
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Databases older than 1 month', @description=N'', @facet=N'Database', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>OR</OpType>
  <Count>2</Count>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>GE</OpType>
    <Count>2</Count>
    <Attribute>
      <TypeClass>DateTime</TypeClass>
      <Name>CreateDate</Name>
    </Attribute>
    <Function>
      <TypeClass>DateTime</TypeClass>
      <FunctionType>DateAdd</FunctionType>
      <ReturnType>DateTime</ReturnType>
      <Count>3</Count>
      <Constant>
        <TypeClass>String</TypeClass>
        <ObjType>System.String</ObjType>
        <Value>day</Value>
      </Constant>
      <Constant>
        <TypeClass>Numeric</TypeClass>
        <ObjType>System.Double</ObjType>
        <Value>-30</Value>
      </Constant>
      <Function>
        <TypeClass>DateTime</TypeClass>
        <FunctionType>GetDate</FunctionType>
        <ReturnType>DateTime</ReturnType>
        <Count>0</Count>
      </Function>
    </Function>
  </Operator>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>GT</OpType>
    <Count>2</Count>
    <Function>
      <TypeClass>DateTime</TypeClass>
      <FunctionType>ExecuteSql</FunctionType>
      <ReturnType>DateTime</ReturnType>
      <Count>2</Count>
      <Constant>
        <TypeClass>String</TypeClass>
        <ObjType>System.String</ObjType>
        <Value>DateTime</Value>
      </Constant>
      <Constant>
        <TypeClass>String</TypeClass>
        <ObjType>System.String</ObjType>
        <Value>SELECT value FROM sys.extended_properties&lt;?char 13?&gt;
WHERE name = ''''ExpDate''''</Value>
      </Constant>
    </Function>
    <Function>
      <TypeClass>DateTime</TypeClass>
      <FunctionType>GetDate</FunctionType>
      <ReturnType>DateTime</ReturnType>
      <Count>0</Count>
    </Function>
  </Operator>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

--Now create the policy itself using the newly created condition
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Expire Development Databases_ObjectSet', @facet=N'Database', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Expire Development Databases_ObjectSet', @type_skeleton=N'Server/Database', @type=N'DATABASE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Expire Development Databases', @condition_name=N'Databases older than 1 month', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'Expire Development Databases_ObjectSet'
Select @policy_id
GO

So let’s talk about this policy first before we run it. The condition on this policy is checking for two things. The first is to see if the creation date of the database is more than 30 days from the current date. Of course you can go into the condition and modify it to whatever you’d like but for the purposes of this post we’re using 30 days. Additionally we also have another clause using OR that is checking the value of the extended property field to see if that custom date is greater than the current date. How did we do this? We use the advanced editor in the condition creator and use the executesql function to query the database for the value of the ExpDate property. If no extended property exists for that database then the policy will simply use the 30 day condition clause mentioned earlier.

So now that we’ve seen the policy and its condition let’s see it in action! I’ve run this policy against the databases on my instance and we get mixed results here. Overall we see a bunch of databases that don’t pass the policy but we have one that does.

As you can see from the screenshots above, the policy that passed did so because it passed our secondary clause in our policy. The policy was run (at the time of this posting) on October 6th 2011. The expected expiration date from the first clause expects a date 30 days ago, which is September 6th. Our second clause, however, instead looks at the value of the extended property we set earlier. Since today’s date is less than that of the property set value of November 11th, the database is still considered to be current and active!

Next Steps

While this policy evaluation doesn’t let you take any action against the databases, this does give you a listing of what databases you should be paying attention to. This method lets you quickly identify which databases you should be “cleaning up” in your environments. You can schedule a policy like this to run only against your development environments by using the Central Management Server’s (CMS) functionality in conjunction with the Enterprise Policy Management Framework. The nice thing about using EPMFramework here is that results are saved in a database and you’re able to see those results in the great built-in reports. To see how to use CMS with Policy-Based Management, check out my webinar from the recent 24 Hours of PASS event!

[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.

Backups, Backups, BACKUPS!

Before we even start getting into hardware for your database servers, answer this question: Are you taking backups? I’m not only asking about SQL Server backups (which you REALLY need to be doing), but backups of any and all of your data as well. There are tons of options for backup devices out there, I highly recommend you add this to your shopping list if you’re putting together hardware list for your budding IT department. I lead with this advice because we can setup the most awesome server setup in the galaxy but it doesn’t do you a bit of good if the power goes out suddenly and all of your databases get corrupted and you have no backups to restore from! If you’re unsure of what sort of backups and maintenance you need to setup for your databases, don’t sweat it, Brad McGehee (Blog | Twitter) wrote a great (and free) e-book on setting up SQL Server Maintenance Plans that I highly recommend you get/read right now. You can also check out Ted Kruger’s HA/DR week from last semester to help you wade through your options.

I can’t emphasize enough how important backups are in your environment, ESPECIALLY in regards to SQL Server. Your data is the lifeblood that powers your business, without it you’re dead.

UPS

As much as I’m a fan of the folks in the ugly brown uniforms, I’m not talking about the delivery service here. UPS stands for uninterrupted power supply. Basically this is a giant battery that you’re going to plug your critical equipment (i.e. servers, routers) into and then plug the UPS into the wall. What happens is that when the power goes out you’re the UPS will continue to power your equipment off its battery until either the power comes back on or you manually shut down the servers yourself. Again, there’s a slew of UPS options out there you can purchase.

There’s a few things to watch out for here, however. UPSes come in all sizes and capacities. The smaller the size, the less battery capacity it has and the less load it can handle. Also because of the different capacities and sizes, that will affect how long it can run on battery before the UPS dies out as well. I mentioned that when the power goes out you’ll need to time to manually shut stuff down. Well UPS systems can’t run forever and the power outage may only be a few minutes or it can last way longer than your system can handle running on batteries. When the outage is going to exceed your UPS’ capacity, start shutting down your systems manually. During this period it will let you save anything pertinent and gracefully shut down systems such as SQL Server. Do NOT try kicking off full backups or anything extremely intensive when your switch to running off the UPS as the more workload you put on the server, the more power it is going to need from the UPS and the less time you’ll have running off batteries. So again, just make sure you have sound backup strategy in place.

Servers

Ah finally, the hardware. Like with everything else, you’ve got quite a few options in this department. I’ll lead off this section by pointing you to a fantastic resource in the SQL Server hardware department and that resource’s name is Glenn Berry (Blog | Twitter). Glenn wrote a fantastic book, which Brent Ozar wrote up a great review on, that covers EVERYTHING you need to know to choose the right hardware to run SQL Server on. I won’t go into specific details about what you “should” be using, because it differs for everyone. Again, there are many different options out there but I’ll just do an extremely high-level overview of a few options.

Desktop/Tower Servers – Okay a server is really nothing more than a regular machine on your network with some specialized software running on it. Technically you can fire up a Windows XP laptop, enable IIS on it and call that a server (I really wouldn’t recommend this as a production solution, by the way). Desktop/tower servers, or rather machines that are essentially running on what look to be regular desktop equipment, have a few differences compared to your regular desktop machines. One of the main differences is that there isn’t only one hard drive in these machines. If you fire up a desktop server you may only see a C:\ drive but behind the scenes you’re probably really looking at two or more hard drives setup in a RAID array.

In small businesses and offices, it’s often common to go with a tower/desktop as a server due to costs or simply space. With a tower you can just set it up under a desk or tuck it in a corner somewhere. While this is a relatively easy setup there are a few issues you should be aware of. If you place a tower server on the floor somewhere, especially under your desk for instance, you could risk accidental damage like kicking it or spilling your morning coffee on it. Probably not the best thing for electronic equipment! Also tucking a machine like that underneath or behind something you run the risk of overheating, which can be really bad for the system.

Another fun issue I’ve seen in small business before is someone plugging in vital equipment (such as server or router) into a wall socket that is controlled by a light switch. I know someone who worked at a company that noticed everyday around 5 or 530 their entire network would go down. It turns out that the last person leaving the office was flipping off the light switches and one of the routers was plugged into it. Once they turned off the lights, they turned off the router which brought down their entire network. Fun times.

Rack-mount servers – These are the servers you typically see in a data center. The obvious drawback here for small businesses is that you would need to buy the rack in order to support these types of servers. On the flip side, if your company has need for multiple servers then buying rack and going with rack-mount servers is probably the better bet. I say this because the tower servers can be cumbersome in regards to finding space to store them. The other nice thing about rack-mount servers is that they’re elevated off the floor so the hardware is protected by things like flooding (unless your UPS is also on the floor then you might run into issues).

Racks are also great because it just makes things more organized. Typically you can mount power strips along the back and also put in other equipment such as network routers in with the servers, so everything is organized nicely in a single space. Also they make rack-mounted UPS units so again, big win for everyone! The obvious drawback for rack-mounts is that the rack needs a place to go. Typically you setup a rack in a dedicated closet or room somewhere in your office.

A few things to note when setting up servers in your closet/server space, YOU NEED COOLING! This hardware is up and running 24/7 and it will generate heat. Make sure you take this into account when selecting your hardware setups and locations.

Storage

You’ve got your horsepower now you need somewhere to keep all that data. Again, a few options here. Direct attached storage (DAS) is the most common form of storage and the one you’re probably used to. That drive in your laptop now? That’s direct attached storage. One, lonely spinning wheel of magnetic death. While this option may be cheap and easy, don’t skimp on your business. If you’re going to do DAS then protect yourself and setup the proper RAID groups. Since we’re focusing on SQL Server here, check out this Storage Top 10 Best Practices article from the SQLCAT team.

To get a good overview on storage, check out Brent Ozar’s SQLU Storage Week posts, which while we’re focusing on small businesses this week, still apply across the board no matter what size company.

The Cloud

Really, this again? Yeah, this again. I’m telling you, Cloud strategies are more than just marketing fluff, it’s an extremely viable and cost-effective solution especially for small businesses. All that stuff we’ve covered today, all of that is handled in background for you if you decide to instead have your infrastructure hosted using a cloud solution like Windows/SQL Azure.

So class, is there anything else you think is missing from this small business puzzle? How is everyone doing on their homework assignment from last class? Let me hear from you in the comments!

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.