IT Pro Tips : SQL Server 2008

Plan Freezing with SQL Server 2008

itprotipsadmin — Fri, 21 Mar 2008 05:01:00 GMT

By Ronald Barrett

With the advent of SQL Server 2008, many DBAs worry about issues that sometimes arise with updating query plans during the upgrade process. Plan Freezing allows you to store plan guides, which can be reverted to if queries either fail or drain resources after the upgrade. Plan Freezing allows stability to queries and comes with several monitoring features to check if the query succeeded or failed.

In the TechNet Virtual Lab: Plan Freezing with SQL Server 2008 we will see how to prevent query plan regression after a server upgrade, use plan freezing to optimize query performance, and copy query plans between servers. Before proceeding with the lab, Microsoft suggests that you be familiar with the SQL Server Management Studio (SSMS) and with Transact- SQL (T-SQL) Programming.

This virtual lab will teach us to:

Prevent Query Plan Regression

Optimize Query Performance

Copy Query Plans between Servers

TIP: Within the console, there are text instructions for each exercise. These contain copy commands for the T-SQL programming to simplify the tasks. Just copy and choose "paste text from host's clipboard" from the action menu in the console.

Exercise 1

In the first exercise, we will generate a plan script onto a test server, deploy the script to a production server, verify the query plan on the production server, and finally, generate a database clone.

We can see how plans are disabled…

And then enabled again for the CustomerSales Script…

Exercise 2

In the second exercise, we run mission critical queries and create plan guides for them, inspect the execution plan of the guide, remove records and confirm that the same execution plan is being used, and then we will disable the plan.

You can see the results of the execution plan in Exercise 2:

In addition, you will compare the three results to see what has changed in each scenario.

Exercise 3

In our final exercise, we generate a plan script on a test server, deploy it to a production server, verify the query plan is on the production server, and finally, generate a database clone.

We see in Exercise 3 how to copy plans from a test server to a production server.

Finally, we clone the database, and the lab ends with us seeing the successful results of our work.

Plan Freezing with SQL Server 2008 is a great Virtual Lab to see how to optimize query plans, move them from test server to production servers, and finally clone the plans amidst several SQL Servers if needed. I hate to admit it because most times I have to deal with T-SQL it is never much fun, but I enjoyed this lab a lot and would recommend that it is worth the time to get an overview of Plan Freezing whether it is your primary role to handle this job function or not.

Working with Pipelines

itprotipsadmin — Wed, 19 Mar 2008 05:01:00 GMT

By David Chernicoff

TechNet Virtual Lab: Working with Pipelines does a good job of pulling the covers off of some basic BizTalk functionality by walking the user through the process of configuring Biztalk and setting up a custom pipeline.

By providing the bulk of the project in predefined form, Microsoft enables the user to walk through the process of building and deploying the project easily within the time constraints of a 60 minute Virtual Lab. The user works with both Visual Studio and Biztalk Server administration in the course of the lab.

The information presented in TechNet Virtual Lab: Working with Pipelines demystifies the complexities often associated with using BizTalk server and implementing pipelines.

TIP: If the automated actions in Visual Studio fail, the manual has instructions for doing the tasks manually, which actually manages to give the user a better understanding of what is going on.

SQL Server 2008 Analysis Services: Delivering Analysis Services Capabilities through Microsoft Office

itprotipsadmin — Tue, 18 Mar 2008 05:01:00 GMT

By Ronald Barrett

Yet again another virtual lab title which had me thinking “That’s a mouthful”, but one must admit the idea of using Analysis Services in Office is in itself a great motivator for moving forward with this lab. Anytime you can move data from the SQL Server and into users’ hands via a product like Microsoft Excel, it’s bound to be a worthwhile thing to learn.

This Virtual Lab titled TechNet Virtual Lab: SQL Server 2008 Analysis Services- Delivering Analysis Services Capabilities through Microsoft Office will take about 30 to 45 minutes to complete (Microsoft says the lab is 45 min.), but if you already have a good understanding of pivot tables and Analysis Services you can move through it a bit quicker. This lab suggests you have familiarity with Excel functionality (in particular Excel 2007 and the new ribbon navigation bar). In addition, Microsoft suggests that you be familiar with Analysis Services concepts and terminology.

TIP: If you need some additional help with Analysis Services, there is a great article in the Windows IT Library for SQL 2005 that introduces both the changes in terminology from SQL 2000 and a good overview of terms you need to understand. Just follow the link above. It's worth the 10 minute read.

In this lab, we will go through three exercises:

1. Working with Analysis Services Data through Microsoft Excel 2007

2. Using OLAP Formulae in Excel

3. Adding a KPI to a SharePoint Web Page

Exercise 1

So let’s begin by looking at the demo of working with Analysis Services data through Excel. This exercise takes us through the process of connecting to Analysis Data, creating and browsing Pivot Tables and Pivot Charts, and adding KPI’s using an action in Pivot Tables and finally connecting a perspective.

As you go through the exercises, it becomes very apparent how easy it is to manipulate the data and drill down to see cross sections of information. This information is easily moved to a secondary worksheet and transformed into a Pivot Chart for a visual representation of the numbers, and just as easily, KPI’s can be added to track performance indicators.

Exercise 2

Next we look at Using OLAP formulae in Excel. Here we complete several tasks including, creating a connection to the Analysis Services Cube, creating a report by entering Formulae into workbook cells, sorting the data, and lastly, converting Pivot Tables to Formulas.

Once we have seen how to enter formulae into workbook cells, we look at how to convert Pivot Tables to formulas in Excel.

TIP: Do not ignore the request to save the Excel files in the Exercise 2. These files will be required for the last set of exercises.

Exercise 3

In our final exercise, we look at how to upload KPI data to a SharePoint Website. To do this we will need to perform the following tasks:

Upload an Office Data Connection file to a SharePoint site; Create a KPI List; Add a KPI to the KPI List; and for the final task, add a Web Part to a web page for display of the KPI List.

Once the data connection is established, the exercise walks us through setting up the KPI’s for the SharePoint Site.

Finally the lab ends with a view of the KPI in the SharePoint site.

This lab was a great way to either learn or refresh you on dealing with Pivot Tables, Formulas, and KPI’s within Excel. The Delivering Analysis Services Capabilities through Microsoft Office lab gives us a great look at how Analysis Services can be used to deliver reporting and analysis capabilities to end users using the very familiar Microsoft Excel spreadsheet software to empower users to create, edit and publish effective information for your organization.

SQL Server 2008: X-Event Infrastructure

itprotipsadmin — Thu, 13 Mar 2008 05:01:00 GMT

By Ronald Barrett

A new feature of SQL Server 2008 is the SQL Extended Events (a.k.a. the X-Event Infrastructure), while event reporting is not a new feature in SQL Server products. X-Event is an exciting new event infrastructure that allows for detailed traces of what is happening within the SQL Server. X-Event has support for sending events to the Windows, and integrating with system level events.

The Microsoft SQL Server TechNet Virtual Lab: X-Event Infrastructure takes you through four labs focusing on the following SQL operations areas:

1. Queryable Schema

2. Procedure & Kernel Analysis

3. Procedure Usage

4. Long Running Queries

The first demonstration shows how X-Event groups metadata into channels and keywords, grouping the results into packages and the objects in the packages.

The next demo takes you through the process of creating a log file that exports results to a .csv file to show how the SQL Server X-Event data is merged into the ETW (Event Tracing for Windows). The demo shows. The results of stored procedures for the SQL Server and Disk IO for the session.

TIP: Step J. calls for launching the xeetw.csv file; this link is not immediately seen when running the demo. To locate the file, go to the first pane ( the command line interface), and scroll to the right. You will see the xeetw.csv file. Double clicking will launch the Excel file.

The third demo shows the ability to get aggregate results from a query. It also shows how to cross-examine the database for results about bottlenecks and failures, rather than reviewing trace logs. The demo shows how the event sessions are created in the system tables of the database.

Finally we take a look at how we can create and alter statements to get precise results for the events that are considered. We also learn how to filter these results and extend data already collected, along with other available actions.

This lab was a challenge, but definitely a worthwhile one. It definitely helps to keep your skills sharp. I have worked some with T-SQL in the past, since most of my job has been database management; I have not done tremendous amounts of querying. However I have done tons of analyses of events and traces in order to track a problem and resolve it. The X-Event Infrastructure certainly is a great tool for querying and resolving issues in SQL Server 2008. This virtual lab certainly whets the appetite and leaves you wanting to dive deeper into the subject.

SQL Server 2008 Reporting Services Enhancements: Report Designer, Visualizations, and Tablix

itprotipsadmin — Wed, 12 Mar 2008 05:01:00 GMT

By Ronald Barrett

When I first saw the title of this virtual lab I thought it would be shorter had they called it “everything but the kitchen sink”. But seriously as daunting as the name sounds, and as difficult as the lab might be deemed based upon the name, the areas covered in the virtual lab all fall under the new enhancements for Reporting Services in SQL Server 2008.

The new enhancements have been designed to allow non-developers to create detailed and professional reports from within the available SQL data easily. The Report Designer is now a standalone application within SQL Server 2008. As the lab explains, the Report Designer can now be run separately from the Business Intelligence Development Studio. The new application can be used to create purchase orders, invoices, contracts, etc.

For this SQL Server TechNet Virtual Lab: Reporting Services Enhancements: Report Designer, Visualizations, and Tablix, users will go through the exercise of creating:

Templates

Tablix Tables

Charts

Once you open the Report Designer, you’ll notice the now very familiar Office Ribbon navigation tools.

As you go through the exercise of creating Tablix tables you will notice that Report Designer has many familiar tools for those who may have worked with products like Crystal Reports.

TIP: When editing the Value expression, you must remove the ( ) around the expression as well as =Sum. Failure to do so will make step o. of the exercise fail.

Next, we will look at creating charts in the Report Designer.

TIP: Step b in the chart task tells you to drag the chart item to the design surface. In the exercise, there was no visible chart item. However, going to the Insert tab and clicking on the chart icon produces the same results.

The lab shows you several very quick but effective techniques for creating reports and charts with very little data manipulation. In fact, most of the design aspect is simply drag and drop.

The lab continues with very detailed changes to charts and finishes by showing the results of your work within Internet Explorer using the Report Manager Intranet page.

Report Designer, Visualizations, and Tablix is a great lab for both users who need to create reports in SQL Server 2008 and Admins who want to understand what the users need to do to generate reports and how it is they can better support them through the process.

SQL Server 2008: Change Data Capture

itprotipsadmin — Tue, 11 Mar 2008 05:01:00 GMT

By Ronald Barrett

The dynamic nature of databases, and in particular data warehouses, calls for a method of updating, inserting and deleting activity in a SQL server table. But change data capture is not only about the physical change of the database, it is about the ability to report on and track these changes. Change Data Capture is part of a suite of tools used in SQL Server 2008 to accomplish that goal.

The Microsoft SQL Server TechNet Virtual Lab: Change Data Capture (CDC) takes the user through the process of simulating an Integration Services package. The lab manual explains the scenario of the CDC exercise and lets you know before you begin what each element is accomplishing during the lab.

The virtual lab begins by launching the Change Data Capture file via Windows explorer. The files resides in C:\SQL Server 2008 Demo Files\Intelligent Platform\Change Data Capture\CDCSample2.sln

TIP: Although this tool is used to track changes in SQL Server, this exercise takes place with Microsoft Visual Studio. It is important to make sure you completely read the scenario to understand what is happening in the exercise.

Once you double click the file, Microsoft Visual Studio opens up in the solutions explorer. You want to be sure to choose "SetupCDCSample.dtsx".

Once you choose the debug file, the system will being the debug process. It takes about 30- 40 seconds for the first Data Viewer to appear.

Finally you click the detach button and the process will run through some changes. When the next data viewer appears, you can see the changes made to the Customer ID 696.

Overall this was a quick and interesting lab. Although it took only 30 minutes (between reading the manual and running the tests twice), the Change Data Capture lab certainly gave a good illustration of what a live scenario could look like and effectively showed how the CDC process works. I particularly enjoyed working within Visual Studio on this lab, which I don’t get to do very often. Bottom-line, this is a great way to start learning about Change Data Capture.

SQL Server 2008: Declarative Management Framework (DMF)

itprotipsadmin — Fri, 29 Feb 2008 06:01:00 GMT

By Ronald Barrett

Day to day management of SQL Servers can be a time consuming task, especially in large data intensive environments. SQL Server 2008 has introduced a management tool to help alleviate some of the Administrative overhead associated with database management.

Before we move into the Virtual Lab let’s define what DMF is and then we will look at what the Virtual Lab will teach us. To begin with, Declarative Management Framework (DMF) takes the approach of using polices to manage a database rather than using tasks.

The Microsoft SQL Server TechNet Virtual Lab: Declarative Management Framework (DMF) takes the user thru three aspects of DMF. They are:

1. Policy Enforcement

2. Monitoring Changes to the System

3. Creating a Policy / Facets

Once the Virtual Lab is launched you will see a desktop with the SQL Server Management Studio (SSMS) icon on the desktop. This is where we will be working. The lab manual takes you through the three examples of using DMF.

Let’s first look at the Policy enforcement exercise:

The exercise takes you through the process of trying to create a table in the database. The first one fails, the second succeeds. Then you view the table naming policy to see why you achieved the results to each query.

In the next exercise we’ll see how to monitor system changes using DMF. To begin the exercise you check the Auto settings of the database.

Next you make changes and go back to check the results.

As the results quickly show, the policy has been violated on the database. When the policy is first changed, notification is given at the top level of the Database server by flagging the database instance , the database, etc. with a red “X”.

For the final exercise the lab explains how to set policies and use facets for databases.

The exercise finishes by showing you how to test to make sure the applied policy is working correctly.

This was an interesting lab. It definitely gives DBAs and IT Managers the power to implement controls to not only monitor but even prevent unwanted database changes easily. Declarative Management Framework (DMF) is a welcome database tool, and this virtual lab is worth the time to get to know how this worthwhile tool works.

SQL Server 2008: Database Mirroring

itprotipsadmin — Thu, 28 Feb 2008 06:01:00 GMT

By Ron Barrett

We have reached a point in business where recovery from failures needs to happen in seconds not minutes. When I was an IT Director for a Financial Services firm in NYC, partners would remind me daily that every time the databases for our CRM, Financial Applications or Time and Billing system where down that we would be losing tens of thousands of dollars an hour. I mention this because it has been my experience that the idea of highly available systems only now seems to be taking hold in the SMB market.

Many enterprises know and acknowledge the need for availability. Sadly even some of these networks run with little or no thought of what happens if the system simply stops working. The nature of today’s business makes it vital to ensure that you can not only continue business, but continue with little to no interruption to the end users.

Although SQL Server 2008 provides several methods of meeting this goal, Database Mirroring, provides a solution that is easily managed and responds fast to a failover, with zero loss of committed data.

The Microsoft SQL Server TechNet Virtual Lab: Database Mirroring takes us through the process in two steps:

1. Establishing the Mirror

2. Simulating a Failover

Unlike some labcasts this lab does not give an overview of the tools you will work with, rather it allows the user to jump right in and begin working. By default it opens the “Database Engine” for “SQL08DEMO”. However, it is important to be sure you’re working on the correct Server Type and Server Name before clicking Connect.

Once the SQL Server Management Studio is launched, users are faced with three panes: the Object Explorer, Object Explorer Details pane and the Solution Explorer. Resizing the Object Explorer allows for better views of the scripts to be run in the Object Explorer Details pane.

The lab next takes you through the process of opening and executing the 5 preset scripts. These scripts will setup the Primary, Secondary, and a third “Witness” server to act as a mediator in case of failure to ensure a quick failover response.

TIP: Script number 4 and 5 will show an error after they are executed. This is because the Adventureworks database has not been refreshed yet.

Next you’ll run through setting up the mirror server instance and adding it to the object explorer.

TIP: The lab tells you to go to the registered servers view to find SQL08DEMO\MIRROR. You will first need to add the registered server before it appears in the registered servers.

Finally you need to check that the Secondary database has been created in the mirror database before moving on to the SQL Server Failover Test.

In the failover test the lab simulates both the failure and failback. The performance monitor shows the simulated load on the systems.

This lab, Database Mirroring, was a quick and fun way to get a grasp on how to prepare your database for high availability. As I said before SQL Server 2008 has several high availability options but as you will learn in this lab, Database Mirroring can be set if needed by a newcomer to SQL Server. There you go; no more excuses for database downtime.

Going beyond relational data with SQL Server 2008

itprotipsadmin — Thu, 10 Jan 2008 06:01:00 GMT

By Haidong Ji

SQL Server 2008 will be released in the first half of 2008, about six months from now, so it's time for me to get a good feel of the new features and enhancements in this exciting new release. Fortunately, Microsoft TechNet provides a series of webcasts to get us started, beginning with Going Beyond Relational Data with Microsoft SQL Server 2008.

One of the exciting features in SQL Server 2008 is its enhancement to manage non-relational data such as documents, image files, and other media data. I can see more usage of this feature as more and more in our lives becomes digitized, and they need to be stored somewhere!

The presenter really did a good job describing how SQL Server 2008 fits together with other products such as Office Servers, BizTalk, and Visual Studio. You have to watch it yourself to find out more details.

I like how data of all different format and size can be managed using SQL Server 2008, even including geo-spatial data, which can be really useful, especially with today’s ubiquitous presence of mobile technology.

I also like the flexibility of being able to store documents and multimedia data in different servers. Once again, you’ve got to watch the webcast yourself to find out some details.

The presenter then offered further discussion on FILESTREAM and remote BLOB storage.

TIP: One really neat thing is that you can choose to dedicate all non-relational data on either remote storage or in file system.

The demo on FILESTREAM is really great. Some simple code really demonstrated the power of managing files in the file system.

I really like the new spatial data support. This new feature has the potential to drastically increase regular user’s experience with your sites or products.

It is really nice to learn SQL Server 2008’s feature set for non-relational data. The Going Beyond Relational Data with Microsoft SQL Server 2008 webcast is a really quick way to get up to speed in this great new development.

Performance Monitoring and Troubleshooting with SQL Server 2008

itprotipsadmin — Wed, 09 Jan 2008 06:01:00 GMT

By Haidong Ji

The New Performance Monitoring and Troubleshooting Using Management Studio webcast gives you a great walkthrough of the new features of SQL Server 2008. SQL Server 2005 introduced Dynamic Management Views (DMVs), which greatly enhanced DBA’s understanding of the internal working of the database engine. Unfortunately, this data will be lost every time SQL Server 2005 instance is bounced. This does not change in SQL Server 2008. However, SQL Server 2008 provides the capabilities to store this data in a database. It also provides various reports to analyze this data for better monitoring and troubleshooting. This webcast really did a good job of demonstrating and educating to end users this great feature.

The webcast starts off with a clear explanation of the overall performance data collection architecture. I always like to know how different pieces fit together, so the presenter really gives us a good overview here.

Going one step further, the presenter talks about various databases and processes involved in gathering performance data.

The demo is terrific. SQL Server 2005 has great server status reports. SQL Server 2008 really kicks it up a notch. I like how you can collapse and expand, or drill down to details when you like. The demo gives a pretty good understanding of how the reports are related to each other.

Pay attention to the demo where Query Plan details were displayed. I know from experience this will be very helpful for performance tuning.

TIP: Use Query Plan Details to get more information on a specific query type, such as times executed, plan changes, etc.

I highly recommend the New Performance Monitoring and Troubleshooting Using Management Studio webcast to anybody who is interested in performance tuning and monitoring aspects of SQL Server 2008.