So what’s new with 3.5? In addition to now having SQL Server 2012 support (SSIS), you may have noticed in previous released that Reporting Services didn’t get much love. That all changes with this release as we now have incorporated health monitoring of Reporting Services instances called Reporting Performance Monitor!The new dashboard includes Average Runtime for Reports, Longest Running Reports, Report Memory Usage, Average number of Rows, Active Users, et cetera:

Another great feature in this release is the Report Deployment Tool. This feature lets you quickly and easily deploy your Reports, Folder Structures, and Data Sources between Reporting Services instances!

One more major update in this release is the update to the SSIS Package Performance monitoring interface. Same great insight, new cleaner interface!

What are you waiting for? If you already have BIxPress, you can update through the regular process. Don’t have it yet and want to try it out? Download a trial copy today! Also, if you’re a Microsoft MVP don’t forget Pragmatic Works offers NFR licenses to MVPs, so go get your copies today!

You sure do got a 'purty motherboard...

Now that SQL Server 2012 is generally available to the public, many companies are looking at the new platform and trying to figure out how to move to it and take advantage of all the new cool features. Unfortunately, some folks haven’t noticed/been aware of some of the fine print that came along with this release. I think at this point, it’s safe to say, mostly everyone knows about the change to a core-based licensing model. The part that is now causing major heartache with folks is an issue that Aaron Bertrand (Blog | Twitter) recently brought up in his post that I HIGHLY recommend you go read ‘A cautionary tale about grandfathering CAL licenses in SQL Server 2012 Enterprise’.

To quickly summarize the issue, there’s a 20-core limit in place with Enterprise edition (UPDATE – Thanks for this clarification point Aaron: to be clear, the 20-core restriction *only* applies if you upgrade Server + CAL via SA. With core limit = licensed. In other words if you buy a 64-core Enterprise license, you get to use all 64 cores.)! In a nutshell what that means is if you have a server with 4 8-core processors for a total of 32 cores, and you install SQL Server 2012 on it licensed previously by CAL with SA, SQL Server will only “see”/use 20 of those cores! This is a huge deal and one I’m really surprised has not been addressed more vocally from the user community. I’ve already seen a couple of statements as strong as “based on this, we will seriously start looking at another platform”. My hope is that if enough noise is made from the customer base, Microsoft will at least up that limit similar to how VMware changed their licensing for vSphere 5 based on customer lashback. Have you or your company run into this issue yet? Let me hear your thoughts in the comments.

Yet Another Update: Per Steve Jones’ request in comments, here’s a visual indicator of scenarios and how it could affect you

*Core pack comes in pairs so 16 cores requires 8 packs to be purchased, etc.

Additionally, here’s link to licensing FAQ from Microsoft. The one you want to pay attention to (for this scenario) is the last one:

How will the 20 Core Server limit work for SQL Server 2012?

Existing Enterprise Edition licenses in the Server + CAL licensing model that are upgraded to SQL Server 2012 and beyond will be limited to server deployments with 20 cores or less. This 20 core limit only applies to SQL Server 2012 Enterprise Edition Server licenses in the Server + CAL model and will still require the appropriate number/versions of SQL Server CALs for access.

Addendum: Please note, this blog (or any other) should be your definitive source for licensing information. For that, always ALWAYS contact your local Microsoft rep as they have the details of your specific agreements and options. This post is meant for informational purposes only.

• BIxPress – Audit, Notify, Deploy and Manage SSIS Packages
• BIDocumenter – One Stop Documentation Solution for SQL Server, SSAS, SSRS and SSIS
• Task Factory – Collection of high performance SSIS components
• DTSxChange (10-pack) – Profile, Convert and Monitor. One stop DTS to SSIS Migration Solution

To get your licenses simply email our Sales folks and they’ll be happy to get you started! If you’re not an MVP and would like to try our software, you can download trial versions of all our software as well.

Download link: STOP_Consolidate_and_Listen_24HOP (ZIP file)

If you have any additional questions around consolidation, virtualization or my general sanity feel free to leave them in the comments section below, thanks!

Issue

You have SQL Server database engine installed on a system with other services such as Analysis Services, Reporting Services and/ or Integration Services and you constantly seem to run out of memory. Restarting service seems to fix the issue temporarily but some time later the same problem returns.

Solution

Always ALWAYS set max memory options for your SQL Server-related services! Setting a hard set maximum keeps your systems from “running away” with memory and causing unexpected performance issues in your environment. This becomes especially important in environments where you’re running multiple services on the same server. In a nutshell here is a breakdown of the different SQL Server services and how they utilize memory by default (read also: running with default settings in relation to memory). The two biggest problem children, in regards to memory configuration, are the database engine service and Analysis Service. Although those two are the most commonly misconfigured, I’ve outlined all four services below.

Database Engine (aka SQL Server service)

By default, the Max Server Memory (MB) setting is set to 2147483647. This is one of the first things you want to change upon a new install of SQL Server! In layman’s terms this default setting tells SQL Server it can essentially take up all of the physical memory on the server for use by the SQL Server buffer pool. Notice I said buffer pool and not SQL Server in total? Pre SQL Server 2012, this setting really is setting max memory for the buffer pool but folks have come across instance where they set the max memory setting and yet SQL Server shows it’s actually using more memory than that. Starting with SQL Server 2012, this setting actually dictates how much SQL Server (buffer pool + everything else) can use so it’s less confusing. See this post by Jonathan Kehayias (Blog | Twitter) for more details on what the max memory setting truly means: http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

Best practice suggests setting this value instead to 80% of physical memory on a server that only has the database engine running. You will need to use smaller percentage if box is sharing resources with other services. Please note this 80% rule is flexible as systems with larger amounts of memory you can increase that percentage. As an example, in the figure below you can an example where I’ve set the max memory for a system with 8GB of RAM and running only the SQL Server database engine on the box. For a great guide on setting max server memory for the engine service see Glenn Berry’s (Blog | Twitter) post on the matter: http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

Analysis Services (SSAS)

This one is really interesting as many folks install SQL Server Analysis Services (SSAS) without realizing what the configurations involved are/do. In a default installation of Analysis Services, the service’s value for LowMemoryLimit is set to take 65% of physical memory by default. Now granted this service does not suck up this much at startup (that value is controlled by PreAllocate property) but if you were to use Analysis Services while running the engine on the same box, Analysis Service will not start freeing up memory until this minimum is reached. Up until that point, any memory used by Analysis Service is exclusive to it. If you’re installing multiple services on the same server, you’ll want to not only set the minimum memory setting here, but you’ll also want to set the TotalMemoryLimit and HardMemoryLimit. Your HardMemoryLimit is really the important one you want to configure as that is the percentage at which SSAS will start denying user and system requests due to memory pressure (essentially an out of memory error). For administrators, a must-read guide for Analysis Services is the SQL Server 2008 R2 Analysis Services Operations Guide. It’s lengthy at 108 pages but you can jump to section 2.3 (Memory Configuration) to get the full details on these settings and how they function.

Reporting Services (SSRS)

SQL Server Reporting Services (SSRS) memory settings can be configured but it’s not as straight forward as the other services are. In order to configure SSRS you need to modify an XML configuration file (rsreportserver.config). In a default installation, this file is located at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer. Please note that path could change depending on what version of SQL Server you’re running, if you’re running 64 or 32 bit installation, and what drive/folder path you’ve installed your services on. If you have trouble locating it, simply do a search on your file system for the rsreportserver.config file.

SSRS, like Integration Services, is typically benign in regards to memory usage. However if you have an environment where it gets utilized heavily, especially on a system that is sharing resources with multiple services, then you may want to tweak these settings. For best practices regarding Reporting Services configurations I suggest you look at the whitepaper from SQLCAT team on Scale-Out Deployments for Reporting Services Best Practices.

Integration Services (SSIS)

Unfortunately this service’s memory usage actually can’t be configured like the other services can. Instead optimization needs to occur at the package level. Having the SSIS service installed alongside the database engine service is quite common and usually doesn’t cause too much issue so long as all the other services are configured optimally. You can read more about SSIS Design and Performance Tuning http://sqlcat.com/sqlcat/b/presentations/archive/2009/05/02/designing-and-tuning-for-performance-your-ssis-packages-in-the-enterprise-sql-video-series.aspx or watch a free webinar from Pragmatic Works on SSIS Performance Tuning: http://pragmaticworks.com/resources/webinars/WebinarSummary.aspx?ResourceID=265

Conclusion

Remember this post is to help those who have multiple services (or even all the services) running on the same server. Best practice dictates that for best performance you’d want to segregate one or all services to their own servers, but make sure you do what makes sense for your environments. Best practices aren’t necessarily one size fits all* so make sure you do your homework!

*Unless that best practice dictates not to auto-shrink your databases. Seriously, don’t shrink your databases, please think of the kittens.

SSIS Expressions

Expressions in SSIS are great. They allow you to create dynamic values for all sorts of stuff like variables, connection strings, properties for almost anything, etc. One huge issue that tends to trip up a lot of folks, especially those new to SSIS, is the evaluation of those expressions when using variables.

The Issue

You create an SSIS variable with an expression but at runtime the expression is not evaluating as expected. Instead the expression value is using the default/static value of the variable.

Quick Answer

Make sure property for ‘Evaluate as an Expression” on the variable is set to True. Without it being set to true, the variable will evaluate to the hard set value.

Explanation

Even if you create an expression on the variable through the expression editor, and even if you test the expression and it evaluates correctly in the editor, the package will not use that expression unless you explicitly set the property on that variable to evaluate as an expression. When the property is set to false, the package will evaluate the hard set value and not the expression! In order to clearly show this in action, I’ve created a quick video below showing this behavior in action.

Example:

To demonstrate this behavior I created a simple SSIS package that has a single variable named strMessage. The value I set for it is Manual text. Go to the properties for the variable, find the properties for expressions and click the ellipses button to open the Expression Builder. In the Expression window copy/paste this expression:

“This is an expression with a date: ” + (DT_STR, 30, 1252) GETDATE()

You can hit the Evaluate Expression button to verify the code is evaluating correctly. Click OK to close the Expression Builder.

In the Control Flow I’ve created a Script Task that creates a message box that displays the value of the variable. Here is the code (VB) inside the script task’s main section of code:

Public Sub Main()
‘
MsgBox(Dts.Variables(“strMessage”).value)

‘
Dts.TaskResult = ScriptResults.Success
End Sub

This code simply populates a message box with the value from strMessage variable.

Don’t forget to supply the variable name in the ReadOnlyVariables property of the script, otherwise the script task won’t be able to read the variable from the package.

If you execute the package you’ll get a pop up box that should show you this:

Notice how the value of the message is pulling from the static value of the variable and not the expression? Now stop the package from running (hit the Stop button or press Shift+F5).

Go back to the properties for your variable and look for a property called EvaluateAsExpression. Change the value of that property to True.

Now run the package again, this time you should see:

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.

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

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.

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.