Allen White

[Ohio] A Day in the Life of the DBA 2008

AllenMWhite — Mon, 06 Oct 2008 12:37:00 GMT

We are pleased to invite you to the October 9th 2008 meeting of the Ohio North SQL Server Users Group (ONSSUG). The meetings are held on the 2nd Thursday of every month at 5:45 in the Cleveland Microsoft office. The address of the office is below:

Microsoft Corporation
6050 Oak Tree Blvd. Suite 300
Cleveland, OH 44131

Agenda:

5:00 - 5:45 PM - Early Arrival/Discussions

Deepak Puri offered to present information that he found interesting from the 2nd Annual Microsoft BI Conference. Deepak has presented to the group following SQL Pass and other conferences and does an outstanding job of synthesizing the most important highlights from the conference.

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM - A Day in the Life of the DBA 2008 by Buck Woody, Microsoft

This presentation details a typical DBA's day, demonstrating the new improvements of the Management Platform made for Microsoft SQL Server 2008. These improvements include more efficient system management, multi-server management, performance tracking, policy creation, state detection and enforcement, and using both graphical and command-line tools. We will cover everything from the new Management Interfaces to Policy Based Management, including a practical demonstration of PowerShell integration in SQL Server administration tasks.

About Buck

As a program manager for Microsoft SQL Server, Buck Woody provides leadership on feature sets within SQL Server, as well as leads team of developers and testers. Buck is also responsible for the documentation, user-experience, and the legal and marketing professionals for the feature sets. Find out more at http://buckwoody.com/BResume.html.

7:00 - 7:30 PM - Wrap up / Ongoing discussions

Registration:

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen

SQL 2008 Data Compression - Linchi Shea

AllenMWhite — Fri, 26 Sep 2008 14:07:00 GMT

Last night at the NYC SQL Server Users Group meeting Linchi Shea presented a session on Data Compression (not Backup Compression) in SQL Server 2008.

By understanding how the compression algorithm works, and tuning your clustered indexes based on that understanding, you can achieve some pretty incredible space savings on disk, and performance benefits due to the increased amount of data loaded into cache. His analysis is available from his blog post SQL Server 2008 Data Compression: Impact of Data Distribution.

His explanation was clear and I learned a lot from his session. (I am in New York to do some performance testing at the Microsoft Technical Center, where the meetings are held every Fourth Thursday of the month.)

If you happen to be in New York and can spare the time, I highly recommend this group. It was well worth my time.

Allen

[OT] Here today, Gone to Maui

AllenMWhite — Wed, 10 Sep 2008 12:40:00 GMT

It's been three years since I've had a real vacation, so we're leaving tomorrow to head to the island of Maui, where I'll be running the Maui Marathon on Sunday (Sept. 14).

Aloha!

Allen

Introduction to the New Data Types in SQL 2008

AllenMWhite — Tue, 09 Sep 2008 17:57:00 GMT

We are pleased to invite you to the September 11th 2008 meeting of the Ohio North SQL Server Users Group (ONSSUG). The meetings are held on the 2nd Thursday of every month at 5:45 in the Cleveland Microsoft office. The address of the office is below:

Microsoft Corporation
6050 Oak Tree Blvd. Suite 300
Cleveland, OH 44131

Agenda:

5:00 - 5:45 PM - Early Arrival/Discussions

Due to technical difficulties we did not get to see the Demonstrations of Excel 2007 BI. These technical difficulties will be remedied and Deepak will show the Excel Demos from the August Meeting.

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM - Introduction to the New Data Types in SQL 2008 by Sarah Dutkiewicz

SQL 2008 introduces 7 more data types. Come see Sarah Dutkiewicz as she walks us through the new datetime, hierarchical, and geospatial data types and see how they can be used.

About Sarah

Sarah Dutkiewicz has been developing database-driven applications over the past few years. When she isn't working as a developer for a Cleveland-based company, she's looking into new technologies and staying involved in the .NET community. Her blog is at http://www.codinggeekette.com.

7:00 - 7:30 PM - Wrap up / Ongoing discussions

Registration:

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen

The Business Intelligence (BI) features of Excel 2007

AllenMWhite — Wed, 13 Aug 2008 12:33:00 GMT

We are pleased to invite you to the August 14th 2008 meeting of the Ohio North SQL Server Users Group (ONSSUG). The meetings are held on the 2nd Thursday of every month at 5:45 in the Cleveland Microsoft office. The address of the office is below:

Microsoft Corporation
6050 Oak Tree Blvd. Suite 300
Cleveland, OH 44131

Agenda:

5:00 - 5:45 PM - Early Arrival/Discussions

Join Bob Coppedge As they discuss the current state of Microsoft certifications for database professionals

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM - The Business Intelligence (BI) features of Excel 2007 by Deepak Puri

The Excel pivot table has long been one of the most popular BI tools for data analysts, and the most frequently used client tool for SQL Server OLAP cubes. Now, with Excel 2007, its integration with Analysis Services has been revamped to comprehensively support SQL Server 2005-specific features. We will explore how this integration helps to improve the user experience.

Many other enhanced Excel 2007 features, such as charting, filtering and formatting, bring significant benefits to BI usage scenarios as well. In addition, the new Excel Services feature of SharePoint 2007 allows spreadsheets to be published to a SharePoint server, and be viewed by users via web browsers.

We will examine how this new functionality makes Excel 2007 a powerful and flexible BI client tool.

About Deepak

Deepak is a Business Intelligence Consultant at a large insurance company, and has been working with SQL Server Analysis Services since 2000. His interest in OLAP technology arose from working with large volumes of call center telecom data. In addition, Deepak also works with performance data and Key Performance Indicators (KPI's) for new business processes. Recent project work includes SSAS cube design, and dashboard and reporting front-end design for cube data, using Reporting Services, SharePoint and third-party OLAP-aware Web Parts Deepak is also an SQL Server Analysis Services MVP.

7:00 - 7:30 PM - Wrap up / Ongoing discussions

Registration:

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen

Oh, What a Tangled Web

AllenMWhite — Thu, 31 Jul 2008 13:17:00 GMT

At Tech Ed 2008 I made a mental note when Paul Randal mentioned that you can't use Database Mirroring when you've got the Filestream feature turned on. I like Database Mirroring and so that was useful information.

In preparing a bunch of demos for SQL Server 2008, I'm using the AdventureWorks2008 database currently available on the Codeplex site. Installing this database requires the Filestream feature, so I turned it on.

Some of the demos I'm working on are existing demos written for the AdventureWorks database on SQL Server 2005, and I've found many of them don't work. It turns out that the structure of the database has been modified, eliminating some tables commonly used in demos (Person.Contact, for example), and changing the column names of primary keys as well as other columns.

I also discovered that you can't set the READ_COMMITTED_SNAPSHOT and the ALLOW_SNAPSHOT_ISOLATION options on if the Filestream feature is enabled, so to demo those options I'll have to load the old AdventureWorks database as well as the new AdventureWorks2008 database.

Fun!

Allen

Partition Wizard Makes it Simple

AllenMWhite — Mon, 28 Jul 2008 12:44:00 GMT

The partitioned table was one of the great new features of SQL Server 2005. We could now manage data based on some range data, usually a date value, and keep the different ranges in separate physical files, and use rolling range scripts to move old data out of the table and create new partitions for new data and incorporate those new ranges into the table, and didn't have to change the application to keep it working properly.

The feature is cool, setting it up turned out to be a pretty large task, and when setting up a partitioned table over two years history with millions of rows of data, it took hours to plan and set up.

SQL Server 2008 introduces a wizard that makes the creation of the partition function and partition wizard fairly easy to understand and very simple to implement.

By right-clicking on the target table, a menu option called Storage appears, and you can then select Create Partition. Select the column to be used as the partitioning column, specify names for your partition function and scheme, then the Map Partition dialog is displayed. You can specify RANGE RIGHT or RANGE LEFT using the right boundary or left boundary radio buttons at the top of the dialog. (Boundary makes much more sense than RANGE, doesn't it?) Also, when the left boundary button is selected, the header of the second column in the filegroups grid indicates "<=Boundary", and when the right boundary button is selected, the header indicates "<Boundary", so it helps you understand the decisions you're making.

Now, let's say you've got two years of data you want to partition, with a partition for each month. Just click the Set Boundaries button, enter the start and end dates and the range you want to partition on (Monthly, Quarterly, Yearly, etc.) and the wizard creates those ranges for you automatically. Now, all you have to do is assign each of the date ranges calculated to the partitions you'll have to have already set up in the database. (You do have to set up the filegroups and files before starting the wizard - it can't do that for you.) You have to also include a filegroup to catch the data falling outside your defined range, so if you've set up 24 ranges for two years of data, you'll need 25 filegroups defined.

The partitioned table is a great feature you should be using to manage data more effectively. With SQL Server 2008 it'll be even easier to implement.

Have fun!

Allen

Tools for the Reluctant DBA

AllenMWhite — Mon, 07 Jul 2008 14:00:00 GMT

We all know them, they're usually developers or network admins who show some abilities when database problems arise, so they're "elected" to the role of DBA, whether they want it or not. They often don't have the necessary training or insight into the issues inherent to managing enterprise database systems, but they've got the job.

What tools do they need? Of course, we all know that backup is critical, as is the ability to restore those backups. What else?

In my presentation at Tech Ed 2008 on Automating DBA Tasks with PowerShell I offered a set of scripts to do the basic administrative tasks.

What do you think? What tools does the Reluctant DBA need?

Allen

SQL Saturday 6 - Cleveland August 9

AllenMWhite — Fri, 27 Jun 2008 11:42:00 GMT

Wow, it's been a crazy month! Two weeks of Tech Ed in Orlando, where I spent time helping people with questions on SQL Server in the Database Platform area and presented two sessions on SMO and PowerShell (one with my friend Peter Ward from Australia). Then, my last week as a DBA at Advanstar, followed by my first week as a Trainer for Scalability Experts! Wow.

So I wanted to get the word out about our upcoming SQL Saturday event in Berea, Ohio, on the campus of Baldwin-Wallace College. If you're going to be in the area, or if this will provide motivation to come to Cleveland, it should be a great event. I'm hoping to have about 30 sessions on SQL Server, both how to do things now, and how you'll be able to do things much easier with SQL Server 2008.

The Call for Abstracts is open, so if you're interested in presenting, please submit your session idea here.

If speaking isn't your strong suit but you'd like to attend we'll be happy to have you. The link to register is here.

The morning sessions will each be an hour long, in the Math and Computer Science building, and the afternoon sessions will be 75 minutes each, and be held in larger rooms in Strosacker Union. We'll have a box lunch on the picnic tables behind the Union (weather permitting) and a nice wrapup after the sessions with some great giveaways, including a full MSDN Team Suite subscription!

I look forward to seeing you at SQL Saturday, and perhaps meeting some of you in my teaching assignments with Scalability Experts!

Allen

Use a SQL Agent Proxy for Special Tasks

AllenMWhite — Tue, 06 May 2008 11:34:00 GMT

When SQL Server Agent runs its tasks it normally does so using the context of the service account you assigned to the Agent service. This may or may not be the best context for your application, so you can set up a Proxy. This allows you to run the task under the context of the account you set up in the proxy, and it's pretty easy to do.

First, set up a Credential. This defines the ADS login credentials the proxy will use. You can do this under the Security tree in Object Explorer, or you can set it up in Transact-SQL like this:

CREATE CREDENTIAL [cred_MyCredential] WITH IDENTITY = N'MyDomain\MyLogin', SECRET = N'S0meComp1exP@ssw0rd'
GO

Once the credential exists then you define the proxy. Expand the tree under SQL Server Agent/Proxies, select the job task type you plan to use the proxy for, then right-click and select New Proxy, or use this Transact-SQL:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'proxy_MyProxy',@credential_name=N'cred_MyCredential',
		@enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'proxy_MyProxy', @subsystem_id=3
GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'proxy_MyProxy', @msdb_role=N'SQLAgentUserRole'
GO

In the sp_grant_proxy_to_subsystem stored procedure, @subsystem_id=3 indicates this proxy will be used to run Operating System commands.

Once the proxy exists it can be selected in setting up a job task by specifying the new proxy in the Run As dropdown in the task dialog. If setting up the job through Transact-SQL you specify the proxy in the sp_add_jobstep stored procedure with the @proxy_name parameter:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 01',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'CmdExec',
		@command=N'"d:\ApplicationDirectory\MyJobStep.bat"',
		@flags=0,
		@proxy_name=N'proxy_MyProxy'

This may seem like a lot, but really it's not, and it ensures that the automation you're implementing is being done using the right context.

Allen

Dynamic Package Loading with SSIS

AllenMWhite — Sun, 04 May 2008 23:33:00 GMT

Here's the agenda for the May 8th 2008 meeting of the Ohio North SQL Server Users Group. The meetings are held on the 2nd Thursday of every month at 5:15 in the Cleveland Microsoft office. The address of the office is below:

Microsoft Corporation
6050 Oak Tree Blvd. Suite 300
Cleveland, OH 44131

Agenda:

5:15 - 5:45 PM - Excel 2007 Data Mining Add-In by Bruce Szabo

The Excel Data Mining Add-In allows one to Use Excel 2007 and Analysis Server 2005 to do some great data mining. This is a quick overview of getting this functionality up and running on a computer.

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM - Dynamic Package Loading with SSIS Presented by Dave Gabele

Dave will discuss leveraging the properties of SSIS to create a scheduler and dynamic package loader. The concepts Dave presents provide the ground work for building enterprise ETL processes with (SSIS). The focus will be on the Control Flow aspects of SSIS and should allow others to learn from Dave’s experiences. Having used DTS extensively Dave will delve into the gotchas he found in making his transition to SSIS.

7:00 - 7:30 PM - Wrap up / Ongoing discussions

Registration:

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

[OT] Summa Cum Laude!

AllenMWhite — Thu, 01 May 2008 13:37:00 GMT

Last night I picked up my tickets for Commencement Exercises on May 11, and found out that I'll be graduating Summa Cum Laude from Baldwin-Wallace College with a Bachelors of Science Degree in Computer Information Systems.

Needless to say, I'm really pleased.

Allen

A Changed Approach to Service Packs

AllenMWhite — Tue, 29 Apr 2008 11:29:00 GMT

Matthias Berndt, Principal Group Program Manager at Microsoft, has just blogged about the new approach for Service Packs and Cumulative Updates. He shared this with us at the MVP Summit and it's nice to have it public now.

A Changed Approach to Service Packs

Thanks, Matthias

Allen

Create Database from PowerShell

AllenMWhite — Mon, 28 Apr 2008 14:13:00 GMT

Last week I presented a session on Using SMO to Manage SQL Server at SQL Connections in Orlando, Florida. This was the third major conference I've presented this session, and each time I do this session I change more demos from VB.Net to PowerShell. This time it was the demo to create a new database.

The big thing about my demos is that I want to reflect what I actually do in my job as a DBA, and I now use this script whenever I want to create a new database. The demo files I included with my presentation contained the VB.Net code I used to use so I wanted to make the code available here.

The first thing we need to do is load the SMO object library and connect to the server.

#createdb.ps1
#Creates a new database using our specifications
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer/MyInstance'

The next thing we'll do is set a string variable to the name of the database. This value can also be supplied as an argument to the script if you want. Then we'll instantiate the database object and add filegroups for PRIMARY (required for SQL Server), and another filegroup I call AppFG for the application data. I've found that I improve performance and recoverability by putting only the database metadata in the PRIMARY filegroup, setting its size to 5MB without expansion, then setting the AppFG (application filegroup) to be the default filegroup.

$dbname = 'SMO_DB'

# Instantiate the database object and add the filegroups
$db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, $dbname)
$sysfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'PRIMARY')
$db.FileGroups.Add($sysfg)
$appfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'AppFG')
$db.FileGroups.Add($appfg)

Once the filegroups have been created, we can create the files for the database. First we create the file for the database metadata. I've set the size to be 5MB with no growth. To create the database the PRIMARY filegroup has to be set to be the default, so we'll set that here as well.

# Create the file for the system tables
$syslogname = $dbname + '_SysData'
$dbdsysfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($sysfg, $syslogname)
$sysfg.Files.Add($dbdsysfile)
$dbdsysfile.FileName = $s.Information.MasterDBPath + '\' + $syslogname + '.mdf'
$dbdsysfile.Size = [double](5.0 * 1024.0)
$dbdsysfile.GrowthType = 'None'
$dbdsysfile.IsPrimaryFile = 'True'

Next we'll create the file to hold the application tables. Normally 25MB works for my databases, so I've set that in the Size parameter, and I use a growth parameter of 25%, because 10% is too small an increment when growth is required, in my opinion. I've also set a maximum size for this file of 100MB. I have to watch this to make sure we don't run out of space, but this is rarely a problem in my environment, and this max helps prevent me from running out of physical disk. (Note that sizes are specified in KB units, so I "do the math" in the script so it's easier to read.)

# Create the file for the Application tables
$applogname = $dbname + '_AppData'
$dbdappfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($appfg, $applogname)
$appfg.Files.Add($dbdappfile)
$dbdappfile.FileName = $s.Information.MasterDBPath + '\' + $applogname + '.ndf'
$dbdappfile.Size = [double](25.0 * 1024.0)
$dbdappfile.GrowthType = 'Percent'
$dbdappfile.Growth = 25.0
$dbdappfile.MaxSize = [double](100.0 * 1024.0)

Now I can create the file for the transaction log. I set this to an initial size of 10MB with 25% growth.

# Create the file for the log
$loglogname = $dbname + '_Log'
$dblfile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($db, $loglogname)
$db.LogFiles.Add($dblfile)
$dblfile.FileName = $s.Information.MasterDBLogPath + '\' + $loglogname + '.ldf'
$dblfile.Size = [double](10.0 * 1024.0)
$dblfile.GrowthType = 'Percent'
$dblfile.Growth = 25.0

We can create the database now, and once it's been created we can grab the AppFG filegroup, set it's default property to True, alter the filegroup and alter the database. Now it's ready for loading the tables and other objects necessary for the application to work properly.

# Create the database
$db.Create()

# Set the default filegroup to AppFG
$appfg = $db.FileGroups['AppFG']
$appfg.IsDefault = $true
$appfg.Alter()
$db.Alter()

This script allows me to quickly create a new application database without the tedium of clicking through the GUI dialogs, but I can run this script against any of my servers without changing it to reference the specific database file locations, because I pull that from the server's Information collection, so it's cleaner than using Transact-SQL scripts as well. (Note, in this case I'm using the MasterDBPath and MasterDBLogPath properties from the Information collection.)

Allen

Database Mirroring - Lessons from the Field

AllenMWhite — Tue, 08 Apr 2008 10:41:00 GMT

Thursday's meeting of the Ohio North SQL Server Users Group will be a good one. Here's the agenda:

5:00 - 5:45 PM - Early Arrival/Discussions/SQL 2008 Launch Update

We will discuss the status of SQL 2008, including the recent launch event, the latest timetable for release, and touch on some of the more interesting features. We'll also provide online resources to get you started with SQL 2008.

5:45 - 6:00 PM - Dinner

6:00 - 7:00 PM -"Database Mirroring - Lessons from the Field," Presented by Craig Purnell & Michele Adams, Baker & Hostetler

Baker Hostetler uses the database mirroring in SQL Server 2005 to maintain data protection and high availability. They will present their real world techniques, and also demonstrate how to set up mirroring between 2 servers.

7:00 - 7:30 PM - Wrap up / Ongoing discussions

Registration:

To register, go to www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen