End to End training is offering a free SQL Stumpers book (a $24 value) for those who receive their schedule monthly. They even pay shipping. It's full of cool SQL Server questions and answers that you would see on exams or interview questions. You of course have to provide your address in order for them to mail you a book.
-- Brian Knight
After a long period of writing and banging my head into the keyboard, my newest book is on Amazon. Professional SQL Serever 2005 Administration was written by some a fantastic team from Microsoft and the community. It teaches you how to cluster, upgrade, administrate SSIS, mirror and much more. You can get it on Amazon now for the geek Christmas present to yourself :)
Service pack 2 released today to the public in CTP format and can be downloaded here: http://www.microsoft.com/sql/ctp.mspx.
Along with bug fixes, there are tons of new features. What impressed me most was I was able to install the SP without a reboot!
- Support for the upcoming Windows Vista.
- Data Mining Add-Ins for Office 2007, which enables data mining functionality from SSAS to be used directly within Excel 2007 and Visio 2007.
- SSRS integration with MOSS 2007, which allows integration with the Report Center in SharePoint providing seamless consumption and management of SSRS reports within SharePoint.
- SSAS improvements for Excel 2007 and Excel Services relating to performance and functionality.
Heterogeneous Environments (Interoperability):
- Oracle Support in Report Builder. Users will now be able to use Report Builder on top of Oracle data sources.
- Hyperion support with SSRS. Organizations will now be able to use SSRS to build reports on top of Hyperion Essbase cubes.
- Data compression (varDecimal), which is important for data warehouse scenarios and is specifically important for SAP BW scenarios. This requires less disk storage of decimal data which increases overall performance.
- Manageability enhancements. Based on customer feedback, SQL Server provides enhanced management capabilities for DBAs such as improvements in database maintenance plans, enhanced management reports and a new copy database wizard.
This weekend, I sent the final introduction out to the publisher on my newest book, Professional SQL Server 2005 Administration. It was a stressful but wonderful experience this time writing again for Wrox. This book is a pretty deep administration book. We had three MVPs, two very experienced authors and four Microsoft employees writing this book and I think you'll be happy with the coverage. You can order the book today on Amazon and it should be out within the next few months.
The first ten chapters of the book are about administering the various areas of SQL Server, including the developer and business intelligence features. Chapter 1 briefly covers the architecture of SQL Server and the changing role of the DBA. Chapters 2 and 3 dives into best practices on installing and upgrading to SQL Server 2005. Managing your SQL Server database instance is talked about in Chapter 4. This chapter goes over also some of the hidden tools you may not even know you have.
Once you know how to manage your SQL Server, you can learn in Chapter 5 how to automate many of the redundant monitoring and maintenance tasks. This chapter also discusses best practices on configuring SQL Server Agent. Chapters 6 and 7 covers how to properly administer and automate many tasks inside of the Microsoft business intelligence products like Integration Services and Analysis Services. Developers will find that Chapter 8 is very useful, as it covers how to administer the development features like SQL CLR. Chapter 9 talks about how to secure your SQL Server from many common threats and how to create logins and users. The last chapter in this part of the book (Chapter 10) covers how to create a SQL Server project and do proper change management in promoting your scripts through the various environments.
Chapters 11 through 15 make up the performance tuning part of the book. Chapter 11 discusses how to choose the right hardware configuration for your SQL Server to perform optimally. After the hardware and operating system is configured, Chapter 12 shows you how to optimize your SQL Server instance the best performance. Chapter 13 shows you how to monitor your SQL Server instance for problematic issues like blocking and locking. Chapters 14 and 15 discuss how to optimize the T-SQL that access your tables and then how to index your tables appropriately.
Chapters 16 through 20 consist of the high availability chapters of the book. Chapter 16 covers how to use the various forms of replication while database mirroring is covered in Chapter 17. Classic issues and best practices with backing up and recovering your database is discussed in Chapter 18. Chapter 19 dives deeply in what the role of log shipping is in your high availability strategy and Chapter 20 presents a step-by-step guide to clustering your SQL Server.
-- Brian Knight
I thought this might be useful for anyone who's looking at learning how to cluster. They're a series of mini-webcasts on how to create a SQL Server cluster from the ground-up (architecture, to windows, to SQL Server 2005).
Description: Before you start learning how to cluster, this video will show you the basics on how clustering in Windows 2003 and SQL Server 2005 works. Brian shows the basic architecture on clustering as well as the checklist that you would want to follow before starting to cluster.
Description: In this presentation, you'll learn step-by-step how to cluster Windows 2003 R2. Brian shows you how to configure and cluster a two node cluster, preparing it for any future services like SQL Server or Exchange.
Description: With Windows 2003 now clustered, you're ready to begin to clustering SQL Server 2005. In this presentation, you'll see how to cluster SQL Server 2005 and some best practices in how to configure the SQL Server cluster after the fact.
-- Brian Knight
SQL Express allows you to distribute a free edition of SQL Server with your applications. There are an enormous amount of 3rd party applications that use it and MSDE (it's SQL Server 2000 equivalent) for their data repository such as Sharepoint (by default). Generally speaking, the user may not even know they have the software on their workstation since it doesn't install anything on their Programs menu. The main weakness in SQL Express is that it doesn't have a scheduling tool. In this set of scripts, you'll receive a canned solution for automating backups of SQL Express. The same logic can be applied to MSDE by changing SQLCMD to OSQL in the batch file. No other syntax used is SQL Server 2005 specific.
The script contains three files:
- BackupExpress.sql - Does the bulk of the work, backing up every database on the instance other than tempdb.
- BackupExpress.cmd - The batch file that executes the script. Must pass in the instance name.
- ScheduleBackups.cmd - Schedules the job by using AT. Must pass in the instance name to schedule like ScheduleBackups.cmd .\SQLExpress.
To do this, make sure you have the Task Scheduler service started. Uncompress all three of the files into the root of your C drive and run the schedule file from the command prompt.
I tried to keep the solution simple since most people that have SQL Express on their workstation may not be technical. So with that said, there is very little configuration or options. The backup solution keeps 7 days of history and constantly overwrites the previous week's backup. So, there's lots of room for improvement if you're feeling adventurous. Please send me any updates and I'll post them for all, giving you credit. The main thing missing from this solution is the index rebuilds, which I'll probably add in the next release.
Download the package here: http://www.whiteknighttechnology.com/downloads/BackupExpress.zip. Enjoy!
Watch the deployment video on JumpstartTV here: http://www.jumpstarttv.com/Media.aspx?vid=30.
-- Brian Knight
Really cool tool that I'll have to start blogging about! Here's the announcement I just received from Microsoft. I added some screen shots below.
Visual Studio Team Edition for Database Professionals delivers a market-shifting database development product designed to manage database change, improve software quality through database testing and bring the benefits of Visual Studio Team System and life cycle development to the database professional. It delivers on Microsoft’s commitment to provide tools that reduce communication barriers and complexity across software development teams and fulfils increasing demand in the market for more advanced database change management tools. Database professionals such as database architects, database developers and database administrators, can now employ integrated change management functionality to streamline changes to their databases, ensure quality, and speed deployment.
Visual Studio Team Edition for Database Professionals includes a number of great new features:
· A new Visual Studio Database Project allows you to import your database schema and place it under source control. When the time comes to deploy schema changes the new project system allows you to quickly build update scripts or packages and then provides a mechanism to deploy them to the database of our choice.
· Rename Refactoring allows you to easily rename any object in your database and be assured that all references to that object will be renamed to correspond to the change
· A New T-SQL Editor allows you to be more productive when writing T-SQL code from within Visual Studio including support for parallel execution of queries and viewing of execution plans.
· SchemaCompare allows you to quickly compare the schema of two databases (or your source controlled project and a database) and script updates to bring the database schemas into sync
· DataCompare allows you to quickly compare two databases and script updates to bring the data in these databases into sync
· The Database Unit Testing infrastructure allows you to create database unit tests using T-SQL or managed code.
· DataGenerator lets you create data generation plans that produce repeatable sets of meaningful data based upon your existing production databases that can be deployed to a database prior to running unit tests thus ensuring consistent test results
You can find out more about this great new release, see screenshots and find out how to get the early community technology preview which will be available on June 11th at http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/default.aspx
We are making Visual Studio Team Edition for Database Professionals part of the Visual Studio Team Suite, so you’ll get this product for free when we RTM this edition if you are a Visual Studio Team Suite subscriber through MSDN. You can learn more about how to upgrade to Visual Studio Team Suite at http://msdn.microsoft.com/vstudio/howtobuy/renewal/#step
Visual Studio Team Edition for Database Professionals will also be available as a stand alone Edition in the Visual Studio Team System family. You can learn more about how to buy Visual Studio Team System at http://msdn.microsoft.com/vstudio/howtobuy/default.aspx
The team has already started blogging. You can find out more information directly from the product team by visiting the following blogs:
You should only use a dedicated administrator connection (DAC) in special occasions. For example, in some cases, you may find that a user is running a query that has utilized all of your systems resources. The user may be using so much of the system’s resources that no other users, including yourself can login to the system to kill the users connection.
This would be a great use for the DAC. Under this situation before, you would have to stop and start the SQL Server if some time had passed. With the DAC enabled, you as a DBA can login if you had sysadmin rights by qualifying the server with ADMIN:. For example, if you wanted to login into the localhost machine you would use ADMIN:localhost. You will receive an error if the feature is disabled. To enable the feature, go to the SQL Server Surface Area Configuration. After it is enabed, you must stop and start the SQL Server instance.
For more information about Surface Area Configuration, go to http://www.jumpstarttv.com/Channel.aspx?cat=8a62d008-b580-4422-91f7-29515baedce3.
-- Brian Knight
Service Pack 1 is here and it fixes loads of items. Some of which were quite embarassing when training or developing a solution with a client standing behind you. The largest enhancement is the addition of database mirroring, which allows you add high availability to your database by sending transactions to another server in real-time. There are loads of SSIS fixes and enhancements. Add a quick rolled up level, here are the SSIS fixes:
||Usability of the Import/Export Wizard has been improved in multiple-table scenarios.|
||The IDtsPipelineEnvironmentService service lets custom data flow components have programmatic access to the parent Data Flow task.|
||Interoperability with Analysis Services has been improved. The DataReader source in SSIS supports the System.Object data type by converting columns that have this data type to the DT_NTEXT ssISnoversion data type. To change the data type to a type that is more appropriate for your data, you can add a Data Conversion transformation.|
||Performance has been improved for many transformations, such as the Sort transformation.|
||Designer usability has been improved. For example, you can now right-click Dataflow and then click Execute Task to execute only the Dataflow task. You do not have to switch to the control flow to execute only the Dataflow task.|
||The Expression Builder dialog box now has a public and documented API. This increased access to expression-related objects will benefit task developers because expressions are important to the way that tasks are interrelated.|
||We have added a registry-based policy for handling SSIS package signatures as a package is executed. For example, administrators can disable loading of unsigned packages and untrusted packages. Because these settings are in the registry, administrators can propagate these settings in the domain by using Microsoft Windows Group Policy policy settings.|
||In the Advanced Editor dialog box, the Flat File source has the new property UseBinaryFormat. This property supports loading packed decimal data into the pipeline for processing by a script or by a custom transformation.|
||In the Advanced Editor dialog box, the DataReader source has the new property CommandTimeout. You can use this property to modify the time-out period to allow for long-running operations. |
||To create or to modify the property expressions of variables, you can now open the Expression Builder dialog box from the Properties window.|
||You can now add annotations to precedence constraints.|
It's certainly a must download for anyone using 2005.
-- Brian Knight
I love the new Microsoft SQL Server 2005 sample databases (AdventureWorks and AdventureWorksDW). Compared with the Northwind and even worse the Pubs databases, they're amazingly more real-world. They show best practices and demostrate a good portion of the 2005 features. The problem lies in a lot of the SQL Server 2000 examples on the Internet still work in SQL Server 2005 and content that's specific to 2005 is still being produced. So, what to do while you wait for the examples to be updated? Why not live like it's 2002 and install the SQL Server 2000 sample databases? That way the sample RDL files, T-SQL scripts and most other examples on the net will work.
To do this, you'll need to download them at: http://www.microsoft.com/downloads/details.aspx?familyid=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en
Run the SQL2000SampleDb.msi file to extract the files to the strangely placed directory C:\SQL Server 2000 Sample Databases (no you can't change this I'm afraid). Then you can run the scripts in the directory to install the databases or attach the data files. Instpubs.sql creates the pubs sample database and Instnwnd.sql creates the Northwind sample database. If you don't have the SQL tools installed, you can use SQLCMD from a DOS prompt to install them as shown below:
c:\sqlcmd -S .\InstanceName -i instpubs.sql
c:\sqlcmd -S .\InstanceName -i instnwnd.sql
If you do have the tools, just run the scripts in the Query Window of SQL Server Management Studio. The MDF and LDF files are there for attachment. Another method is to right-click on the database tree and click Attach, pointing to the MDF and LDF files. Before doing this though copy the files to the instance's Data directory. After the attachment or script is run, you can remove the directory and files through Add\Remove Programs.
PS: Don't get in the habit of creating new scripts against these databases, as they no longer represent the current technology strategies you would employ.
-- Brian Knight
My typical query I run for retrieiving a list of tables and how many records in each table there are is to use the sp_msforeach stored procedure. The sproc loops through each table in a database and performs an action. Given that, you can run a query like this to obtain a list of tables and how many records are in each table:
"Print '?' select count(*) from ?"
The problem for me with this above query is that it's not sortable without some additional customization. So, I needed a different answer. The sysindexes table holds some very valuable information for a developer or DBA. The table shows you a record for each index in your database as well as the number of rows that are estimated to be in the table. It's not guaranteed to be up to date as far as the number of rows but it's a good number to start with. To model a query at that table, you can use the following syntax:
select convert(varchar(30),object_name(id)) [Table Name], rows from sysindexes
where object_name(id) not like 'sys%' and indid = 1
order by object_name(id)
Instantly you retreive in a single recordset the number of estimated records in each table. The key thing to keep in mind with the above query is I'm weeding out any table that began with sys. If you have a user table that begins with sys, it will be excluded. The indid=1 part of the syntax retrieves only the primary index. There are flaws to this method because it's not exact but it does give you a rought idea of your system information.
-- Brian Knight
One of the frustrating things about the new Visual Studio interface is the load speed. This applies to SQL Server 2005 as well. I have a very new computer which is quite speedy and the splash screen still takes 5-10 seconds to load. On my old machine, this is more like 10-15 seconds while the splash screen is loaded before SQL Server Management Studio would show the login dialog box. A remedy I stumbled across was to add the -nosplash switch at the end of the executables call in the shortcut. After killing the splash screen from displaying, my load time on my good machine was down to 1-2 seconds before being prompted for a login screen. I come in an out of tools so much, that this is quite useful.
To do this, right-click on the shortcut in the start menu for your tool like Business Intelligence Development Studio or SQL Management Studio and click Properties. In the target text box change the shortcut to point to the following for SQL Management Studio:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -nosplash
For Business Intellgience Managment Studio, the same logic applies:
"C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe" -nosplash
Also, this works for the Visual Studio environment in general. I personally hop in and out of these tools all day long and this solved a lot of frustration.
Keeping to their promise, Microsoft plans to release a version of Books Online every quarter for SQL Server 2005. A new version of the SQL server 2005 Books Online is now available only a month after the RTM release at:
The download has new tutorials, instructions and descriptions of common tasks. It's worth the download for sure but downloader beware: the download is 113 MB!
There are three main ways to do dynamic queries inside of SQL Server. My personal favorite method is option one below since it's a single line of code. Option three helps with caching of parameters though. Needless to say, dynamic queries should be avoided whenever possible :).
@TableName = 'Employees'
@ColumnName = 'LastName'
EXEC ('select max(datalength('+
@ColumnName+')) from ' +
@strSQL = 'select max(datalength('+
@ColumnName+')) from ' +
@ColumnName+')) from ' +
-- Brian Knight
DDL triggers are a fantastic way to prvent DDL events in a production database. In the following database triggers, you prevent any type of DDL event, like altering a sproc in production. If a statement is issued, the statement is logged, rolled back and a user receives a message. Databae triggers can be raised at nearly any level. The below example captures any database-level event. There are also server DDL triggers that captures server events like creating logins or changing the configuration of a database.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TRIGGER PreventDDL
PRINT 'DDL events are prohibited, your statement was rolled back.'
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA()
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)', 'sysname')
(GETDATE(), CONVERT(sysname, CURRENT_USER), @eventType,
CONVERT(sysname, @schema), CONVERT(sysname, @object),
-- Brian Knight
Can't make it out west for the launch? You can watch the keynote from Steve Ballmer here live on November 7th! While we're on the topic, I found on the same site some KILLER webcasts that are must-sees.
Dynamic Management Views (DMVs) are one of the nicest new DBA manageability feature in SQL Server 2005. The feature exposes much of what was hidden or unavailable in SQL Server 2000. Let me start the series with one of my favorite DMVs for the lazy DBA like myself: sys.dm_db_missing_index_details. Well, I won't start by saying I'm lazy perse, but this does give you a nice starting point to investigate.
The sys.dm_db_missing_index_details DMV gives you hints on what indexes the database engine thinks you need on a given database. It gives you the object name where the index would be useful and the columns that it would recommend. Use the object_name(object_id) function to translate the object id to it's real logical name. Where this is going to be extremely useful is when you go into a new environment for the first time and need a general gist of where the weaknesses are. Then, you can go logically through the system to make sure this gist is correct.
There are a few important notes with DMVs. DMVs are only in scope until you stop and start SQL Server. After that, the DMVs are all flushed and it may take time to get the true statistics again. The other thing to keep in mind with the sys.dm_db_missing_index_details in particular is the recommendations it gives you are not the law. Much like the Databaes Engine Tuning Advisor, it may recommend indexes that are 16 columns wide, which is probably not practical in your environment. Use its recommendations with a bit of untrustingness.