THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Lara Rubbelke

Interesting Things in the World of SQL Server

  • Big Data Learning Resources

    I have recently had several requests from people asking for resources to learn about Big Data and Hadoop.  Below is a list of resources that I typically recommend.  I'll update this list as I find more resources.  Let's crowdsource this... Tell me your favorite resources and I'll get them on the list!


    Books and Whitepapers

    Planning for Big Data Free e-book

    Great primer on the general Big Data space.  This is always my recommendation for people who are new to Big Data and are trying to understand it.

    Hadoop: The Definitive Guide by Tom White 

    This will dive deep under the hood of Hadoop.  This should not be a first book for someone who is just starting with Hadoop, Map Reduce or Big Data.  Make sure you don’t get the first edition.  The third edition is the best as it also dedicates a chapter to HBase, Hive, and other tools in the ecosystem that are important to understand.

    Programming Pig by Alan Gates

    Great (and entertaining) book about Pig.  The first chapter is a really good primer on Hadoop.

    Programming Hive By Edward Capriolo, Dean Wampler, Jason Rutherglen (est publication date 10/9/2012)

    Nothing to say about this book yet – it isn’t yet released.  I will add a quick blurb when I have a chance to read it.

    “If You Have Too Much Data, then ‘Good Enough’ Is Good Enough” by Pat Helland

    Great whitepaper to discuss the tenets behind distributed systems.



    Apache Hadoop:

    Microsoft Big Data Solution:

    Windows Azure:



    Hadoop Videos on Microsoft TechNet:

    Hortonworks Video Series:

    Cloudera Video Series:



    Tim O'Reilly and Dave Campbell Explore How to Accelerate Insights from Data


    Denny Lee talks about Big Data



    Andrew Brust on ZDNet:

    Denny Lee:

    Carl Nolan:


    Cindy Gross: 

    Oakleaf Blogs (good for Hadoop on Azure):

    Buck Woody: Big Data: A Microsoft Tools Approach

    Forrester Blogs:


    Try Now

    Preview of the Hadoop-based service for Windows Azure:  

  • SQL Server Separation of Duties Whitepaper Released

    Hot off the virtual press, the SQL Server Separation of Duties Whitepaper is now available!  SQL Server 2008 R2 provides the full breadth of tools to support restrictive compliance and security requirements. This whitepaper will associate the features and options available in SQL Server 2008 R2 to meet the varying separation of duties requirements to enable the DBA to be productive and responsive while protecting applications and sensitive data.

    Key concepts in the whitepaper are:

    • Applying Security Principles to the DBA (ie the DBA is NOT sysadmin!)
    • Protecting Database and Data Access
    • Authorizing Elevated Privileges for Specific DBA Task

    I hope you enjoy the paper! :-)

  • You want to grant someone permissions to do WHAT?!?!

    Have you ever heard of these types of requests?  True story! I have had each of these and many more:

    • A customer needed to grant a business user the rights to issue a KILL command – without giving them sysadmin or CONTROL SERVER. 
    • A customer wanted to grant a user the rights to update a job – just one job – without any other changes to the job.
    • There was the case where a customer wanted to give a set of junior admins the rights to unlock a set of logins – without granting any additional rights to alter logins. 
    • And of course, there are many, many customers who are facing internal and external regulations that dictate the DBAs should not have rights to view sensitive data.  Period. 

    Managing security is never easy, and these additional requirements can cause a lot of distress to those who are trying to provide the right level of security while protecting their data, databases, and server infrastructure.  Grant too many privileges, and you open up your environment to a host of potential issues.  Grant too few privileges, and the users and administrators are unable to do their jobs. 

    Enter the Separation of Duties Framework.  The Separation of Duties Framework was originally designed to address the separation of DBA from sysadmin, but this framework may also be used to temporarily grant users elevation of privileges in a controlled and auditable environment.  The SQL Server Separation of Duties Framework will ease the process of setting up a restrictive environment while providing a predefined set of processes a DBA may use to manage restricted instances and sensitive databases. The Separation of Duties Framework is designed to empower the DBA team (or users) to be productive and responsive with processes that are auditable, secure, and extensible while being easy to implement and manage. 

    The Separation of Duties Framework was originally released in November 2010. Brian Davis (blog and twitter) and I just released v2.0 of the framework.  The framework will create database roles, signed stored procedures, and the securables needed to support the environment.  The framework is set up in the following steps:

    1. Define the roles and tasks.  Each organization will have different regulations that stipulate the security boundaries for individuals and groups. Prior to installing the Separation of Duties Framework, it is necessary to define the types of roles that will engage with SQL Server and the tasks that each role is permitted to execute.
    2. Create folders to represent the defined roles.  Create folders in a Procedures directory that will mimic the security roles you identified in the previous step. Remember that these folders are hierarchical, and each folder level will inherit the privileges of the parent folders. The Separation of Duties Framework will create roles based on the folder structure under the Procedures directory.
    3. Add stored procedures sql files to the folders created in the previous step. Create procedures or use existing example procedures available in the framework that represent the tasks each role is allowed to execute.  Place these in the appropriate folder which represents the users who are permitted to execute the task.  The Separation of Duties Framework install script will create each procedure, sign the procedures with a certificate, and grant EXECUTE permissions to the appropriate roles. 
    4. Execute the PowerShell install script. 
    5. Place the appropriate users and groups into the newly created Database Roles.

    More details on the installation process are available with the download.  Brian Davis and I will also be following up with some additional blogs with details on the framework over the next few weeks. 

  • Connect Spotlight: Rename Instance Name

    Every now and then customers ask me how they can suggest changes or behavior changes to SQL Server.  Many of us are aware of Connect, where you can add feature recommendations and vote on other people’s suggestions.  There are a LOT of recommendations, and I know Microsoft values your feedback and suggestions.  Sometimes these recommendations are grand, and others are small – in either case, your votes do make a difference on how Microsoft prioritizes features and changes in future releases. 

    Recently, a couple of customers have talked to me about their recommendations.  The first I will highlight was officially submitted by Joe Sack on behalf of his customer who would like to rename a named instance (not the server name):  This customer is in the process of upgrading to SQL Server 2008 and discovered that several applications have hard-coded the instance name in the application.  They want to minimize the downtime, and hoped that they could install side-by-side and rename the instance.  Unfortunately, this is not supported.  Because of this issue, an upgrade in this scenario will require more downtime as they will need to uninstall and reinstall the instance. 

    If this is something that you consider important, please take the time to vote on the connect submission. 

  • Microsoft Assessment and Planning (MAP) Toolkit 5.0 Beta

    Do you know where SQL Server is installed - everywhere it is installed?  Do you really know where SQL Server is installed?  Are you looking for a tool that will help you discover any rogue instances so you can better manage these instances? 

    The Beta 2 for the Microsoft Assessment and Planning (MAP) Toolkit 5.0 is now open. Join the beta review program and help influence the development of the toolkit. To participate, register for the MAP Toolkit 5.0 Beta 2 at Microsoft Connect.

    The MAP Toolkit 5.0 is an agentless tool designed to simplify and streamline the IT infrastructure planning process across multiple scenarios through network-wide automated discovery and assessments. This Solution Accelerator performs an inventory of heterogeneous server environments and provides you with usage information for servers in the Core CAL Suite and SQL Server, SQL Server 2008 discovery and assessment for consolidation, Windows 2000 Server migration recommendations, and a readiness assessment for the most widely used Microsoft technologies—now including Office 2010.

    Is your organization spending valuable resources planning its IT infrastructure? Participate in the MAP Toolkit 5.0 Beta 2. Take an early look at this release, and provide timely feedback to help ensure that the MAP development team builds the tool to best meets your needs.

    Next steps:

    · Join the MAP Toolkit 5.0 Beta 2. Then tell the MAP team what you think!

    · Send your comments to the MAP dev team.

    · It's Free! Download MAP 4.0 for planning Windows 7, Windows Server 2008 R2, and Hyper-V deployments.

    · Visit the MAP Team blog for more news on the MAP Toolkit and other IT planning Solution Accelerators from Microsoft.

    The Beta 2 will run through mid-June. That means now is the time to join the beta program, preview this toolkit, and provide the MAP team with your feedback.

    What's new with MAP Toolkit 5.0 Beta 2 (What’s in it for you and SQL Server)?

    Software usage tracking for Exchange Server and SQL Server

    Right-size your IT environment with MAP Toolkit 5.0 and simplify software license management and compliance processes. MAP 5.0’s new usage tracking feature provides consistent software usage reports for key Microsoft server products: Windows Server, SharePoint Server, System Center Configuration Manager, Exchange Server, and SQL Server. Run updated reports whenever you need to accurately assess current software usage and client access history in your environment. This reduces time and administrative costs for managing your server and client access licenses (CALs) and helps you to streamline the management of your software assets.

    SQL Server discovery and assessment for consolidation

    MAP 5.0’s new database discovery feature gives you the information you need to optimize your database resources and investments. MAP helps you simplify database administration and provides wide-ranging details of databases and server instances—information you can leverage for consolidation. Use the MAP Toolkit’s proposals to better utilize hardware and database resources, reduce administrative costs and streamline your software licensing needs— all essentials for cost effective IT planning and operations.

    Windows 2000 Server migration assessment

    As support for Windows 2000 Server ends soon, MAP 5.0’s Migration Assessment feature helps you prepare for migration to Windows Server 2008 R2 by assessing the Windows 2000 Server environment and legacy workloads in the form of proposals and reports. The MAP Toolkit’s actionable recommendations help you to understand the potential business impact of maintaining legacy workloads and the benefits of migrating to the robust Windows Server 2008 R2 environment. With migration to Windows Server 2008 R2, you’ll be able to utilize the increased IT flexibility and efficiency from such technologies as Hyper-V and Remote Desktop Services, as well as tap into power-savings features to decrease TCO.

  • Women in Technology Microsoft Career Webcast: Learn More About Microsoft Services Roles

    Since I joined Microsoft over 2 1/2 years ago, many of my friends and colleagues ask me how I like it and how things are going.  To be more precise, often these friends tip their heads to the side and ask with great concern “How are you doing?  Are you working all the time?”.  In many cases, I think this would be the same manner that they may have inquired on my state after a death in the family:-)  I don’t begrudge anyone for how they approach me in my choice to join Microsoft, and fully appreciate how Microsoft may be viewed from the outside – particularly since I was once on the outside and full of the misconceptions that my entire life outside of Microsoft would halt if I ever joined the organization. 

    My answer to these questions is a resounding “I am doing great!” and “I am not working more than I did before.” followed by an unsolicited "Best career decision I have ever made!"  In fact, many people may not realize that I actually joined Microsoft to achieve a better balance between my work and personal life while still making a positive impact on the larger community.  I can hear many people out there scoffing and laughing, but this was my motivation and I can happily say that my family has much better balance than before I joined Microsoft! 

    Now to the point of this post: Microsoft is holding a series of webcasts to promote various Microsoft technical roles in the services organization.  Anyone is welcome to attend, and we are specifically highlighting several women in our organization who are in technical roles.   There are a large number of technical roles at Microsoft, and these are but a few that may resonate with you.  I can attest that my experience with Microsoft has been fulfilling, rewarding, and I am extremely happy that I joined Microsoft.  I encourage everyone to attend and learn first-hand if one of these roles appeals to you. 

    Register Today!

    Latin America - April 28th

    North America - April 29th

    ASIA - May 24th

    EMEA  - May 25th

    Meet 12 Microsoft women.

    Across 9 countries.

    Showcasing 3 Services roles they excel at and love to do.

    Learn if one of these roles is right for you.


    About the Event

    Meet up to 12 different women from Microsoft Services and learn how they are helping our customers succeed. We are holding four webcasts to accommodate four different time zones on the three roles below. We hope you will join the one that works best for you.

    The Consultant:  Meet new customers. Help them assess their business needs. Design and deliver Microsoft technical solutions that allow them to get the maximum value for their business. Be a technical consultant. View job description.

    The Technical Account Manager:  Support a Microsoft Premier customer. Deliver Microsoft technical solutions that allow them to have the best operational health possible. Be a technical liaison across Microsoft. View job description.

    The Premier Field Engineer:  Active travel to reach many Enterprise customers. Provide proactive and reactive support to the most technically complex and business critical situations. Be the technical expert. View job description.

    Who should attend

    We are looking for women with great technical experience who have a passion for working with customers.  While our event is open to anyone interested in considering roles at Microsoft, our focus on the above three roles is targeted to those individuals with strong technical expertise, 3-5 years in the technology industry, a Computer Science/Engineering degree or equivalent experience. You must have a proven record of delivering business value to customers preferably on the Microsoft platform, technologies, and products.

    REGISTER TODAY  for the webcast which best fits your region and time zone:

  • SQL Server 2008 R2 CTP Available

    The first public CTP of SQL Server 2008 R2 is available for download.  Read more about it at the Data Platform Insider blog:


  • Automating SQL Server 2005/2000 Policy Evaluation

    The Enterprise Policy Management Framework version 3.0, a new version of the framework to support policy automated policy evaluation for SQL Server 2000 and 2005, has been posted to codeplex.

    For those who are not familiar with the tool, the Enterprise Policy Management Framework is a reporting solution on the state of the SQL Server enterprise against a desired state defined in a policy. The key capabilities are to extend Policy-Based Management to all SQL Server instances in the enterprise, including SQL Server 2000 and SQL Server 2005.  The EPM Framework will automate a scheduled evaluation of a set of policies against a group of servers, and provide reports for DBAs to understand where they have instances and database objects which are not complying with an organization’s defined standards. 

    The new 3.0 release includes the following enhancements:

    Supports nested server groups in the Central Management Server

    The previous versions did not support Central Management Server groups that were nested in parent groups.  This restriction has been removed and you may now design CMS groups to fit your organization, and leverage these groups for the EPM Framework.

    A new parameterized PowerShell execution

    The PowerShell script has been updated with parameters.  This enhancement will significantly ease how you may deploy the solution, so you only have a single script to manage.  The previous versions would have required multiple versions of the PowerShell script you were to design the execution strategy by server group and policy category.

    Policy results are stored in a table format

    The new version 3.0 will shred the policy result XML document to a PolicyHistoryDetail table during the evaluation.  The previous version only stored the XML data and issued queries against XML results stored in a SQL Server table named PolicyHistory.  This update will greatly improve performance during reporting and provides a better platform for the community to build customized views and reports. This could also improve storage – you can purge the data in the PolicyHistory table if you do not require the XML results.

    New Report Parameters

    Based on feedback from the community, the new version includes parameters in the reports to support filtering by Central Management Server group.  This will be a very important criteria for large organizations who would like to focus on specific groups of instances.

    Fixes to error reporting logic

    Not much to say, other than the logic that identifies errors stored in the tables is fixed.

    Updated Documentation

    The documentation has been updated, and should be much easier to follow when setting up the framework.


    The EPM Framework leverages the Central Management Server, PowerShell, Reporting Services 2008, and Policy-Based Management.  You will need at least one instance of SQL Server 2008 and an instance of SQL Server 2008 Reporting Services to support the framework.  I will dive deeper into installation and configuration of the framework in subsequent blogs. 

    Please let me know if you are using the framework, and if you have suggestions for future enhancements.  I am going to be integrating SQL Server 2008 Policy History centralization into the framework in the next version.


  • Should I Compress My Indexes? New Compression Whitepaper!

    A very well written study of the compression is now available: Data Compression - Strategy, Capacity Planning and Best Practices.  This study provides guidance on best practices for designing, implementing and managing compression in your environment.  I found this whitepaper to be full of useful guidance and scripts to monitor and manage compressed tables. 

    The paper starts with tips and tools to help decide what to compress.  It then guides the reader through considerations for implementing compression and estimating necessary resources to complete compression.  The final sections of the paper provide guidance and evidence on management topics – space management, performance management, partition management. 

    I am certain that those who are considering compression will find these concepts, scripts and tools to support the full lifecycle of compression.  I know that I will keep this on my short list of favorite whitepapers!

  • Achieving PCI Compliance Resources

    This past week I was delivering some events for customers on supporting Mission Critical databases with SQL Server 2008.  During the compliance conversation I mentioned that there were a couple of new resources available related specifically to PCI compliance. 

    Parente Randolph published a whitepaper on how to leverage SQL Server 2008 features to meet PCI compliance. 

    Furthermore, these auditors presented a TechNet webcast on the same subject.

    I would also highly recommend the SQL Server 2008 Compliance Guide which will complement the above resources with technical implementation examples.

  • Another EKM Vendor Announced Support

    This week Thales announced that their nCipher product line now integrates with SQL Server 2008 EKM.  This announcement follows SafeNet as our second vendor to support EKM. 

  • Standardized SQL Server 2008 Audit

    Isn’t life grand when you have a standard approach?  That is typically my frame of mind, which is one reason why I advocate for a standard approach to designing, deploying and managing your SQL Server 2008 audits.  I have developed the attached standardized scripts which should get you started on your standardized approach.

    The script SetupAudit_Shell is a single SQLCMD script that will create the Audit, the Server Audit Specification, and a Database Audit Specification in every user database (except Model and TempDB).  The script is written to send the audit data to a file destination.  You will need to alter this part of the statement if you prefer to send your audit data to the Windows Security or Application log.  The Server Audit Specification and the Database Audit Specification statements do not specify the audit action groups – you will need to supply these based on what you want to audit. 

    The script SetupDataAccessAudit_Shell is a script to alter an existing Database Audit Specification with table level audit actions.  This will apply the exceptions to your standard audit when compliance goals require you audit DML activity from tables which store sensitive data.

    The remaining scripts (DisableAudit_Shell, EnableAudit_Shell, RemoveAudit_Shell) are scripts to help manage the audits which are deployed with the defined standards.

  • SQL Server 2008 Auditing Resources

    I love auditing!  I know – I hear it all the time – I am very strange:-)  Since I first started working with SQL Server 2008 in the early CTP process, Auditing was near or at the top of my favorite features in the new version.  Below are some of my top resources to help you understand auditing – and build some great solutions to centralize auditing data from multiple sources. 

    A new whitepaper was recently published as a comprehensive description of the new feature, followed by comparisons of capability and performance with other methods to collect and store audit data.  This whitepaper is a great resource for anyone who is researching methods to implement an auditing capability. 

    In addition to the excellent whitepaper just mentioned above, the SQL Server 2008 Compliance Guide is another great resource for understanding how to implement and manage your SQL Server 2008 Audit feature and data.  Please be sure to download the complementary SDK which includes some helpful solutions to jump start your audit initiative.  I am particularly impressed with the materials in the SDK co-developed by Denny Lee (Microsoft SQL CAT) which centralize audit data from multiple sources to a single audit warehouse – complete with reports!  When you download the SDK, you will find the various solution files (SQL scripts, SSIS and SSRS projects) in the SQL Audit folder.  This is a great solution to get you started on compliance reporting across a number of instances. 

    I will be releasing some additional audit materials in the next couple of weeks.  These will include enhancements to the reporting available in the SDK (let’s pull audit and Policy history data together!), and standardized master audit scripts to generate consistent audits across all instances and databases.  If you are in the St. Paul/Minneapolis area, please consider attending the upcoming TechFuse conference on March 17 to see these materials in action.  Can you think of a better way to spend St. Patrick’s Day? :-)

  • Enterprise Policy Management: Updates, New Whitepaper, and Upcoming Webcast

    The next version of the Enterprise Policy Management Framework has been published to CodePlex.  The new version includes error trapping and error reporting, and a few bug fixes.  Please be sure to download the documentation as well, which will help you understand how to use the PowerShell variables to scale the execution in your environment. 

    A new whitepaper has been published outlining the intent of the Enterprise Policy Management Framework. A big thank you to Tom Davidson for all of the hard work writing the paper.

    Finally, anyone who is interested in an introduction to Policy-Based Management and a demonstration of the EPM Framework in action can attend my TechNet webcast next Thursday, March 12: TechNet Webcast: Governing Your Enterprise with Policy-Based Management (Level 300).  This webcast is best suited for those of you who…:

    • are currently frustrated with managing a SQL Server environment that is inconsistently deployed
    • want to reduce the time you spend developing and running scripts to determine issues in business and regulatory compliance
    • simply want to see a better way to manage groups of servers to better scale your time

    Join in the webcast, it is sure to be fun and enlightening!  In short, you will walk away with an understanding of Policy-Based Management, how to use the SQL Server 2008 Policy-Based Management framework to support down-version (SQL Server 2000 and 2005) policy evaluation, and how to implement the EPM framework (see picture below) for an understanding of policy compliance across the enterprise. 


  • Happy Square Root Day!

    For those out there like me who find a weird sense of joy by discovering math patterns in our daily life, Happy Square Root Day! 

More Posts Next page »
Privacy Statement