SQLChicken.com

SQL Server DBA Tips & Tricks

Apr
30

By

SQL Server 2012: Biggest Little Core-house

Category: Syndication Tags: , 7 Comments

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

Edition Licensing today Licensing in 2012 Can I use all my cores?
Enterprise Per Processor (4 procs, 4 cores) License all 16 cores (buy 8 core packs*) Yes
Enterprise Per Processor (4 procs, 8 cores) License all 32 cores (buy 16 core packs) Yes
Enterprise Grandfathered Server (4×4 cores) + CALs (with SA) No core packs purchased due to agreement Yes
Enterprise Grandfathered Server (4×8 cores) + CALs (with SA) No core packs puchased due to agreement No (limit to 20)
Enterprise Server (4×8 cores) + CALs (with SA) License 32 cores (buy 16 core packs) Yes

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

Share

Apr
23

By

Pragmatic Works Software for MVPs

Category: Syndication Leave a Comment

PW_logo_lgThis is just a quick post to remind folks who are current Microsoft MVPs that Pragmatic Works offers NFR licenses of its software! This NFR offer includes:

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

Share

Mar
21

By

STOP! Consolidate and Listen

Category: 24 Hours of PASS, Presentation, Syndication 4 Comments

I just wrapped up my 24 Hours of PASS session on consolidation. A big THANK YOU again to everyone in attendance, who kindly put up with my horrendous rapping “skills”! As promised, below is the link to the slide deck. In the presenter’s notes you’ll find some good links and resources for consolidation.

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!

Share

Feb
28

By

Monday Morning Mistakes: Not Setting Memory Limits

Category: Monday Morning Mistakes, Syndication 11 Comments

Welcome back to another addition of Monday Morning Mistakes series. Today’s issue is one I tend to run into quite often with clients and is an important topic to know about as a database administrator. Without further ado, let’s get to our issue

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.

Read More

Share

Feb
13

By

Monday Morning Mistakes: SSIS Expressions Not Evaluating Correctly

Category: Monday Morning Mistakes, Syndication, Tips 9 Comments

M3logo

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.

Read More

Share

Jan
23

By

Monday Morning Mistakes: Remote Connectivity to SQL Server

Category: Monday Morning Mistakes, Syndication, Tips 4 Comments

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.

Read More

Share

Dec
30

By

Estimated Completion Time for Backups and Restores

Category: DBA, Syndication 1 Comment

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

[code lang="sql"]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'[/code]

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

Share

Nov
17

By

SQL University: Virtualization Basics

Category: SQL University, Syndication, Virtualization Leave a Comment

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.

Read More

Share

Nov
3

By

SQL Server 2012: Business Intelligence Edition

Category: Syndication 7 Comments

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.

image

 

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.

Share

Oct
24

By

Deploying SSIS Packages with BIxPress

Category: Featured, Syndication 4 Comments

oldbusted-newhotness

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.

Share
« Older Posts
Newer Posts »