The Art of Being a Successful DBA (Part 1)

Last Friday, I delivered a talk covering 5 tips and tricks on monitoring SQL Server 2005 and SQL Server 2008. The following are the details of the talk:

 

Background:

Monitoring is an important aspect of database administration because SQL Server provides services in a dynamic environment. The data in the application changes, the type of access that users require changes, and the way that users connect changes. SQL Server automatically manages system-level resources such as memory and disk space, but monitoring lets administrators identify performance trends to determine if changes are necessary.

 

Objectives:

In this talk, the attendees are introduced to different strategies to monitor SQL Server:

• to use the System Monitor to obtain performance data about your computer and the instances of SQL Server running on your computer
• to use the SQL Server Profiler to trace server and database activity
• to use event notifications to capture and monitor significant events for a SQL Server instance
• to implementing DDL triggers that enable you to audit changes to the structure of database objects
• to examine the current activity in a SQL Server instance.

Description:

In this talk, the attendees are introduced to several tips and tricks to use the monitoring tools in SQL Server to monitor SQL Server. SQL Server provides you with a number of tools you can use for tracking how well SQL Server is performing, including the SQL Server event log, which can prove to be an invaluable tool when determining the reason for a failure as part of a trouble-shooting exercise. However, this session concentrates on techniques you can use to trace and monitor live activity occurring in SQL Server databases.

 

Slides URL: http://cid-d1df34a904545dc5.skydrive.live.com/self.aspx/Public/Monitoring%20SQL%20Server%202008%20-%20Slides.zip

Demonstrations URL: http://cid-d1df34a904545dc5.skydrive.live.com/self.aspx/Public/Monitoring%20SQL%20Server%202008%20-%20Demonstrations.zip

DTSRun command line decryption utility

Recently, I helped one of my friends to migrate DTS packages from SQL Server 2000 to SQL Server 2005 (as SSIS packages). Most of the DTS packages are triggered by a SQL Server job in which the DTS package name and its corresponding parameters are encrypted. I tried to do a little research on the Internet and found this wonderful command line tool (DTSRunDec.c). Oppps is a C source file. Is ok anyway. I have managed to compile the source file. I’ll be including the download url later in this blog entry. 

 

Scenario

Sometimes people ask how to decrypt some sort of data stored in Microsoft SQL Server 2000. One case is decryption of DTSRun command line parameters that can be found in sysjobsteps table:

 

SELECT command
  FROM msdb.dbo.sysjobsteps

 

DTSRun /~Z0xF2E216E36948A6C83AC888EA05C93E2CACB07C0B59649CE75654E3FDAC6CCE4ED5F72528FC6458D14F566A164C9F31C4D18CA65E9E244994C4DF0ABE475C42D18B31DC84FB51E22B6DF34778D5159106B3D727B6CD576891E34C7D

 

Value after /~Z can be decrypted using DTSRunDec.exe.

 

Download URL: http://cid-d1df34a904545dc5.skydrive.live.com/self.aspx/Public/DTSRunDec.zip

The Art of Being a Successful DBA (Part 1)

This coming Friday I’ll be delivering a talk specilizing on the tips and tricks to monitor SQL Server 2005 and SQL Server 2008. I’ll be demonstrating the tools that you can used in SQL Server 2008 to monitor both SQL Server 2005 and SQL Server 2008. Do drop by and support me. 🙂

 

Available Today: Service Pack 1 for Microsoft SQL Server 2008

What Is Microsoft SQL Server 2008 SP1?

Service Pack 1 (SP1) for SQL Server 2008 is available today for customers. The Service pack is available via download here and is primarily a roll-up of previous cumulative updates, quick fix engineering updates and minor fixes made in response to requests reported through the SQL Server community.  While there are no new features in this service pack, customers running SQL Server 2008 should download and install SP1 to take advantage of the fixes which increase supportability and stability of SQL Server 2008.

Customers have no reason to wait to upgrade to SQL Server 2008 and many are already taking advantage of SQL Server 2008 as a smart IT investment. In fact, there have been over 3 million downloads of SQL Server 2008 since the RTM in August. With this Service pack, Microsoft is introducing 80% fewer changes to customer configurations compared to previous SQL Server Service Pack releases. This remarkable decrease is a testament to a revised product development process and updated servicing strategy that is focused on ease of deployment while keeping customer environments stable.

What Are The Key Improvements in SQL Server 2008 SP1?

With this service pack, Microsoft has made investments to ease deployment and make management of service packs more efficient. Key improvements in SP1 include Slipstream, Service Pack Uninstall and Report Builder 2.0 click-once deployment.

·         Slipstream allows administrators to install SQL Server 2008 and Service Pack 1 in a single instance. This decreases the total time for an installation, including a fewer number of reboots thereby increasing productivity and deployment availability.

·         Service Pack Uninstall allows administrators to uninstall the service pack separately from the database release.  This feature also improves DBA productivity, reduces the cost of deployment and improves overall supportability

·         Report Builder 2.0 Click Once improves the existing SQL Server end-user report authoring application by easing deployment to business users

 

Download URL: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19