SQL Server Resources - Edgewood Solutions

To access the Edgewood SQL Server Resources, fill in a one-time registration form. Once you've completed the registration form, you'll be able to access all the Edgewood resources all of the time. We're always adding new content, so keep checking in!

Click Here to Register to Access Edgewood's Free SQL Server Resources

Select a topic and submit query to preview some of the Edgewood resources.

Title	Summary
Different ways to determine free space for SQL Server databases and database files 7/30/2009	One of the functions of a DBA is to keep an eye on free space within the database and database files. The auto grow feature is great as a last resort, but proactively managing the database files is a better approach. Also, this information can be used to shrink data files as needed if there is a lot of free space in the files. In this tip we take a look at a few ways to find the current free space within a database, so you can better manage your database files.
Send Email from SQL Server Express Using a CLR Stored Procedure 7/14/2009	One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.
SQL Server Indexing Basics 7/14/2009	This tip covers the basics for SQL Server indexing.
DOS Commands for the SQL Server DBA 4/14/2009	I have seen your recent tips (Introduction to Windows PowerShell for the SQL Server DBA Part 1, Introduction to Windows PowerShell for the SQL Server DBA Part 2 and PowerShell Support in SQL Server 2008 - Invoke-Sqlcmd Cmdlet) on PowerShell and see value in them. Unfortunately, we do not have PowerShell installed on our SQL Servers and I am not sure when that is going to happen. Until that day arrives could you give me some insight into valuable DOS commands that I can leverage in administrative and research situations on my SQL Servers?
Capture all statements for a SQL Server session 3/18/2009	One thing that frustrates me is that sometimes I write these great queries, but often forget to save them or can't remember exactly what the query looked like that I ran five iterations ago. One smart thing to do would be to always save your scripts, but when your in the process of trying a bunch of different things there is always the chance you may loose something. In this tip we look at creating a server side trace for one session and capturing all completed batches that are run, so you can find that great query that you thought you may have lost.
SQL Server Worst Practices 3/13/2009	Since today is Friday the thirteenth, what are some of the SQL Server worst practices and how can I prevent or fix them? Can you provide some real world examples? Can you focus on the technical aspects of SQL Server that DBAs and Developers have the ability to correct?
Determine Free Disk Space in SQL Server with T-SQL Code 3/12/2009	At our organization we need to check for a minimum amount of free space before proceeding with some processes that run on SQL Server 2000, 2005 and 2008 SQL Server instances. Do you know of a way to find out the free disk space and then fail the process if it does not meet the minimum requirements? Can you provide some sample code?
SQL Server Interview Questions for a Network Administrator 1/21/2009	At our organization many folks wear a variety of hats, because we have a small and talented team. Although we have a SQL Server DBA, our Network Administrator also takes care of some of the SQL Server related tasks in our environment. We are looking to grow our team and we are interested in finding out what a reasonable set of questions would be for Network Administrator candidates that also state that they can support SQL Server. Do you have any suggestions?
SQL Server Join Examples 1/16/2009	I am new to SQL Server and want to learn about the JOIN options. What are all of the JOIN options in SQL Server? What is the significance of each of the options? I am a little confused on the differences and syntax, can you provide some examples and explanations? Are JOINs only for SELECT statements?
SQL Server CLR and T-SQL functions to parse a delimited string 1/14/2009	There are several tips and articles on the internet that discuss how to split a delimited list into multiple rows. This tip shows two approaches to this problem a T-SQL function and a CLR function.
Executing SELECT statements in SQL Server without locking processes 12/15/2008	We have two different situations in our SQL Server 2000 and 2005 application where we suspect we have locking caused by SELECT statements. First, we have reports that need to run in our production environment from a third party application. These seem to be the worst offenders since some of the reports are ad-hoc in a sense and others are the same report issued repeatedly. Second, as a portion of our OLTP application we have some fairly complex SELECT statements referencing fairly complex VIEWs. These are isolated to a specific portion of the application and we have a good sense of their usage. In either of these situations can you offer any suggestions to reduce the locking?
Junior SQL Server Developer Interview Questions 12/11/2008	We are in the process of trying to hire a Junior SQL Server Developer to work on a few of our projects. What expectations should we have on their skill set? What are some fair questions to ask them during the interview process? With a junior level developer, what should be the depth and breadth of the questions?
Finding a better candidate for your SQL Server clustered indexes 12/9/2008	When creating tables it is difficult to determine exactly how the data will be accessed. Therefore when clustered indexes are chosen they are often just the ID column that makes the row unique. This may be a good choice, but once the application has been used and data access statistics are available you may need to go back and make some adjustments to your tables to ensure your clustered indexes are providing a benefit and not a drain on your applications. This tip shows a simple approach on how to determine a better candidate for your clustered indexes.
Using SQL Server DMVs to Identify Missing Indexes 11/25/2008	In a previous tip, Deeper insight into unused indexes for SQL Server, we discussed how to identify indexes that have been created but are not being used or used rarely. Now that I know which indexes I can drop, what is the process to identify which indexes I need to create. I can run the Database Tuning Advisor or examine the query plans, but is there any easier way to determine which indexes may be needed?
Junior SQL Server DBA Interview Questions 11/13/2008	My organization is in the process of hiring a junior SQL Server DBA to add to our team. What are some fair questions to ask as a portion of the interview process? I want to make sure we hire someone who has a good base of knowledge. I am concerned are Senior SQL Server DBAs are looking to stump the candidates which is not really my goal. Do you have any suggestions?
Debugging SQL Server CLR functions, triggers and stored procedures 11/11/2008	One of the nice things about developing your SQL Server CLR code in Visual Studio is that you get to take advantage of the debugging aspects of the tool. When developing it is always beneficial to use a debugger and step through your code to find any coding problems, but how is this done when writing CLR code for SQL Server?
SQL Server for Network Administrators 10/15/2008	My job function is a Network Administrator, not a SQL Server DBA or Developer, but I work with SQL Server on a regular basis. I could use some pointers to improve my skills. What would be a good SQL Server primer for me? What are some high level SQL Server topics that I should be aware of for my regular tasks? Where can I get some additional information to brush up on my skills?
Simplified process to move all SQL Server system databases at one time 10/14/2008	One task that you may need to do as a DBA is to move the system databases from one location to another. The documentation that is found on the Microsoft site is helpful, but the steps are more geared toward moving one database at a time. In this tip we look at a streamlined process of moving all system databases at the same time.
Database Mirroring adjusting the automatic failover time 10/13/2008	Database Mirroring is becoming a popular feature of SQL Server 2005. By following a few simple steps either by using the SQL Server Management Studio or by running a few T-SQL commands you can easily establish database mirroring for one or more of your databases. One of the configuration options for Database Mirroring is the High Availability mode. With this option three servers are put in place the principal, mirror and witness servers. This is the only option that allows for automatic failover. One of the things that I have noticed is that when there are periodic network issues a failover occurs even though there are no issues with the Principal server. Are there any options to delay the failover, I did not see anything in SQL Server Management Studio?
SQL Server Cursor Examples 10/7/2008	In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.
How to find a SQL Server DBA Job 10/6/2008	I am new to the SQL Server platform and want to break into the field. I have been doing IT work for a while now and I am looking to focus my career on SQL Server. Do you have any suggestions? Where should I start? What should I be prepared for during the interview process?
Rounding Functions in SQL Server 9/23/2008	I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options.
SQL Server T-SQL Functions Interview Questions 9/22/2008	In the latest installment of the MSSQLTips interview question series we are going to cover questions on T-SQL functions. The questions are categorized as either easy, moderate or difficult with the question available to read, but the answers are hidden to challenge yourself. Once you have your answer formulated then highlight the answer to see how you have done. Good luck!
Auto generate SQL Server restore script from backup files in a directory 9/16/2008	One of the ongoing challenges of a DBA is to backup and restore databases. Backups are done on an automated schedule, but restores can take on many different versions, you may need to restore a production database, restore a development or test database or just create another copy of the database somewhere else. There are several ways of automating the restore process and creating a script, but this approach shows a way this can be done by just reading the contents of a directory for the backup files that exist.
Changing the default SQL Server backup folder 9/15/2008	When you install SQL Server the path for the installation is generally something such as the following: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL. In this directory there are also folders for your DATA files and also your BACKUP files. Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases, but you can not change the default directory for your backups. Is it possible to change the default directory for backups, so it does not need to be specified each time I run a backup?
SQL Server Security Interview Questions 8/20/2008	SQL Server Security, probably one of the most controversial and debated topics among SQL Server DBAs and Developers. One person's security is another person's nightmare and vice versa. With security being so important for so many different reasons let's try to determine some baseline interview questions although some of the responses can vary greatly based on the environment and industry. Good luck!
Database Mirroring Automating Failover for Multiple Databases 8/18/2008	Database Mirroring was released with SQL Server 2005 and is becoming a popular choice for a failover solution. One of the big issues with Database Mirroring is that the failover is tied to one database, so if there is a database failure the mirrored server takes over for this one database, but all other databases remain functional on the primary server. The drawback is that more and more applications are being built where multiple databases make up the application, so if only one of the databases fails over the other database will still be functional on the principal server, but the application won't work. How can I be notified when this happens and make all of the databases failover?
Recover from a SQL Injection Attack on SQL Server 8/11/2008	Lately it seems like SQL Injection attacks have been increasing. Recently our team has worked through resolving a few different SQL Injection attacks across a variety of web sites. Each of these attacks had a number of similarities which proved to point back to the same source. With this information in hand, the resolution should be much quicker. As such, if your web site is attacked with SQL Injection, how should you address it? How can the identification, analysis, recovery and resolution be streamlined? What are some lessons learned?
SQL Server Find and Replace Values in All Tables and All Text Columns 8/5/2008	In a previous tip, Searching and finding a string value in all columns in a SQL Server table, you showed how to find a string value in any text column in any table in a database. I was wondering how this can be taken a step further to allow a replacement of text in a string data type for all columns and tables in my database. I have read about a SQL injection attack where text is inserted and this could be a good way to remove the offending text.
Calculating Mathematical Values in SQL Server 7/24/2008	In our application we have the need to perform mathematical calculations. Right now we are doing so in our front end application. Unfortunately we are starting to experience performance problems with large data sets and differences in calculations due to developers using different logic. We are seeking some other options to perform the calculations. Does SQL Server perform basic mathematical calculations?
Deeper insight into unused indexes for SQL Server 7/22/2008	One of the balancing acts of SQL Server is the use of indexes. Too few indexes can cause scans to occur which hurts performance and too many indexes causes overhead for index maintenance during data updates and also a bloated database. So what steps can be taken to determine which indexes are being used and how they are being used.
Inserting, Updating or Deleting Data in SQL Server from an Excel Spreadsheet 7/15/2008	I have seen your previous tips (Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server) related to working with Excel and SQL Server data. The main command used in one of the tips is OPENROWSET. This has been beneficial for us because in our environment because our business users provide data to us in the form of Excel spreadsheets. Many of the situations end up translating to INSERT, UPDATE or DELETE code in one or more of our SQL Server databases. We always upload the data to a table and then begin the process. Although this process works are you familiar with any other options to directly perform the INSERT, UPDATE or DELETE operations? Are their any benefits to transitioning our code to another approach with the OPENROWSET command?
SQL Server Integration Services Interview Questions 7/9/2008	Over the years at MSSQLTips.com the tips focusing on interview questions have been popular with the community. From our first tip on SQL Server Concurrency and Locking Interview Questions to our most recent on SQL Server Backup and Recovery Interview Questions. Our Integration Services tips have also been very popular and a big help to those developers\DBAs making the transition from DTS. Let's bring the two topics together in today's tip and test your knowledge of SQL Server Integration Services with interview style questions where the questions are visible, but the answers are hidden until you highlight that portion of the tip. Good luck!
Dynamically controlling the number of rows affected by a SQL Server query 7/8/2008	One thing you may need to do is dynamically return a set amount of rows based on user input. This could be for a search function, reports, dropdown lists or whatever. Instead of hard coding a set value you would like to pass in a variable that will then determine the number of rows to return. How can this be done with T-SQL?
Searching and finding a string value in all columns in a table 6/18/2008	Sometimes there is a need to find if a string value exists in any column in your table. Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column". So trying to find a value in any column in your database requires you to build the query to look through each column you want to search using an OR operator between each column. Is there any way this can be dynamically generated?
SQL Server Disasters with Preventive Measures 6/13/2008	Over the years I am sure we have all seen and heard about various SQL Server disasters either at our organization, on the web, or at conferences\user group meetings. Friday the thirteenth seems like an appropriate time to outline some of the common disasters we have seen over the years and provide some hind sight into the situation to prevent a future disaster. Have you seen disasters in your environment? I would bet you have at some point. Let's see if our top 13 disasters matches your experiences.
SQL Server Backup and Recovery Interview Questions 6/11/2008	If you are preparing for a SQL Server DBA interview as the interviewer or interviewee, today's tip should offer value to you. This tip has interview style questions graded as either easy, moderate or advanced related to backup and recovery. To help gauge your skills, the question is available to read, but the answer is intentionally hidden. Once you have thought about your answer, highlight the answer and see how you have done. Good luck!
Problem using DISTINCT in case insensitive SQL Server databases 5/27/2008	SQL Server gives you the ability to store mixed case data in your databases, but depending on how you create your databases SQL Server will ignore the case when you issue T-SQL commands. One of the problems you may be faced with is that you want to get a distinct list of values from a table to show the differences in your table, but if your database is setup as case insensitive the DISTINCT clause does not show the differences it all gets grouped together. So based on this what options are there?
Scripting out T-SQL commands generated by SQL Server Management Studio 5/23/2008	With SQL Server Management Studio you have the ability to do just about everything you can do using T-SQL commands. One problem with using the GUI is that it is difficult to remember everything you clicked on to reproduce the behavior a second time. Another issue is that most of what you do through the GUI is done immediately which may not always be the best scenario. How can I capture what SQL Server is doing so I can reproduce the behavior or run the commands at a later time.
Interview Questions for New Features in SQL Server 2005 5/14/2008	Staying up on the latest SQL Server technologies is a huge benefit to employers. Many seek only the best and brightest to build solutions for the organization. The general premise is the more knowledgeable their team is the more opportunities the organization will have to succeed. With the release of SQL Server 2005 many new features were introduced, but not taken advantage of across the industry for one reason or another. Break out of your SQL Server 2000 shell and test your knowledge on the SQL Server 2005 new feature set.
Selecting the database recovery model to ensure proper backups 5/13/2008	One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.
Getting started with SQL Server stored procedures 5/9/2008	I have been using SQL Server for some time, but all of the code that is issued against the database is embedded in the application code. I know that you can create stored procedures, but I am not exactly sure where to start or what I need to do to implement stored procedures.
Full Text Search Noise Words and Thesaurus Configurations 5/5/2008	I have heard that Full Text Search uses a noise words to eliminate meaningless words in searches. I have also heard that a thesaurus is used, but I am not exactly sure how. Can you provide some details related to how both of these technologies are used in Full Text Search? I am interested in more of a background on these specific Full Text Search technologies as well as where the files are located and how I can update them.
Dynamic SSIS workflow control based on variables 4/29/2008	When developing SQL Server Integration Services (SSIS) packages there is sometimes the need to only run certain steps or paths in the package execution either based on time period or maybe a parameter value that is passed to the package or queried from the database. How do you setup an SSIS package to have different execution paths based on a parameter value?
SQL Server Health and History Tool 4/21/2008	I have heard about the SQL Server Health and History tool, but I have never used it. How long has this tool been around for? In your opinion is this a tool worth using? What sorts of functionality does the tool have to offer? What types of limitations does the tool have? Is this tool primarily intended for DBAs, Developers or Network Administrators? Where can I download a copy of the tool and what do I need to do to install it?
Identify SQL Server 2005 Standard Login Settings 4/16/2008	Gaining insight into my SQL Server standard and Windows logins has historically been a challenge in terms of determining password changes, failed login attempts, etc. I have noticed that you have experienced the same issue with your tip entitled 'When was the last time the SQL Server sa password changed' and I have noticed some information in the forums on the topic as well. With SQL Server 2005 can I gain any more insight into the SQL Server standard logins part of the equation? I know I can talk to my Network Admin counter parts for some of the Windows related login information. Any and all recommendations would be appreciated.
Methods to determine the status of a database 4/15/2008	I think that just about all aspects of the SQL Server Management Studio can be exposed by using T-SQL instead of having to use the GUI. I have been trying to write some routines to determine whether a database is online as well as to check some other information about the status of a database. What options are there to get status information for a database without using the GUI?
Reading the SQL Server log files using T-SQL 4/14/2008	One of the issues I have is that the SQL Server Error Log is quite large and it is not always easy to view the contents with the Log File Viewer. In a previous tip "Simple way to find errors in SQL Server error log" you discussed a method of searching the error log using VBScript. Are there any other easy ways to search and find errors in the error log files?
SQL Server DBA Phone Interview Questions 4/9/2008	The phone interview. It has tales of bringing normally rationale people to a terrified state. I have even heard of a DBA that was so worried about a SQL Server phone interview that 'they just happened to be in the office park' where the company was located and actually wanted the interview face to face. The reality is, just about all organizations that I work with have a phone interview as a right of first passage in the process. The employer wants to quickly determine if the DBA candidate could be qualified for the position from a technical perspective and if they will fit into the team. As a DBA, what sorts of things should you be on the lookout for during a phone interview? What do you think the employer is expecting? Is this the technical interview or not? Should you try to avoid the phone interview all together and just 'pop-in' for a face to face interview?
Designing Tables for Audit Data in SQL Server 4/2/2008	My company is just starting to look at adding functionality to retain historical data for key tables and columns for auditing purposes in many of our SQL Server databases. I have seen some of your recent tips related to triggers (Forcing Trigger Firing Order in SQL Server and Trigger Alternatives in SQL Server - OUTPUT Clause). Based on using triggers or a similar technology, what is the best way to store the historical data? What options are available to store the data? Are there really a variety of options? Should the historical data be in the same database as the original data? Does it really make a difference?
Simple approach to populate a column with a sequential number not using an identity column 4/1/2008	I have a database table that has a lot of data already in the table and I need to add a new column to this table to include a new sequential number. In addition to adding the column I also need to populate the existing records with an incremental counter what options are there to do this?
Issues with running backup log with no_log or truncate_only 3/27/2008	On my database server I have my databases set to the full recovery model, but the transaction logs get quite big, so I am issuing a BACKUP LOG with NO_LOG. I am not exactly sure if this causes any issues, but I know that I am able to free up space in my transaction log and shrink the file. Is this the correct way to handle this situation?
Why Upgrade to SQL Server 2008 3/25/2008	With the recent release of SQL Server 2008, getting up to speed on the technology is necessary from a number of different perspectives. From your view of the world what are the drivers from a business, technology and career perspective? Have you heard insight or opinions related to employers view of SQL Server 2008? Do you have any suggestions when talking to management about moving to SQL Server 2008?
Analyze and Correct a large SQL Server MSDB Database 3/24/2008	It has come to my attention that some of our MSDB databases are getting what I would consider large for a system database. Some of our MSDB databases are over 2 GB which is a little perplexing because I know we do not create any user defined objects in that database. Can you give me some insight into the issue? I know we have this issue with both SQL Server 2000 and 2005 instances.
Free Performance Profiler Tool for SQL Server 2005 Express 3/20/2008	With the release of SQL Server 2005 Express, Microsoft has offered a lot of new features that did not exist with MSDE. A couple of these things include Reporting Services features as well as a GUI management tool to manage SQL Server instead of having to do everything via command line. Although they have added a lot of new features, not all of the tools needed to maintain and troubleshoot SQL Server are included. One of these tools that is not included is Profiler. You can still run a server side trace on your SQL Server Express databases as well as import the data into a table for analysis, but sometimes having a GUI tool to troubleshoot an issue is much simpler than using a server side trace. So what are the options?
How to stop logging all successful backups in your SQL Server error logs 3/18/2008	The SQL Server error log is a great place to find information about what is occurring on your database server. One helpful thing that each log offers is that it shows you all of the failures and/or errors that have occurred since the last start of SQL Server or since the last time you cycled the error log. On the flip side of that, it also shows you success messages such as backups, logins, DBCCs, etc... Although this may be helpful to log all of this information it unfortunately clutters up your error logs pretty quickly especially if you are doing a lot of database backups every day. So how can you turn off all of those successful backup messages.
SQL Server Database Requirements 3/17/2008	In our organization, I have noticed that database requirements are never included as a portion of the system requirements. The requirements always focus on the interface and we derive the database design from the interface as well as fill in some of the gaps. For some developers that process seems to yield a decent product, but not always. I think if we requested database requirements from both the business and technical management our overall offerings would be much better and we would have less patch\fix cases. For the requirements in our environment I have a couple of ideas in mind, but I am hoping you can give me a broader view of the situation with respect to overall SQL Server database requirements.
SQL Server T-SQL Interview Questions 3/7/2008	If you are preparing for a SQL Server Developer or DBA interview as the interviewer or interviewee, today's tip should offer value to you. This tip has interview style questions graded as either easy, moderate or advanced related to T-SQL, the relational engine programming language. To help gauge your skills, the question is available to read, but the answer is intentionally hidden. Once you have thought about your answer, highlight the answer and see how you have done. Good luck!
How to join tables where columns include NULL values 3/4/2008	When building database tables you are faced with the decision of whether to allow NULL values or to not allow NULL values in your columns. By default SQL Server sets the column value to allow NULL values when creating new tables, unless other options are set. This is not necessarily a bad thing, but dealing with NULL values especially when joining tables can become a challenge. Let's take a look at this issue and how this can be resolved.
Script to determine permissions in SQL Server 2005 2/22/2008	At times I run into application issues when I am unable to perform particular functions in the application that some of my peers are able to complete. I need to be able to quickly and easily understand my permissions in SQL Server. Do you know of an easy way to do so? Do you know how I can compare 2 different users to see what the difference could be at an instance, database or object level? Any and all suggestions would be appreciated.
SQL Server Reporting with Microsoft Excel 2/21/2008	Our business users are very savvy with Microsoft Excel. The issue we struggle with is getting the SQL Server data to them in a streamlined manner. We are familiar with some of your earlier tips related to integrating Microsoft Excel and SQL Server. We have gone down the paths and they have been helpful for a number of standardized reports. Now that the users can see the data, we are getting a number of requests to modify reports on a daily basis. Unfortunately, we do not have the staff to support all of the reporting needs. Do you have any suggestions to conduct SQL Server reporting directly with Microsoft Excel from a user perspective?
Upgrading databases and changing database compatibility levels 2/18/2008	When upgrading databases from an older version of SQL Server using either the backup and restore method or detach and attach method the compatibility level does not automatically change and therefore your databases still act as though they are running using an earlier version of SQL Server. From an overall standpoint this is not a major problem, but there are certain features that you will not be able to take advantage of unless your database compatibly level is changed. This tip will show you how to check the current compatibly level, how to change the compatibly level and also some of the differences between earlier versions and SQL Server 2005.
Tempdb Configuration Best Practices in SQL Server 2/12/2008	In SQL Server 2005, TempDB has taken on some additional responsibilities. As such, some of the best practice have changed and so has the necessity to follow these best practices on a more wide scale basis. In many cases TempDB has been left to default configurations in many of our SQL Server 2000 installations. Unfortunately, these configurations are not necessarily ideal in many environments. With some of the shifts in responsibilities in SQL Server 2005 from the user defined databases to TempDB, what steps should be taken to ensure the SQL Server TempDB database is properly configured?
SQL Server Performance Tuning Interview Questions 2/7/2008	In the latest installment of the SQL Server interview questions, we will outline questions suitable for a DBA or Developer interview to assess the candidates skills related to SQL Server performance tuning. In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did. Good luck!
Import, Export, Copy and Delete Integration Services Packages in SQL Server 2005 2/6/2008	I have seen many of the changes with SQL Server 2005 Integration Services (SSIS) versus SQL Server 2000 Data Transformation Services (DTS). Integration Services certainly has much more functionality out of the box than DTS and I am learning SSIS as my projects move forward. One item that has seemed to be a thorn in my side is deploying an SSIS package. So, I have read your tip (SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package) related to deploying a package and wanted to find out if any other options are available? Can you shed some light on the situation?
Creating delays in SQL Server processes to mimic user input or delay process steps 1/30/2008	Sometimes when running processes there may be the need to create delays before the next step runs. This could be for processes that are run externally and therefore you have no control over when that process finishes, the need to mimic a delay in user response if you are doing testing or maybe you are collecting data at various intervals and want to delay the next collection time. These are just a few examples of the need to create a delay, so what approaches exist?
SQL Server System Databases 1/24/2008	I have seen the Master, Model, MSDB, etc. databases in Management Studio and in a variety of scripts. Right now I have a fairly rudimentary set of knowledge about these databases just based on reviewing those scripts. As I expand my SQL Server knowledge, I am interested in learning more about the SQL Server system databases and how I can use them to help me on a daily basis. I am sure their are many recommendations when working with these databases and I am interested in those as well.
SQL Server 2008 First Impressions 1/23/2008	At our organization we have had limited exposure to SQL Server 2005 because SQL Server 2000 just works. We have worked with SQL Server 2005 in some particular areas, but we do not have the enterprise experience we need to move forward. We know support will be lifted for SQL Server 2000 and know we need to jump on SQL Server 2005 or 2008 in short order. How can I get some first hand experience and what sorts of things should I try to do?
Gathering I/O statistics down to the database file level 1/21/2008	When managing your SQL Server environment there are many aspects that need to be reviewed to determine where the bottlenecks are occurring to ensure you are getting the best performance possible. SQL Server offers many great tools and functions to determine issues with locking, blocking, fragmentation, missing indexes, deadlocks, etc... In addition, to looking at all of these areas another area of concern is I/O. Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on the server. What other options are available to look at I/O related information down to the file level for each database?
Determining SET Options for Current Session 1/18/2008	With each session that is made to SQL Server the user can configure the options that are SET for that session and therefore affect the outcome of how queries are executed and the underlying behavior of SQL Server. Some of these options can be made via the GUI, while others need to be made by using the SET command. When using the GUI it is easy to see which options are on and which options are off, but how can you get a better handle on all the options that are currently set for the current session?
Access to SQL Server 2008 Virtual Environment 1/16/2008	Back in June 2007, we announced that SQL Server 2008 is on its way in this tip SQL Server 2008 has arrived. In addition, we talked about how to learn more about SQL Server 2008 in this tip Learning SQL Server 2008. The problem with learning something new is that it often takes a lot of time to setup an environment before you can actually do anything. With server virtualization this is much easier to setup and take down servers at will, but this still requires effort to get the virtual server setup in the first place. Are there any easier solutions for getting your hands on SQL Server 2008?
SQL Server 2005 Exposed - Static Port Assignments 1/15/2008	We have noticed some connection issues on a variety of our web based applications when the corresponding SQL Server is rebooted. We have noticed the issue with SQL Server 2005 Express edition as well as the SQL Server 2005 Standard edition. It seems like after the reboot we have to change our application connection strings and firewall settings in some circumstances in order to correct the situation. We know in some of the circumstances the applications are in a transient state, but others have been deployed and unchanged for a while. When we have to reboot servers, the whole team is on guard to test the applications and ready to troubleshoot the issues. We having found that in some circumstances SQL Server 2005 is listening on different ports. Should we write this issue off to gremlins or is there a real solution for our problem?
Verbose SQL Agent Logging 1/14/2008	How often have you reviewed a SQL Server Agent Job’s history to determine why a job failed only to be confronted with a minimal level of detail? When a job failure occurs it is imperative to be able to quickly identify the root cause in order to determine the correct remediation. As such, what are some of the options to consolidate the logging on a per job basis? How can I setup this functionality for my key SQL Server Agent Jobs?
Interview Questions - SQL Server System Databases 1/11/2008	In the latest installment of the SQL Server interview questions, we will outline questions suitable for a DBA interview to assess the candidates skills related to SQL Server system databases. In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did. Good luck!
Forcing Trigger Firing Order 1/10/2008	I have two triggers defined on my table which are set to fire on the same table actions (i.e. an INSERT, DELETE, UPDATE transaction). The second trigger that fires is dependent on the first fired trigger. How can I make sure that they fire in the correct order to enforce my business logic? In addition, as our system changes, what are some of the caveats that I need to be aware of when managing the trigger firing order?
Getting exclusive access to restore databases 1/8/2008	A task that you may be faced with as a DBA is the need to refresh a test or development database on a periodic basis. This may be a scheduled process or it may be an ad hoc process. One of the things that you need to do when restoring a database is to ensure you have exclusive access to the database otherwise the restore process will not work. What options are there to ensure you have exclusive database access, so the restore process will work without issue?
Switching data in and out of a SQL Server 2005 data partition 1/7/2008	One of the challenges of working with large datasets or datasets that become stale is the need to move large chunks of data in and out of your tables. This can be done with large INSERT and DELETE statements or by using views, but with SQL Server 2005 data partitioning makes this task much easier to manage than in previous versions of SQL Server. In this tip we will take a look at how to use the SWITCH operator to move data in and out of partitions.
Adding or dropping identity property for an existing column 1/4/2008	One nice feature of SQL Server that is used quite a bit is the use of identity columns. This function gives you a simple way of creating a unique value for every row in your table. Adding a new column and making it an identity column is an easy thing to do as well as dropping an existing column that is an identity column, but how can you modify an existing column to make it an identity column or remove the identity property from an existing column?
10 Step Checklist: Re-Architecting a SQL Server Backend System 12/28/2007	All systems generally follow the same basic life cycle from inception, development, maintenance and sun-setting. However, some systems seem to take a slightly different path where they are re-architected either in entirety or particular modules of the application are significantly re-architected. Re-architecting a production backend system that needs to continue business operations presents a significant challenge. Although the positive side of the situation is that the problems that system suffers from have already surfaced and the business processes have been tested to determine if they are beneficial or not. Now it is a matter of rolling up your sleeves re-architecting. So where do we start?
Rollback: Identifying and Deleting Archive Tables 12/27/2007	In an recent tip (Backing up SQL Server Data for Rollback Purposes), the second option in the tip outlined steps to backup data prior to making a mass data changes. This is a critical process in order to rollback if an issue arises. Unfortunately, one missing component in that tip is identifying and deleting unneeded archive tables. Depending on the deployment schedule, the amount of archived data can quickly add up, which expands backup\restore windows, increases tape\storage costs and may lead to a situation where unneeded data lingers for months or years. Although I want to have a solid rollback plan, I also need to be able identify and delete the archive tables once they are 30 days old. Any suggestions on how to do so?
SQL Server Database Backup Retention Periods 12/26/2007	As a best practice we issue full SQL Server database, differential and transaction log backups. We have setup a process to backup to local disks and then also copy the files to a centralized set of storage. On a weekly basis the centralized file system is backed up to a tape backup device. The tapes are rotated on a weekly basis in order to maintain 4 weeks of data on the tapes stored off site. I know I can get data off of the tapes, but that process is time consuming, not well tested from my perspective and I am not in control of the overall process. Can you offer some recommendations from a SQL Server backup retention perspective?
Backing up SQL Server Data for Rollback Purposes 12/20/2007	Many of our SQL Server releases include data changes, not just code changes. In some respects the SQL Server data changes are more of an issue to manage than the code changes, because we can easily isolate the code changes and roll them back as needed. With the data changes if we add a simple lookup value to a table and update particular records we can do some detective work and trace back the data. It takes time, but we can typically trace it back. Unfortunately, when we change data by a percentage or make numerous changes in a table, those changes are a bit more difficult to trace back because rolling back a percentage is not as precise as we require. Thus far our SQL Server rollback plan has been to just restore a preliminary SQL Server database backup that was issued, but this is an time consuming proposition if we have only one small issue. Can you offer a better approach to isolate the SQL Server data changes and only rollback specific data?
Limiting amount of data returned with TEXTSIZE 12/19/2007	When working with large-value data types such as varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data types sometimes you do not need to return the complete column contents, but maybe only a portion of the data. This may be for testing or maybe just to check to see if there is a value or not. This can be done by using the SUBSTRING function or the LEFT function, for each column that uses this data type, but are there any other functions that can be used to limit the amount of data returned by a SELECT statement for all columns of these data types?
Disabling (or Enabling) SQL Server Agent Jobs 12/18/2007	As a portion of our backup and recovery process I need to disabled (or enable) all of our SQL Server Agent Jobs depending on the situation and the SQL Server instance. I know I can use the GUI to click through and make the changes, but on a number of our SQL Server instances we have 50+ jobs, so this is a monotonous task. Do you have any scripts to enable or disable all SQL Server Jobs? What about enabling or disabling all SQL Server Jobs by Job name or Job Category name?
Passing dynamic parameter values to an SSIS package 12/17/2007	When using SQL Server Integration Services (SSIS) the ideal situation is to make the code as re-useable as possible, so the same code-set can be used to handle multiple situations instead of having a hard-coded solution. In a previous tip, "Dynamic Flat File Connections in SQL Server Integration Services" we looked at how to create a dynamic file source based on some variable settings within the SSIS package. This solution was great, but how do I take this further and pass in a dynamic value into an SSIS package?
How to store longer SQL Agent Job Step output messages 12/14/2007	When working with SQL Agent jobs sometimes it is difficult to determine why a job failed or even exactly what occurred. For each job step SQL Server provides a message, but it is not always that easy to determine exactly what was occurring during that step. The default message that is saved in the job history is 1024 characters. In most cases this may be enough, but if you have a long running process you may need to store more than 1024 characters. Is there any way to get additional information within the job history to help troubleshoot issues as well as just knowing what occurred?
Importing Excel data using SSIS and dealing with unicode and non-unicode data issues 12/13/2007	One task that most people are faced with at some point in time is the need to import data into SQL Server from an Excel spreadsheet. We have talked about different approaches to doing this in previous tips using OPENROWSET, OPENQUERY, Link Servers, etc... These options are great, but they may not necessarily give you as much control as you may need during the import process. Another approach to doing this is using SQL Server Integration Services (SSIS). With SSIS you can import different types of data as well as apply other logic during the importing process. One problem though that I have faced with importing data from Excel into a SQL Server table is the issue of having to convert data types from Unicode to non-Unicode. SSIS treats data in an Excel file as Unicode, but my database tables are defined as non-Unicode, because I don't have the need to store other code sets and therefore I don't want to waste additional storage space. Is there any simple way to do this in SSIS?
T-SQL Scripts: Calculating Table Width 12/12/2007	I am concerned our database design is overly denormalized. I believe we have some very wide tables which makes some of our coding very easy, but I am concerned about the data access and overall performance. Before I go too far down the path of just changing some of these tables or trying to change the ways of my team for future database design, how can I find out how wide some of these tables really are based on the data as compared to the theoretical maximum size? Can you provide a script or two that I can run on my SQL Server databases? Can you also provide any resources for practical database design? I know having some denormalization is reasonable, I am just not sure if some of the database design has gone to an extreme or not.
Interview Questions - SQL Server Agent 12/11/2007	In the latest installment of the SQL Server interview questions, we will outline questions suitable for a DBA interview to assess the candidates skills related to SQL Server Agent. In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did. Good luck!
SSIS: Perfmon Counters for the Data Flow Engine 12/10/2007	We have adopted SQL Server 2005 Integration Services and we are moving a great deal of data on a consistent basis throughout the day for a number of systems. Unfortunately, we have been seeing some memory related issues and wanted to find out how we could monitor these on a regular basis? We want someway to collect performance related data and monitor the overall process. What tools and metrics are available for monitoring an SSIS Package? Can you provide some examples?
Solid State Disk Drive Considerations for SQL Server 12/7/2007	Are solid state disk drives really the next big thing? Will they be able to expand the IO needs of an application further than the current set of disk drive technologies? Is this a technology I should consider with my future SQL Server platforms? The reason that I ask is because our SQL Server based applications are experiencing IO issues. We have split our databases into numerous file groups on numerous disk drives and have had some relief. At the same time we have been focusing on tuning our code and have made strides in that area. We just cannot tune the code quick enough to meet the demands. Are solid state disk something we should consider evaluating and migrating to in the near term to help with our performance issues?
Properly Sizing the TempDB Database 12/6/2007	Properly sizing the TempDB database has recently surfaced with some new customers and has also been a thread (http://blogs.mssqltips.com/forums/t/64.aspx) posted in the MSSQLTips forums. Here is the general scenario: On all of my SQL Server instances (development, test and production) the TempDB database is configured for auto growth for the database and transaction log. During a recent performance tuning engagement I learned a lot about how TempDB is being used in my environment. It was brought to my attention how much the TempDB database is being used by our applications. I had no idea our core business applications are using the TempDB database so heavily. In addition, I did not know how large the TempDB database and transaction log have grown. As such, I know I cannot change the applications in short order so I need to get a handle on how to resolve these issues and size the TempDB database correct. How should I do so?
Joining data and differences of using UNION and UNION ALL 12/5/2007	Sometimes there is a need to combine data from multiple tables or views into one comprehensive dataset. This may be for like tables within the same database or maybe there is a need to combine like data across databases or even across servers. I have read about the UNION and UNION ALL commands, but how do these work and how do they differ?
Expanding advanced search capabilities with the FREETEXT command or not? 11/29/2007	I have been reading about the Full Text Search capabilities in SQL Server 2005 and your tips have been a big help to improve how we query our data. Thus far converting our core queries to leverage Full Text Search has been a big help. I have been seeing information about the FREETEXT command and I would like to learn how to use this FREETEXT command versus the CONTAINS command. Should I use the FREETEXT command over the CONTAINS command or vice versa? Can you also provide some examples of using the FREETEXT command to learn about the variety of capabilities available?
A Trigger Alternative = OUTPUT Clause 11/28/2007	I have processes in my applications that I need to audit the data and write the data out to a specific audit table, but only in specific circumstances based on business logic and not for every transaction. I know triggers are available on a per table basis to audit the data, but I do not want to audit all of the INSERT, UPDATE or DELETE statements that run from a variety of applications (fat clients, web clients, automated processes, monthly batch processes, etc). How can I audit only the specific processes versus all transactions that run? Are any elegant options available directly with the INSERT, UPDATE or DELETE statements?
Please do not drop the triggers 11/27/2007	We were just working through a deployment and ran into a snag which caused us to run over the allocated amount of down time. To speed up the some of the upcoming steps in the process we dropped a few triggers on our larger tables. We did this to prevent the large number of inserts into the audit tables from extending the down time even further. Not having the trigger fire for this process was something we were willing to sacrifice. Unfortunately, once we released the system to production we no longer had the triggers and had to conduct a manual review to ensure we had all of our triggers. Missing the data once we went to production was a major issue on top of having to extend the down time. Do you have any suggestions on how to better manage the triggers in this scenario?
Warning: When problems strike 11/26/2007	Blunders, mistakes, absent mindedness, being over extended - you name, it happens. Whether it happens to you or someone on your team, the repercussions can be severe. Over most people's careers you have seen issues to a varying degree of severity. To me the biggest blunder is knowing that a problem exists and either ignoring it or procrastinating on the implementing the resolution. Do not be upset when the problem strikes and you have to drop everything you are doing to fix an issue that could have been prevented. This tip focuses on SQL Server problems that could have been prevented. See if you can relate to any of these items.
Getting Started with Reporting Services 11/21/2007	SQL Server has a lot of great features and taking the time to find out what these features are and secondly taking the time to understand these new features is often time consuming and usually only done if there is a real pressing problem. I still run into these challenges and it amazes me that there is always some way to solve the problem with SQL Server. I think one of the greatest new add-ons to SQL Server is Reporting Services, but it still seems like a lot of people have not yet adopted Reporting Services. Microsoft even included Reporting Services features with SQL Server 2005 Express, so this is definitely something you should take the time to investigate. The big question though is how to get started.
Making data imports into SQL Server as fast as possible 11/20/2007	When bulk loading data into SQL Server, in most cases you want to have this process run as fast as possible. Depending on the options you select and how indexes are setup on the table, the time to load the data could differ quite drastically. In this tip we will take a look at different options for bulk loading along with examples and the time it takes for each of these operations to complete.
Script to create commands to disable, enable and drop / recreate Foreign Key constraints 11/19/2007	Foreign keys (FK) are designed to maintain referential integrity within your database. When used properly FKs allow you to be sure that your data is intact and there are no orphaned records. On the flipside of using FKs to maintain referential integrity, they also become an issue when you need to change table structures or temporarily modify data that might violate the foreign key constraint. Other tips have been written that show you how to identify your FKs and why you should use them, but what is the best approach for manipulating FK constraints to make structure or data changes?
Dynamic stored procedure execution form 11/16/2007	The purpose for most stored procedures is for execution within applications, but there are some stored procedures that may be used for administrative purposes and only get executed ad hoc. In addition, during testing you run stored procedures interactively to make sure things are working correctly. You have the ability to run any stored procedure directly from a query window and include the necessary parameters, but is there any easier way to know what parameters a stored procedure requires and to pass the parameters directly to a stored procedure?
CLR Function - Delete older backup and log files 11/15/2007	In a previous tip we looked at how to put together a CLR function for sorting text data. In addition, we have also written tips about how to mimic the functionality of maintenance plans without having to use a maintenance plan. In one of these previous tips, "Maintenance task to delete old backup files" we outlined how to delete older backup files by using a VB Script. To take this deleting of older files a step further, this tip will look at this same task to remove older backup and log files, but this time using a CLR function.
SQL Server Crosswalk - Database Management Commands 11/12/2007	It is common in many SQL Server database management scripts to rename databases, detach and attach databases, change the database status, etc. With all of the changes related to deprecated SQL Server features, are any of these commands (sp_attach_db, sp_renamedb, sp_dboption, etc.) marked for replacement in SQL Server 2008? If so, what are the corresponding commands that I need to start using in order to not re-write my code in the future? What is the new coding standard?
Identifying Deprecated SQL Server Code with Profiler 11/9/2007	In your recent tip (WARNING: SQL Server Deprecated Features) you have outlined a number of deprecated features. The listing with the mapping to the new code set is beneficial. Unfortunately I have an enterprise SQL Server environment and I need to be able to identify the deprecated features in more of an automated manner. We do not have the time to check all of our code (stored procedures, functions, etc.) and I know we have T-SQL embedded in middle tier and front end applications. How can I analyze this code in an efficient manner?
SQL Server script to rebuild all indexes for all tables and all databases 11/6/2007	One of the main functions of a DBA is to maintain database indexes. There have been several tips written about different commands to use for both index rebuilds and index defrags as well as the differences between index maintenance with SQL Server 2000 and SQL Server 2005. In addition, other tips have been written about using maintenance plans to maintain indexes on all databases. One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not. What other options are there to rebuild indexes on all databases besides using a maintenance plan?
Maximizing your view into SQL Query Plans 11/5/2007	One nice feature of SQL Server is being able to display graphical query plans. The problem with graphical query plans though is that they are sometimes extremely large and often difficult to maneuver through the plan to identity where the issues are within your statement or statements. Within SQL Server Management Studio you have the ability to zoom in and zoom as well as the ability to find certain parts of the query plan by hovering over a reduced version of the query plan, but often you are constrained by the size of the results pane to view the plan and it is still quite difficult to find the information you are looking for. Are there any other ways of making navigation of a query plan easier?
Using Solutions and Projects to manage your SQL Server code 11/1/2007	When developing code for a project there is often more than one component that you need to work with for the entire project. The project may include new tables, table changes, new stored procedures, changes to stored procedures, etc... Keeping all of these components straight as well as logically grouped together is sometimes a challenge in itself. Are there are any built-in tools that allow you better manage project components vs. one big file?
SQL Server 2000 to 2005 Crosswalk - Code Identification 10/31/2007	When troubleshooting a potential SQL Server performance problem, it is difficult to know if the code is problematic without being able to review all of the code. You could ask the Developer for the code you suspect, run Profiler to capture code, leverage a third party tool for the data collection or try to leverage the native T-SQL commands to review the code. Historically tracking down the code was available with sp_who2, DBCC INPUTBUFFER and fn_get_sql. With the introduction of SQL Server 2005, is a simpler means with more bells and whistles available with the dynamic management views and functions?
Clearing Cache for Valid Performance Testing 10/26/2007	When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem. To determine the best overall solution, each option is tested and the results are recorded. As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves. Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison. How can I ensure during each execution of a new set of code that the data is not cached?
SQL Server 2000 to 2005 Crosswalk - Locking Resources 10/25/2007	A common problem in many environments is locking and blocking. Locking and blocking can cause performance problems and a poor user experience. If this problem worsens, it can be escalated to deadlocking. At a high level, these two problems are due to multiple processes trying to access or modify the same data and SQL Server's lock manager ensuring data integrity. This problem can be corrected by a number of techniques which may include database design, consistent data access in stored procedures, shortening the transaction length, issuing dirty reads, etc. What's really necessary is taking the first step to determine where the locking is occurring. With all of the changes from SQL Server 2000 to 2005, what is the best way to find out what locks are being issued and by whom in SQL Server 2005?
Optimize Parameter Driven Queries with the OPTIMIZE FOR Hint 10/18/2007	SQL Server doesn't always select the best execution plan for your queries and thankfully there are several different hints that can be used to force SQL Server into using one execution plan over another. One issue that you may be faced with is when using parameters in your WHERE clause, sometimes the query runs great and other times it runs really slow. I recently had a situation where the hard coded values in the WHERE clause worked great, but when I changed the values to parameters and used the exact same values for the parameters the execution plan drastically changed and the overall time it took to run the query increased by about 5 times. This situation is referred to as parameter sniffing where SQL Server stores the values used as part of the execution plan and therefore other queries with different values may act totally different. So what options are there to get around this.
Execute Complex Search Queries with Ranked Results 10/17/2007	Just like every other company, we need to have our queries run as fast as possible for our users to have the best experience possible with our application. One of the core components of our application is to search across a number of columns in a table. We have been trying to accomplish this task with a variety of T-SQL options and nothing has been very quick. To add fuel to the fire, we need to rank the data so that the most relevant data is sorted from top to bottom. I have seen some of your recent tips related to Full Text Search. Can this technology help me achieve high performance searching while ranking the data?
WARNING: SQL Server Deprecated Features 10/16/2007	Should I really be concerned about the deprecated features\commands that I use in SQL Server? In earlier versions, I have heard that commands will no longer be available and I have seen references in documentation. Thus far my scripts always seem to work with the new versions of SQL Server. Recently, I have heard rumors that Microsoft is no longer going to support the particular features once they are designated as deprecated features. So is Microsoft really going to deprecate the features and am I really going to have modify all of my scripts?
Updates for SQL Server 2005 Books Online 10/15/2007	In a recent tip (Applying SQL Server Service Packs) you have talked about updates to the server side components of SQL Server as well as the client side components. One item that I have had an issue with is related to updates for SQL Server 2005 Books Online. Can you please confirm that this portion of the product is updated or not as a portion of those processes? I am concerned that I have outdated Books Online and do not even know it.
Project Management for DBAs and Developers 10/12/2007	When most DBAs and Developers hear the term 'project management', I am sure this is not a term that makes them jump for joy. Those emotions are probably from past experiences where they have had issues. To me "past performance does not always dictate future results" when it comes to a SQL Server DBA\Developer who wants to make a difference in their organization. DBAs\Developers are in a unique position in the IT department and overall organization because in many circumstances they work with Network Admins, SAN Admins, Reporting Groups, users, etc, which is not always the case with other groups in the organization. As a DBA\Developer you can consider yourself the hub that connects many spokes in the organization. As such, as DBAs\Developers we have the ability work from the bottom up to make changes which can have a ripple affect across the organization. You make ask how is that possible, I am totally overwhelmed and I need to keep the boat afloat, but I want to be working on the latest and greatest technologies.
Determining Free Space Per Database 10/11/2007	On all of my databases, I cap the growth of the data and log files. I have not done my due diligence and performed capacity planning (Capacity Planning for SQL Server 2000 Database Storage), so I know I need to take those steps. I have been recently caught with a full database and I had to rush to expand it to keep the application up and running. Independent of the capacity planning process, I want to be able to monitor the database size and free space. Do you have a script that I can run to perform the checks on my SQL Server 2005 databases?
Automate SQL Server Testing with Profiler, SQLCMD and SQL Server Integration Services 10/8/2007	One of the Edgewood Solutions team members recently spoke at a testing conference held in Washington, DC by the name of Verify. During the presentation, a few different questions were asked about automating database testing with the native SQL Server 2005 tool set. In this tip, we will outline some options to automate database testing and verification with Profiler, SQLCMD and SQL Server Integration Services.
Finding Text within Text with the LIKE Operator / Pros and Cons 10/8/2007	One problem that you may be faced with is the need to find text data that is contained in a larger set of text. There are two ways this can be done either using the LIKE operator or by using Full Text indexing. Let's take a look at some of the options of using the LIKE operator and some pros and cons of this approach.
Using Views to Simplify Data Access 10/5/2007	One challenge that just about everyone is faced with is ever changing database schemas. From the onset of a project the database schema might be perfect on day one, but as the application evolves and the business needs change database table structures have to change. In addition, as database become more and more complex there is often the challenge of having to join several tables together on an ongoing basis which is time consuming and also creates the possibility of mistakes. So what other options are there to ensure your applications do not break when your database schema changes and what is an easier way to handle multi-join queries that are used over and over again?
CLR Functions - Getting started with a string sort function 10/4/2007	With the introduction of SQL Server 2005, Microsoft released the Common Language Runtime (CLR) to allow developers and DBAs to take advantage of managed code outside of SQL Server. The original thought when this was announced was this was going to be a bad thing, because people that knew how to develop in a .NET language, but not T-SQL, would adopt this across the board. Over the past couple of years the opposite has happened. T-SQL still continues to be the primary language that is used and I have seen very few implementations where the CLR is being used. In this tip we will take a look at a simple example of where the CLR can come in handy and what a big improvement it can make on certain tasks.
Learning SQL Server 2008 10/3/2007	I have been hearing all of the buzz about SQL Server 2008 from many of your recent tips (SQL Server 2008 - Features, Function and Value) and your web cast (SQL Server 2008 - First Look). I did not get a good jump on learning SQL Server 2005 and we have been slow to migrate to SQL Server 2005 because of this and a few other reasons. I really want to get on the bandwagon and try to adopt SQL Server 2008 earlier and maybe even upgrade some of our legacy applications directly to SQL Server 2008. Do you have any recommendations on going down that path?
Full Text Search Querying Alternatives 10/2/2007	In one of your recent tips, you outlined setting up a SQL Server 2005 Full Text Catalog and some basic queries. Can you outline some of the Full Text Search querying alternatives? I thought your first tip (Making the case for Full Text Search) was beneficial to get started, now I think I want to take the next step with Full Text Search to see if it can functionally meet some application needs that we have been struggling with for some time.
Index Maintenance Checklist 9/27/2007	In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The third item on the list was to maintain indexes. There have been several tips on the MSSQLTips.com website about index maintenance, but we will take a look at a checklist of things to perform to ensure your indexes are not degrading performance on your system.
Finding process percentage complete with dynamic management views 9/26/2007	Some tasks that are run in SQL Sever take a long time to run and it is sometimes difficult to tell whether these tasks are progressing or not. One common way of determining that status is to look at the data returned from sp_who2 or sp_lock to ensure that things are still working and the process is not hung. With SQL Server 2005 several dynamic management views have been added, so let's take a look at some of these and how they can assist.
Making the case for Full Text Search 9/18/2007	I have heard about Full Text Search and I know it has been around for the last few versions of SQL Server. I have never really used it and I have seen some of the other tips related to the technology on your web site, so I assume someone must be using it. Can you provide any use cases for Full Text Search? I think I understand the general premise, but not sure if it is any quicker than just using a LIKE statement or how I would need to change my queries.
Streamlining SQL Server Documentation 9/14/2007	I work for a company that has auditing requirements and with those auditing requirements comes the need to document the environment, specific processes and policies in our organization. I read your Defining and Establishing SQL Server Policies and Procedures tip and found that it was a help with the policies that we needed to have for the audit. Although document is not a very exciting topic, it is something that I need to do for my job and for my organization to meet compliance. If we do not meet compliance, our organization can have some problems, problems that I would like to avoid. Can you provide me with some suggestions on how to document our SQL Server environment? I am having a hard time just getting the information down on paper, so I need some help from the start. Thanks in advance!
Comparing Multiple Datasets with the INTERSECT and EXCEPT operatorsv 9/11/2007	When joining multiple datasets you have always had the ability to use the UNION and UNION ALL operator to allow you to pull a distinct result set (union) or a complete result set (union all). These are very helpful commands when you need to pull data from different tables and show the results as one unified distinct result set. On the opposite side of this it would be helpful to only show a result set where both sets of data match or only where data exists in one of the tables and not the other. This could be done with using different join types, but what other options does SQL Server offer?
Applying SQL Server Service Packs 9/10/2007	As with most applications there are hot fixes, updates and/or service packs that are often released. Some of these updates are automatic while others require you to take action and apply the update. SQL Server is one of those applications that requires you to take action versus having the updates automatically applied. This is a good thing, but it requires the DBA to take some type of action. In addition, applying the service packs to the database engine is pretty much understood, but did you also know that these updates also need to be applied on the clients as well?
Maintenance task to delete old backup files 9/6/2007	In two previous tips we discussed how to automate full backups and transaction log backups by creating scripts that iterate through each of your databases and then execute the backup commands. A reader requested information about how to automate the process of deleting older backup files, so this tip explains one approach for getting rid of older backup files that are generated.
SSIS: Merging multiple sources with the MERGE JOIN task 9/5/2007	When loading data into SQL Server you have the option of using SQL Server Integration Services to handle more complex loading and data transforms then just doing a straight load such as using BCP. One problem that you may be faced with is that data is given to you in multiple files such as sales and sales orders, but the loading process requires you to join these flat files during the load instead of doing a preload and then later merging the data. What options exist and how can this be done?
T-SQL - Parsing a URL 8/31/2007	In my environment I have a few tables with thousands of rows that have URLs in particular columns. We are going through some changes in the environment and need to be able to parse out the data (base URL, file name, query parameters) from the specific columns then move the parsed data to another database design. Moving the data is no problem. I plan on using SQL Server 2005 Integration Services and I have a good sense of how to use that tool. By the way, all of the URLs are in varchar fields if that makes any difference. Can you provide any recommendations on how to parse that data?
SQL Server Consolidation - Pros and Cons 8/30/2007	In the recent years, there have been a few different trends related to the hardware platform for SQL Server. One trend was to have a dedicated SQL Server for each application. This trend has been countered in some organizations by a major consolidation effort. The consolidation effort in some circumstances consists of consolidating the hardware and storage to a unified set of devices although the same number of logical machines exists. In other circumstances, the consolidation is for the hardware, storage, Windows and SQL Server instances. Both alternatives have advantages and disadvantages, so what is the correct path? What should organizations follow? When considering migrating to SQL Server 2005 or 2008, should a consolidation effort be considered as well if we have a large number of SQL Servers are in scope for the project?
Automating Transaction Log Backups for All Databases 8/28/2007	Maintenance plans are a great thing, but sometimes the end results are not what you expect. The whole idea behind maintenance plans is to simplify repetitive maintenance tasks without you having to write any additional code. For the most part maintenance plans work without a problem, but every once in awhile things do not go as planned. Two of the biggest uses of maintenance plans are issuing full backups and transaction log backups. What other approaches are there to issue transaction log backups for all databases without using a maintenance plan?
Keeping data available in the data cache with PINTABLE 8/27/2007	Have you ever wondered why even after optimizing a database that it still takes considerable time to fetch the results? The problem is whenever a query is fired SQL Server fetches the data from the database by means of bringing the data pages into memory. Depending on the database activity the data you may be using quite frequently may be getting paged in and out of memory which may account for why sometimes it takes longer then other times to fetch the data. This paging is done automatically, but if you feel that a table is being used very frequently then this is where the DBCC PINTABLE command may come in handy.
SSIS - How to strip out double quotes from import file 8/24/2007	When loading data using SQL Server Integration Services (SSIS) I am importing data from a CSV file. Every single one of the columns in the CSV file has double quotes around the data. When using the Data Flow Task to import the data I have double quotes around all of the imported data. How can I import the data and remove the double quotes?
Hard Drive Configurations for SQL Server 8/22/2007	At my company we are looking at purchasing a new SQL Server and I am not sure how many disk drives we should purchase. We do not have an unlimited budget because we are a small company, but I am interested in how we should configured disk drives for SQL Server if we bought 1, 2, 4, etc disk drives. I also am interested in the rationale behind the decisions. Can you shed any light on the subject?
Checklist - Selecting a SQL Server Product 8/17/2007	I have seen the recent addition of SQL Server product listings on your web site and see the value in being able to find all of the SQL Server products per category in one place. Going to your web site rather than having to search all over the Internet to find the products myself is a big time saver. Now that I know where the products are located, how should I go about evaluating them? I know I only have a limited amount of time to take a look at the products and need to select the correct one for our environment. I really do not want to get bugged by all of the sales people at these companies, but I do want to take a look at their products. So how should I go about evaluating products in the SQL Server market?
Are you a 'typer' or a 'clicker'? 8/16/2007	Over the last few months I have heard the terms 'typer' and 'clicker' related to how SQL Server DBAs manage SQL Server. Working with one specific customer, the customer came right out and asked me 'if I was a typer or a clicker'. I was surprised to hear that question, but I understood why they asked it as they observed me work. They noticed all of the T-SQL commands that I issued as opposed to using Enterprise Manager. They were also surprised to see how many SQL Server system tables that I accessed and the information that I gathered to troubleshoot the issue. This same distinction between DBA management preferences became crystal clear to me during a recent Northern Virginia SQL Server User Group session. As the group was having a discussion, I was surprised to see hear how how many people provided input based on clicks in the GUI (Management Studio or Enterprise Manager) versus issuing T-SQL commands. So is managing SQL Server via the GUI or T-SQL commands better than the other approach? Is there information that is only available in one technique and not the other?
Web Based Database Administration for SQL Server 8/15/2007	I am working at an organization where I need to have access to my SQL Servers around the clock. I do not have a company laptop to check into the environment and troubleshoot issues directly so I am looking for alternatives to meet the need. We are on a tight budget so I wanted to find out what native tools are available. Do you have any suggestions for SQL Server tools to meet this 24X7 management need?
Retrieving random data from SQL Server with TABLESAMPLE 8/14/2007	In a previous tip we talked about how to randomly retrieve records from the database. This tip took a look at both the RAND() function and the NEWID() function. Both of these approaches are helpful, but one problem with both of these is that depending on how you use them you may end up reading through your entire table to get a random distribution of data. The good part about this is that you are pretty much guaranteed that you will get a totally random set of records each time. Because of the potential overhead with reading through the entire table are there any other approaches to randomly retrieving data?
Simple way to find errors in SQL Server error log 8/13/2007	When managing SQL Server there are so many different places to look for data. These include the error logs, system event logs, profiler data, performance counter data, etc... Once you have collected the data you then need to parse through and interpret the data you collected. One of these areas where errors and other informational data is stored is the SQL Server error log. The problem with the error log file is that there is so much data collected it is sometimes hard to determine where the real errors lie. By default all backups and integrity checks are logged in the error log. In addition, if you are auditing logins these messages are also stored in the error log, so this further compounds the problem. It is great to have all of this data, but trying to find your problems can become quite a chore. So how can you find the errors much easier?
Defining and Establishing SQL Server Policies and Procedures 8/9/2007	As your IT team grows more and more people may have direct impact on your SQL Server databases. At the onset it may have been just a couple of developers and a DBA, but as your company becomes more successful and more resources are put on developing, maintaining and supporting a database driven application the potential for inconsistent processes and undocumented changes increases. Just when you need more control and structure things tend to sway the other way, because now there are more people and components that need to be managed. So what is the best process to put controls in now so when things grow beyond one or two people chaos does not follow?
SQL Server Code Review Checklist 8/7/2007	In a recent tip, you outlined steps for code deployment (Code Deployment Best Practices). That is a good first step to get a process in place to work with the team and set expectations. As a DBA, I need to find out the technical steps I should take when reviewing T-SQL code. Can you provide me with a checklist or recommendations to work through this process?
Code Deployment Best Practices 8/3/2007	I am the only SQL Server DBA at my organization and I feel like I am constantly pushing out code. I have Developers send me emails, I get help desk tickets, I have to go out to file shares and VSS to check for code that needs to get pushed out. I have been at a point where I am not able to get my other work done which is becoming frustrating. The other issue that I have is that I am unable to track the changes over time. With 4 different places that I need to check for code, this has become a nightmare that I have to get in control and soon. How should I go about doing this?
Hardware 101 for DBAs 8/2/2007	I am a SQL Server DBA and I am very comfortable with the design and development side of SQL Server. I have been complimented by my own team on many of my database designs as well as on much of the behind the scenes development that I have done in SQL Server. Unfortunately, the tides have turned a little bit at work and we now have a stronger focus on the infrastructure components with some new people on the team. I know the basics about hardware, but I get lost on some of the newer terms that some of the new folks bring up. Can you help define some of the key hardware related items and the significance in SQL Server?
Building SQL Server Indexes in Ascending vs Descending Order 7/30/2007	When building indexes often the default options are used to create an index which creates the index in ascending order. This is usually the most logical way of creating an index, so the newest data or smallest value is at the top and the oldest or biggest value is at the end. Although searching an index works great by creating an index this way, but have you ever thought about the need to always return the most recent data first and ways you can create an index in descending order so the most recent data is always at the top of the index? Let's take a look at how this works and the advantages of creating an index in descending vs ascending order.
Database Integrity Checks Checklist 7/27/2007	In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The second item on the list was to check database and table integrity. This is pretty much a simple task to perform and there is not much if any interaction that needs to take place between the DBA and SQL Server. Although this is pretty straightforward to setup and run, this is not always implemented and secondly the output is seldom reviewed to see if there are any issues. This tip will address some of the items you should consider when putting your integrity check process in place.
Listing SQL Server Object Dependencies 7/26/2007	When developing components for SQL Server one change may affect another database object. Finding these dependent objects should be straightforward, but in most cases it is not as easy as you would think. So what is the best way to find dependency information in SQL Server?
Database Restore Verification 7/24/2007	I have over night processes on a few different SQL Servers in my environment that are backup and restore related. I have used the scripts on MSSQLTips.com to check if the SQL Server Agent Jobs\Job Steps have run as well as the backup verification code. Some of the backup and restore processes I have written and others I have inherited. Some of the processes seem to be very reliable and others are not. I need a way to validate the restore processes have completed properly just like the SQL Server Agent Job and backup code. Can you provide this script so I can include it in my daily verification process?
If it's on the Internet, it must be true 7/23/2007	Among many other things, the Internet is a great resource and has revolutionized many things including troubleshooting SQL Server issues. Based on my observations, more often than not, when a SQL Server issue occurs DBAs, Developers or Network Administrators will ask their team if they have seen the issue they are experiencing and for the resolution. Depending on the answer, the next resource that is tapped is their favorite search engine i.e. Google. After a few different keyword searches, you may have your answer in a matter of minutes as compared to combing through book after book to find an answer. So once you have this information what should you really do?
SQL Server Best Practices Analyzer (Tip 1 of 2) 7/20/2007	We have a number of SQL Server 2000 instances in our environment. I am not sure if they have been consistently deployed or not. Further, I am not sure if the SQL Server 2000 instances are following best practices or not. How can I get some feedback on my SQL Server 2000 environment to shed some light on the situation?
Assigning shortcuts to commonly run tasks 7/17/2007	The SQL Server management tools offer a lot of hidden features, but finding all of these features is sometimes done by accident or by necessity to get a particular job done. One particular feature that would be helpful is to assign shortcuts for particular stored procedures that you run quite frequently. By default SQL Server offers default shortcuts such as Alt+F1 (sp_help), Ctrl+1 (sp_who) and Ctrl+2 (sp_lock). How can you assign your own frequently used stored procedures to shortcut keys?
Modifying tables and keeping like columns together 7/13/2007	When designing tables you may not always know what columns need to exist prior to when the table is created. Therefore over time table changes are made where you may add or drop columns. Based on this table modification you may want to have certain columns next to each other for easier management and grouping of like data in the table structure. It doesn't really matter to SQL Server where the column is located, but to us humans it sometimes makes a difference. Dropping a column is not a big deal because the column just goes away, but when adding a column the default process is to add the column to the end of the table. So how can you modify the table structure so all of the like columns are next to each other?
Backup Checklist 7/12/2007	In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The first item on the list was to address and implement a sound backup strategy. This is probably one of the easiest things to take care of, but often the wrong options are selected or the wrong or no backup plan is put in place. This tip will address some of the items you should consider when putting your backup process in place.
Assigning DBA Rights in SQL Server 7/10/2007	As a DBA you have the responsibility to ensure the security for the application and users related to the SQL Servers that you manage. But what are the security principals that you follow for the administrators? Are there any hard and fast rules that must be followed? What concerns should be taken into consideration when the rights are assigned to DBAs? How is this any different for a large organization versus a small team where many of the team members wear numerous hats (DBA, Developer, Network Admin, System Admin, etc.)?
DBA database management checklist 7/3/2007	For both old and new DBAs there are fundamental procedures that should be addressed and proper processes put in place to handle various areas of database management for SQL Server. Whether you are a full time DBA or this is one of many job roles that you perform the same basic steps should be implemented and adhered to in order to have some peace of mind that you are performing the correct procedures to ensure a healthy running SQL Server environment. For old DBAs these items should be a no-brainer, but often a refresher is good reminder to make sure everything is in place. For new DBAs some of these basic items are not all that apparent and often some of the most basic DBA 101 items are sometimes overlooked. So based on this, what is a good plan to implement to make sure the basic SQL Server DBA items are being addressed?
Retrieving SQL Server Column Properties with COLUMNPROPERTY 6/28/2007	SQL Server stores a lot of data about your database objects in various places and in various formats. When pulling data about table columns this data can be pulled directly from the syscolumns table. Some of this information is useful as it is, but some of the data needs to be interpreted to understand. In addition to pulling data directly from syscolumns you can also use the information schema view. If you query from INFORMATION_SCHEMA.COLUMNS you get a lot of data in a useful format, but there is still some missing data. So how can you retrieve additional data about column level properties?
Changing Not For Replication Value for Identity Columns 6/27/2007	When setting up replication there are many things to think about and many options you can choose from when setting up your publications and subscriptions. In most cases replication is an after thought and not part of the original database or application design, so there may be some required changes that need to be done when replication is setup. The most important part is that the table has a primary key. All tables should have a primary key when they are created, but sometimes this is not addressed and for replication to work this needs to be setup. In addition, if you are using merge replication you need to have a RowGuid column. Also, if you use identity columns you need to make sure the not for replication parameter is turned on.
SQL Server Agent Job Ownership 6/26/2007	I am making a number of SQL Server security changes at the server and database level all at once, which is a problem in and of itself. I will be changing SQL Server service accounts, removing rights to BUILTIN\Administrators group, limiting specific user rights, moving to group based security, changing SQL Server Agent Job owners, etc.
SQL Server Best Practices Analyzer (Tip 2 of 2) 6/26/2007	With SQL Server 2000, I had a good feel for many of the best practices, from an administration, development and configuration perspective. When deploying SQL Server 2005 some of those same principals will apply. But I'm sure with the vast amount of change in SQL Server 2005 that there are some new rules as well. How can I validate my SQL Server 2005 environment is properly configured? Are there really absolute rules when configuring a SQL Server 2005 instance?
File Validation with xp_fileexist 6/25/2007	In a recent tip (Accessing the Windows File System from SQL Server) options were outlined to capture files from a specific directory into a temporary table or table variable for additional processing. A similar file operations task that is needed in particular T-SQL code is to validate a specific file exists or not. Then logic can be written to either process the file, retry at a specific interval or fail the process. As such, how can this be accomplished and can you provide me with some concrete examples in SQL Server 2000 and 2005?
Transactional Replication Snapshot Issues 6/21/2007	SQL Server replication has been around for quite some time. I think just about every DBA has setup replication in some kind of test environment if not fully deployed this in a production environment. One of the issues that you are faced with when setting up transactional replication is getting your subscribers all of the initial data prior to replicating new transactions. There are various ways this can be done, but probably the most used technique is using the snapshot process.
Missing SQL Server Agent History 6/20/2007	SQL Server Agent is a useful tool to allow you to schedule reoccurring jobs to run on your server. Most of these jobs are usually maintenance type jobs or data loads, but this tool can be used to run any T-SQL code or even execute programs external to SQL Server. In addition to the ability to schedule jobs to run, SQL Server Agent also gives you the ability to see historical run information for the jobs as well as for each step within the jobs. This information is very helpful to have on hand for knowing if the job succeeded as well as how long the job took to run. This way the next time you need to run one of these jobs you have an idea of how long it will take to complete. If you have jobs that run on a frequent basis or if you have a lot of jobs that are scheduled you may notice that there is not much historical information stored when you view job history, so what can be done to keep more historical run information?
Memory Allocation for SQL Server 2000 Enterprise Edition 6/19/2007	I have a Windows 2003 Enterprise Edition server with SQL Server 2000 Enterprise Edition. The machine has 10 GB of memory, but I am not confident that the machine is using all of the memory that I have configured from an Enterprise Manager perspective. How can I validate how much memory SQL Server is using? I have a sneaking suspicion that only a small percentage of the memory is used, but I am not 100% sure. If the SQL Server is not using all of the memory, how can I have SQL Server access the 10 GB of memory or is that too much?
Capacity Planning for SQL Server 2000 Database Storage 6/18/2007	I am running very low on storage to support my SQL Server 2000 user defined databases on a number of my production SQL Server instances. We have made the decision to invest in some sort of NAS\SAN or direct attached storage solution. Independent of which technology, vendor and RAID sets we select, I need to plan for my database storage for the next 18 to 24 months and beyond. I have seen your script for capacity planning at a table level (Easing the Capacity Planning Burden), but how can I roll this up at a database level? I need to determine the storage needs at a database and SQL Server instance.
Identify All Tables with Columns of a BLOB Data Type 6/15/2007	After some recent SQL Server performance tuning, I noticed that one of my large core tables (important) has a column with a text data type. I had no idea that this table had a BLOB (binary large object) data type. After running some quick queries on the table\column and doing some quick analysis I have found that none of the entries exceed a few hundred characters. After talking to the development team, they indicated that a 500 character limit is enforced on the front end. With this being said, I clearly do not need a column with the text data type and could use a varchar or nvarchar (Unicode) column with a width of 500. Since I was not aware of this particular BLOB column in one of my large core tables (important), I suspect some other tables in my environment have column text, image, ntext, etc data types. How can I easily find these columns on a per table basis without having to review them in SQL Server Enterprise Manager or SQL Server Management Studio?
SQL Server Database Specific Performance Counters 6/14/2007	Both SQL Server and Windows offer a lot of data to help troubleshoot and monitor overall usage and performance for your SQL Server databases. Within SQL Server there are several DBCC commands as well as a lot newly exposed data from the Dynamic Management Views in SQL Server 2005.
Capturing Graphical Query Plans with Profiler 6/13/2007	One of the primary things that any DBA or developer will do when tuning their SQL statements is to look at the query plan or execution plan. This can be done in both a graphical tree format as well as a text based format. For most people this information is confusing at best and does not explicitly tell you what the issue is and how to fix the problem. For this reason, people have probably become more use to using the graphical plan output versus the text based output.
Accessing the Windows File System from SQL Server 6/12/2007	Within my SQL Server T-SQL stored procedures and scripts I need to find out the files in a specific directory. What are some approaches to do so? I need some flexibility to capture the file names and general information for subsequent processing. What are the native SQL Server options as well as the custom options that are available?
Error - Windows cannot access the specified device, path or file. 6/11/2007	Every time I try to execute an executable (*.exe) file that I copied from a Windows 2003 File Server to my SQL Server running Windows 2003, I am unable to access the file. It works without a problem on the original Windows 2003 File Server and I am able to validate that nothing has changed. I just keep getting this error message - Windows cannot access the specified device, path or file. How can I resolve this issue? What am I doing wrong? I am just copying a file to my SQL Server.
Database Mail Install Hangs 6/8/2007	In SQL Server 2005, Microsoft introduced Database Mail the replacement for SQL Mail. When setting up Database Mail a few options need to be set including enabling Database Mail, setting up profiles and also making sure that Service Broker is enabled. Most of these options can be configured on the fly by just selecting "Configure Database Mail", but when running the install and selecting "Yes" to activate Service Broker for "msdb" the installation just hangs and the install never completes.
Script - Recompile All SQL Server Database Objects 6/7/2007	In some of your tips you reference sp_recompile as a stored procedure that needs to be executed during some maintenance related processes. After doing some basic research it looks like I can execute sp_recompile against a particular stored procedure or against a table so that all objects, typically stored procedures, that reference that object can be recompiled. I know that sp_updatestats exists to update statistics against all objects in the database. Does a similar stored procedure exist for sp_recompile?
SQL Server 2008 has arrived 6/6/2007	Too many changes, not enough time. Here we go again, another new version of SQL Server ready to be launched sometime in the near future. Microsoft has officially named the next version of SQL Server as SQL Server 2008, so based on the official name I would assume we will see the RTM release sometime in 2008. This release was previously known as "Katmai", but this week Microsoft has released the first CTP version available for download. As far as the clients that I work with, most are still running SQL Server 2000, so does this potentially mean a double jump like a lot of people took from SQL Server 6.5 to SQL Server 2000?
DING!!! DING!!! DING!!! Disaster Recovery Fire Drill 6/5/2007	One old adage that has stood the test of time is "you will never know until you try". I would have to say that is the case with a disaster recovery plan. I would be surprised to hear any company relying on IT does not have a disaster recovery plan. Some are probably more formal than others, but in the middle of many of those plans are SQL Servers and the need to recover them quickly to maintain the business operations. But have you put your plan to the test? What about when key people are out of town? Or when you do not have direct access to your SQL Servers or network? Does your DR Plan fall into place or does it fall like a house of cards?
Getting IO and time stats for your queries 5/31/2007	If you're like any other DBA or developer in the world, writing sound T-SQL code and performance tuning is one of your primary duties. There are many tools, both native to SQL Server and third-party, that assist you in this endeavor. But in thinking about writing and tuning queries, what should the focus be? For many, the length of time taken to execute the query is "good enough". One focus should be on the resources used by the server, since the length of time taken to execute the query can vary based on other server activity. In addition to using Profiler and Execution Plans, consider using SET STATISTICS IO and SET STATISTICS TIME.
SQL Server Documentation Tools 5/24/2007	Maintaining documentation about your SQL Server databases is a big task. No matter how many developers or DBAs you may have, keeping documentation current is a challenge. In most cases you only need view a portion of the database defined in an easy to view format, but if this is something you don't keep up with on a constant basis your efforts will never yield up to the minute documentation. In addition, SQL Server stores all kinds of meta data about your database objects, so it makes sense to utilize this data since it is easily accessible. You could take the time to right queries and reports, but why bother when there are so many documentation products already available.
Do you really know if your SQL Server database backups are successful? 5/23/2007	Are you absolutely sure? Are you? After hearing a presentation from Microsoft's Paul Randall, at a recent Northern Virginia SQL Server Users Group session, I wondered how many DBAs\Developers\Network Admins realize their SQL Server database backups are failing. According to Paul they find out when it is too late - when they are experiencing corruption and facing lost data after significant amounts of downtime. Both of which are detrimental for the success of the organization and inexcusable for DBAs\Developers\Network Admins responsible for SQL Server Service Level Agreements (SLAs).
Encrypt and safeguard your backups 5/22/2007	The SQL Server backup process allows you to perform full, differential, transaction log and file level backups. The problem with all of these backups methods is that the data that is created in the backup is stored in clear text and can easily be comprised if someone cares to take the time to hack through these backup files. In addition, SQL Server makes it very easy for you to move a database from one server to another server by restoring a complete backup or by having the MDF and LDF files and using the attach functionality. So what are the best practices to combat this?
Options and Analysis - Exclusive Database Access 5/21/2007	In your earlier tip (Die Sucker - Killing SQL Server Process Ids (spids)) you outlined an option to kill spids (system process ids) to perform database restores or database maintenance when exclusive use of the database is needed. Do any other options exist? What are the advantages and disadvantages to these approaches? When should one approach be used over another?
Die Sucker - Killing SQL Server Process Ids (spids) 5/18/2007	I have noticed some of my processes are failing because spids are already connected to the database. This happens specifically when I need to a restore database. I catch this problem pretty quick when I am working on it during the data and can fix it, but during nightly processing existing spids become problematic. I have also noticed existing spids causing problems for my SQL Server 2000 Database Maintenance Plans. I have found this issue in my logs specifically related to performing integrity checks (DBCC CHECKDB ('YourDatabaseName') REPAIR_FAST) when the database needs to be in single user mode before the integrity check commands run. How can I kill these spids prior to running my processes?
Migrating a SQL Server Instance 5/17/2007	I need to migrate an instance of SQL Server 2000 due to a hardware issue that I am having. What are the steps that I should take before, during and after the process? What pit falls should I watch out for?
Build code using object browser's drag and drop capabilities 5/16/2007	Trying to remember all of the object names, parameters and columns within tables becomes quite daunting for very large data models. This is where GUI tools such as Enterprise Manager and SQL Server Management Studio provided a big boost to the database market for SQL Server. The ability to do most of your work through a graphical interface versus having to do everything command line makes users much more productive and some time consuming tasks can be done with a point and click. There are several areas where these graphical tools have made a big impact on who is and who can use SQL Server. One feature that is often difficult is writing T-SQL statements for your databases. Trying to remember the exact names of the tables, the columns, the stored procedure parameters is not always that easy. So what can be done to ease this process?
Accessing SQL Server Agent Data 5/15/2007	SQL Server Agent has a lot of great information stored in the system tables and the GUI provides a nice way of retrieving and viewing the data, but it does not always give you the data the way you would like it displayed. From a high level it is nice to see the current status of all jobs and the last run status of the jobs, but what if you want to see additional information or the data displayed in a different format. On a one by one basis you can get this information from the GUI, but it is not very easy to correlate this data across jobs or even across different time periods.
Auto generate database restore scripts 5/14/2007	When a failure occurs you need to act quickly to possibly restore your database. When you are performing both full and transaction log backups there are multiple files that will need to be restored and therefore your restore script could get quite long and tedious to write. When using Enterprise Manager or SQL Server Management Studio the GUI gives you the list of files that should be restored, but what if you need to do this manually or you would rather have a script to perform the restore process instead of using the GUI. How can you easily generate the restore script?
Finding and fixing database constraint issues 5/11/2007	Adding constraints such as check constraints or foreign keys to a table are best practices to keep your data as clean as possible with minimal data enforcement rules performed at the database level. Unfortunately sometimes issues may occur where the data becomes out of synch and one of these constraints has been violated. This may be due to disabled constraints or constraints that are later added with the NOCHECK option. Finding these issues can be done by running queries to check each of the constraints, but is there any easier way to determine if the data the constraints support has been violated?
Preventing T-SQL code from running on a Production SQL Server 5/10/2007	I have T-SQL code that I cannot have run on my production SQL Server. How can I be sure that it does not run in production by mistake from a programmatic perspective rather than from a process perspective? The reason I ask is because if the code ran in production, it would take us hours to get back up and running because we would need to go to our backup and then perform a number of restores. The code that I am concerned about is responsible for purging particular data. Although it is needed across our databases due to internal business requirements, I need a way to protect our production environment from this code being executed by mistake.
SQL Server DBA Checklist 5/9/2007	I am looking for items that I should address on a daily basis on my SQL Servers. As such, what are the critical aspects of SQL Server that should I check on a daily basis? Should I perform additional checks on a weekly, monthly or yearly basis? How can I automate some of these tasks so I do not spend my whole day reviewing SQL Servers rather working on the latest and greatest technologies?
How to get index usage information 5/8/2007	Databases have two primary storage needs; data pages and index pages. Understanding and viewing the actual data in your tables is pretty straightforward by running some sample queries to get an idea of what columns are being used as well as what type of data is actually being stored. On the flip side of this, it is often difficult to know exactly what indexes are being used and how they are being used. So how can you get a better understanding of how your indexes are being used and what operations are occurring (inserts, updates, deletes, selects)?
Debate - The Value of the MCDBA\MCITP:DBA Certification 5/7/2007	At a recent Northern Virginia SQL Server user group meeting, a debate was held about the Microsoft Certified Database Administrator\Microsoft Certified Information Technology Professional : Database Administrator being a 'must-have' for all SQL Server DBAs. As such, let's explore some key questions. Is it worth studying for the MCDBA\MCITP:DBA exams or not? Is this time well spent? How time consuming will the studying be to pass the tests? Is the MCDBA\MCITP:DBA just about studying for and passing tests? Will the MCDBA\MCITP:DBA help DBAs get a job or a raise?
Finding primary keys and missing primary keys 5/4/2007	One design aspect that all tables should have is a primary key. The primary key is the main entry way into your dataset, so that when you access your data you are guaranteed to only affect one row of data. Having primary keys are not only a good design feature they also play an important role in replication and data updates especially when there may be duplicate rows of data. So how can you determine what tables have primary keys and what tables do not have primary keys?
Performance Tip: Avoid functions in WHERE clause 5/3/2007	SQL Server offers many handy functions that can be used either in your SELECT clause or in your WHERE clause. For the most part these functions provide complex coding that would be very difficult to get this same functionality without these functions. In addition to the built in functions you also have the ability to develop your own user defined functions. When functions are used in the SELECT clause to return uppercase output, a substring or whatever, it doesn't affect performance that much, but when functions are used improperly in the WHERE clause these functions can cause major performance issues.
Differential Database Backups for SQL Server 5/2/2007	I know the 'typical' backups are full database backups executed every day. I have looked into transaction log backups and I am not sure if that is something I can support. I have seen some information on differential backups, but I am not sure exactly how they work, how they are different and how they could help me? Could you please explain some of the considerations with differential backups to determine if they would be beneficial to me and my situation?
Capturing Deadlock Information in XML Format 4/30/2007	In your recent tips on SQL Server dead locks (How To: Graphical Deadlock Chain and Deadlock Priority Configuration) I can see the value of using Profiler to capture the process related information. Do any other options exist in Profiler to capture the information is an easier format that I can review? If so, what is the format and how can I begin to take advantages of this configuration in Profiler?
Getting rid of some blocking issues with NOLOCK 4/27/2007	Often many applications utilize the same database and it may be quite difficult to manage locking issues that occur which may lead to significant blocking issues and therefore potential performance issues. In a perfect world this would never be the case and everything would run without issue. Unfortunately locking and blocking are real things that occur regardless of how well you architect your database application, so the name of the game is to minimize this as much as possible. As mentioned already, there may be multiple applications hitting the same database such as a transaction based application and a reporting based application. So what can be done to minimize the blocking issues?
Point in time recovery 4/26/2007	At some point, a detrimental command will probably be issued against one of your databases and you will need to recover the lost data. There are several actions that you might be able to take to recover the lost data, but what option makes the most sense. One option that SQL Server offers is the ability to do point in time restores of your data in order to restore your database back to the point right before that detrimental command was issued. So what are the steps in order to do a point in time recovery?
Retaining SQL Server Job History 4/25/2007	I have a set of SQL Server Jobs (transaction log backups, notification of business rules, etc.) that run every 5 minutes just about 24 hours a day. I seem to lose my SQL Server Job History in a few days and need this type of data to troubleshoot specific situations. It always seems like I lose this SQL Server Job related data when I need it most. I am trying to find the SQL Server Job that someone could have setup to delete from these tables and I only see the Jobs that my team created. I have gone over these with a fine tooth comb and none of these delete data from the SQL Server Job related tables in the MSDB database. What exactly is deleting these entries? Do I need to start backing up the MSDB database a few times a day to troubleshoot this issue?
Notes from the field - Post SQL Server 2000 Service Pack 4 Performance Issues 4/24/2007	My company has recently upgraded to SQL Server 2000 Service Pack 4 and we have experienced significant performance issues. Overall our performance baseline has dropped significantly (more than 50%) immediately after the service pack installation. We need to correct our performance issue, determine the problematic symptoms and the root cause in the service pack. How should we do so?
Clustered Tables vs Heap Tables 4/23/2007	One very important design aspect when creating a new table is the decision to create or not create a clustered index. A table that does not have a clustered index is referred to as a HEAP and a table that has a clustered index is referred to as a clustered table. A clustered table provides a few benefits over a heap such as physically storing the data based on the clustered index, the ability to use the index to find the rows quickly and the ability to reorganize the data by rebuilding the clustered index. Depending on the INSERT, UPDATE and DELETE activity against your tables your physical data can become very fragmented. This fragmentation can lead to wasted space in your database, because of partly full pages as well as the need to read several more pages in order to satisfy the query. So what can be done?
Granting permission with EXECUTE AS 4/20/2007	One area that is often under utilized in SQL Server is issuing granular security rights at both a login and user level. It is often easier to just give someone more rights then they usually need to perform the task at hand versus trying to figure out exactly what rights they need and then issuing just those rights. This is often true where server level roles, such as sysadmin, are granted or database roles, such as db_owner, are granted. By giving logins and users these rights the database user is pretty much guaranteed to have the necessary access that is needed, but granting additional rights could put your data at risk. So what other options are there?
Performance Tips: SET NOCOUNT ON 4/19/2007	One of the biggest things that DBAs try to do on a daily basis is to ensure that their database systems run as fast as possible. As more and more users access the databases and the databases continue to grow, performance slow downs are almost inevitable. Based on this, DBAs and developers should do everything they possibly can to keep performance related issues in mind early in the database lifecycle. This is not always easy to do, because of the unknowns and the changes that occur over time, but there are some simple things that can be done and we will touch upon one of these in this tip.
How to determine transaction log use 4/18/2007	One crucial aspect of all databases is the transaction log. The transaction log is used to write all transactions prior to committing the data to the data file. In some circumstances the transaction logs can get quite large and not knowing what is in the transaction log or how much space is being used can become a problem. So how to you determine how much of the transaction log is being used and what portions are being used?
How To: SQL Server DTS to SSIS Upgrade 4/17/2007	With as many Data Transformation Services (DTS) Packages that have been developed and deployed for B2B, data integration and BI needs, when it comes to upgrading from SQL Server 2000 to 2005, this portion of the upgrade will need special attention.
What exactly is Service Broker? 4/16/2007	have heard some buzz about Service Broker and I have seen the recent tips (Service Broker Infrastructure Objects and Service Broker Troubleshooting) that have piqued my interest. Unfortunately, I am still a little foggy on this technology. In SQL Server 2005 Management Studio, I have browsed some of the folders under my databases and seen some default objects, but not too sure if I get it.
How To: Graphical Deadlock Chain 4/13/2007	I have read your tip on SQL Server locking, blocking and deadlocking (Finding and troubleshooting SQL Server deadlocks) which are applicable to some of issues I have been recently facing. I have been noticed locking on my SQL Server and have heard about the issues from my users. I have been trying to troubleshoot it, unfortunately, I have been having a hard time understanding the issues with large deadlock chains. Does SQL Server have any way of identifying, understanding and trouble shooting deadlocks other than your previous tip (Finding and troubleshooting SQL Server deadlocks)?
SQL Server T-SQL Aggregate Functions 4/12/2007	I have started a new application where I need to start aggregating some of the data and perform some basic mathematical functions. I know I can perform some of the calculations in the front end application, but I am not sure this is the best approach in the long term. How can I do perform the aggregations in my T-SQL code based on some user needs in the OLTP application and in the corresponding reporting application? What aggregate functions are available in SQL Server 2000 and 2005? Can you provide me with some examples on how to do so in my T-SQL code?
SQL Server 2005 Upgrade Considerations for DBAs and Developers 4/11/2007	Making the decision to upgrade to SQL Server 2005 should be one that is made with sufficient information, not haphazardly. Understanding the changes needed to your SQL Server 2000 environment prior to upgrading should be one of the first sets of information gathered to determine the level of effort needed to upgrade. Scanning your code for issues, reviewing your configurations and DTS Packages can be a time consuming process. As such, how can I gather the needed information about my SQL Server 2000 environment to determine how much work I have to complete before upgrading to SQL Server 2005?
Why do my maintenance plan transaction log backups always fail 4/10/2007	One nice feature of SQL Server is the ability to create maintenance plans for all different types of maintenance activities such as backups, index rebuilds, integrity checks etc... One part of the backup process is the ability to create transaction log backups. One thing you may have noticed is that when you set this up and the job runs it always fails. You check the backup files and the backup files are created, so why does this job always say it failed?
Using Bitwise operators to store multiple values in one column 4/9/2007	Sometimes there may be the need to save multiple values into one column instead of creating multiple columns to store these indicators. Let's take for example we need to know who has different telephone types and instead of having multiple indicator columns for each type of telephone number you use one column to store the different values. So for example value 1 = home phone, 2 = mobile phone, 3 = fax, etc... You could have three different bit (yes/no) columns such as homeNumberIndicator, mobileNumberIndicator and faxNumberIndicator, but each time you add a new phone type you need to add a new column to your table. Another way to do this is to store the data in a binary format and use the bitwise (&) operator that SQL Server offers to store all of the data in one column and to also easily tell what values are applicable based on the value stored.
Problems running sp_helpdb 4/6/2007	One useful system stored procedure is sp_helpdb. This stored procedure returns information about all of your databases on your server such as the size, owner, when it was created and the database settings. One issue that you may run into is that the stored procedure does not provide data, but an error occurs instead. The error that you receive is "Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails."
Copy-only backups: Another useful tool in SQL Server 2005 4/5/2007	A new feature added to SQL Server 2005 is the ability to create copy-only backups. The advantage to this type of backup is that it doesn't interfere with your regular scheduled backup sequences, if you need to create another backup file outside of your regular backup processing.
Executing a T-SQL batch multiple times using GO 4/5/2007	Sometimes there is a need to execute the same command or set of commands over and over again. This may be to insert or update test data or it may be to put a load on your server for performance testing. Whatever the need the easiest way to do this is to setup a while loop and execute your code, but in SQL 2005 there is an even easier way to do this.
Commenting SQL Server T-SQL Code 4/4/2007	I do a horrible job of commenting my T-SQL code in SQL Server. I could use a few pointers because I have gone back to change my code and it has taken me forever. How should I go about commenting my T-SQL code so it is simple and helpful? Does any of the functionality differ between SQL Server 2000 and 2005?
Who is logging in as the 'sa' login? 4/3/2007	Not knowing which applications or people on your team that are using the sa login should be a major cause for concern in security conscious organizations. If the password for this standard SQL Server login is known by more than 1 team member, tracking changes committed by the sa login can be challenging. Since, the sa login has rights to execute all commands (highest level of rights in SQL Server) knowing who and what applications that are the sa login should cause concern for organizations that are less security conscious. Regardless of my situation, how can I find out who is using the sa login?
Correlating Performance Monitor and Trace Data 3/30/2007	Just about all SQL Server performance related tips talk about using data from Performance Monitor and data from SQL Profiler. The big draw back has always been that they are two totally separate tools, with different interfaces and therefore you need to figure out a way to correlate the data between the two applications on your own. This was the old way of doing things, but with SQL Server Profiler for SQL 2005 this has changed.
Collecting Performance Statistics for Scheduled Jobs 3/28/2007	One big issue with scheduled jobs is that they are often batch type jobs that take a significant amount of resources when they run. Another problem is that these jobs usually run during off hours such as nights or weekends, so there is no one to monitor the jobs resource usage. Without being around to monitor the job activity it is difficult to determine whether the jobs are running efficiently or not. Also, when batch jobs are first implemented they are often done with less data, so the resource needs are not that great, but over time the database grows and often the load for these batch processes grow. So how can you monitor the jobs resource usage while you are not there?
Deadlock Priority Configuration 3/27/2007	In specific SQL Servers databases, I have deadlock problems that often result in significant performance degradation. When the deadlocks occur frequently in my environment, the deadlocks often become a performance killer for the overall application. If the deadlocks become severe enough, I need to drop everything I am doing to manage the issue in order for the users to stay productive in the business application. If I could manage the deadlocks in an easier manner, that would be good motivation to move to SQL Server 2005. Does SQL Server 2005 have any new deadlock related features?
Suspect SQL Server 2000 Database - Part 2 3/26/2007	In your recent tip (Suspect SQL Server 2000 Database) you outlined a suspect database scenario. I took a look at the script and tried to run it in my environment. Unfortunately, this did not resolve my issue. Are you aware of other causes of SQL Server 2000 suspect databases? We have been working through a SOX audit and have been changing permissions at the file system level for our SQL Server. Could this type of change cause a suspect database? The timing of the permission change and the suspect databases correlate, but I am not sure if this is the true cause and affect.
Different Options for Importing Data into SQL Server 3/23/2007	Moving data into SQL Server is something that most DBAs or Developers are faced with probably on a daily basis. One simple way of doing this is by using the Import / Export wizard, but along with this option there are several other ways of loading data into SQL Server tables. Another common technique would be to use either DTS (SQL 2000) or SSIS (SQL 2005). In this tip we take a look at some of these other options for importing data into SQL Server.
SQL Server Understanding Indexing 3/22/2007	With so many aspects of SQL Server to cover and to write about, some of the basic principals are often overlooked. There have been several people that have asked questions about indexing along with a general overview of the differences of clustered and non clustered indexes. Based on the number of questions that we have received, this tip will discuss the differences of indexes and some general guidelines around indexing.
Service Broker Infrastructure Objects 3/21/2007	In a recent tip you outlined steps to troubleshoot Service Broker. I can see how that would be a good tip for someone who is actively working with Service Broker in SQL Server 2005. Unfortunately, I am new to SQL Server 2005 and Service Broker. I am not sure where to start with Service Broker. Can you outline the steps that are needed to setup Service Broker?
Grant Execute to all Stored Procedures 3/20/2007	With SQL Server 2000 no default server, database or application role was available to be able to execute all stored procedures. With SQL Server 2005 has this changed with all of the new security features? If not, what options do I have to grant execute rights to the needed database roles?
Suspect SQL Server 2000 Database 3/19/2007	I have a SQL Server 2000 database that has the wrong database status. For some reason it is in the 'Suspect\Offline' mode. I just need to correct the problem quickly and get my database back online. How can I do so? Once the status is correct, do I need to take any further steps?
Export data from SQL Server to Excel 3/16/2007	Exporting data from SQL Server to Excel seems like a reasonably simple request. I just need to write out a few reports for users on a regular basis, nothing too fancy, the same basic report with a few different parameters. What native SQL Server options are available to do so? Do I need to learn another tool or can I use some T-SQL commands? Does SQL Server 2005 offer any new options to enhance this process?
Handling Large Tables with Data Partitioning 3/15/2007	With the increasing use of SQL Server to handle all aspects of the organization as well as the increased use of storing more and more data in your databases there comes a time when tables get so large it is very difficult to perform maintenance tasks or the time to perform these maintenance tasks is just not available. In the past, one way of getting around this issue was to partition very large tables into smaller tables and then use views to handle the data manipulation. With SQL Server 2005 a new feature has been added that handles this data partitioning for you automatically, so the ability to create and manipulate data in partitioned tables is much simpler.
SQL Server Agent Proxies 3/14/2007	With so many security changes in SQL Server 2005, how did the security changes affect SQL Server Agent? I have heard about SQL Server Agent Proxies, but I am not too familiar with them. How do I set them up? What dependencies do they have? What is the security benefit?
ASP.NET Security for SQL Server 3/13/2007	In all of my ASP applications I need to have at least basic security, how can I do so in simple manner? The reality is that in the majority of my applications I need membership, profiles, role management and personalization, are any applications available directly from Microsoft to handle this need? If so, where can I find them? Do they integrate with the .NET 2.0 framework? If so, how do you set this up? Where can I find more information?
Service Broker Troubleshooting 3/12/2007	When learning about Service Broker (or any new SQL Server 2005 technology) you are bound to have some issues with your first application. With the value that Service Broker offers applications directly via the T-SQL language, this is a valuable technology to learn, experiment with and implement. Unfortunately, troubleshooting issues may be a little bit of challenge without knowing where to look and making sure the error messages are properly handled. As such, where can I find information to troubleshoot my Service Broker applications as I learn about the technology?
DBCC SHOWCONTIG Blocking Issues 3/9/2007	With most tasks that access database level objects there is always some type of locking that occurs that causes potential blocking situations. Unfortunately even processes such as DBCC SHOWCONTIG, which allows you to get table fragmentation information, also uses locks that could cause blocking issues. With SQL Server 2005 these issues have been resolved, but this is still an issue with SQL Server 2000. So how do you get around this issue?
Issue with SQL Server 2005 SP2 3/8/2007	If you haven't heard already, there was a problem identified with service pack 2 for SQL Server 2005. The problem has to do with the "History Cleanup" tasks and the "Maintenance Cleanup" tasks. The issue is that the interval that these tasks runs is not based on what you think when you set this interval. Based on this, your backup files or other files that are cleaned up by these tasks may be prematurely deleted or these files may stay around longer then intended and therefore possibly cause disk space issues.
Hiding instances of SQL Server 2005 3/7/2007	One of the nice things about SQL Server 2005 is that it is pretty easy to find what machines have SQL Server installed. If you open up SQL Server Management Studio and browse for servers or browse for instances of SQL Server with some other utility it is pretty easy to find all of the installed instances on your network. Is there any way to not have the name of your SQL Servers broadcasted so other people can't find the server without knowing the name of the server and/or instance name?
Optimize database snapshots 3/7/2007	In a previous article, SQL Server 2005: When and how to use Database Snapshots, we took a look at the new database snapshot feature which is available only in SQL Server 2005 Enterprise and Developer Editions. Basically, a database snapshot allows you to create a static read-only copy of your database that can be used for other purposes, such as reporting, auditing or recovering data.
Remotely Accessing the SQL Server Desktop 3/5/2007	How can I get direct access to my SQL Server's desktop, not a terminal services session? Do I need to purchase a third party product or is a tool available with Windows or SQL Server? I know I should not need access to my SQL Server like this on a regular basis because I can manage my enterprise of SQL Servers via Management Studio or Enterprise Manager. However, I want to find out if an option is available to keep in my back pocket in case a pop-up is on the desktop or I need to check to see if something unexpected has occurred on the SQL Server.
Help With Full-Text Catalogs - Stored Procedures Available In SQL Server 2/28/2007	As mentioned in a previous tip, there are a number of locations where full-text search information can be found in SQL Server 2000 and SQL Server 2005. SQL Server 2005 includes some dynamic management views, but in SQL Server 2000 there are no such views, requiring creation of your own statements or views to gather the information.
How to get length of data in Text, NText and Image columns 2/27/2007	There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?
Minimally Logging Bulk Load Inserts into SQL Server 2/22/2007	One of the advantages of using the Bulk-Logged recovery model is to minimally log bulk load commands, but still have other transactions fully logged. You may find that when you set your recovery model to Bulk-Logged or to Simple and you run your bulk load commands that the size of your transaction log still grows quite large. The reason for this is that there are some other settings and criteria that need to be met in order to minimally log the bulk insert commands and to minimize the amount of space needed in your transaction log.
Virtual Server Technologies and SQL Server 2/21/2007	Recently virtual server technologies have been getting a great deal of buzz in the industry. Is this a technology that I should have on my radar screen? What impacts does this technology have on SQL Server? What can virtual SQL Servers do for me as a DBA? I have been hearing a great deal of positive information, but is there any down side of implementing this technology for SQL Server? Where can I find more information on virtual server technologies?
Configuration Information Locations for Full-Text Indexing in SQL Server 2000 2/19/2007	Information about the status of your full-text search process is not always readily available or in one place. Although you can check some values in the Support section of Enterprise Manager, not every configuration value is there. In order to check if your full-text environment is configured correctly, you need to know where to look for this information.
SSIS Package Scheduling with SQL Server Agent 2/15/2007	With the new features in SQL Server 2005 Integration Services, what are the related impacts to SQL Server 2005 Agent? Do I still need to build a dtsrun command (SQL Server 2000) to execute an SSIS Package in an automated manner? What insight do I have into the internal SSIS objects and some of the advanced features?
Built-In Performance Reports in SQL Server 2005 2/14/2007	Finding a good reporting mechanism for your SQL Server environment can be tedious and time-consuming-you can either write your own reporting application or choose a third-party solution. You may also have to install an instance of Reporting Services in your environment, depending on the needs of the application. SQL Server 2005 includes a number of built-in reports to assist you in troubleshooting and measuring performance.
Monitoring transaction log space 2/13/2007	Keeping track of your transaction log usage is key to understanding how your database transaction logs are being used as well as keeping track of how much space the transaction logs are using. Depending on the recovery model that is set within your database and also the backup jobs that you run, the size of your transaction log can vary quite a bit. So what is the best way to keep track of the transaction log usage?
Determining space used for each table in the database 2/12/2007	One thing that is often handy to know is how much space your tables are using within your database. It is helpful to know the number of rows, the data space used as well as the index space used. There are several ways that you can get this information, by reading the system tables, using the built-in reports in SQL Server 2005 or by using the Taskpad view in SQL Server 2000, but what is a simple way of retrieving this data without using the GUI or without having to write a query to gather this data from the system tables?
Using the GthrLog Utility to Troubleshoot Full-Text Search Errors 2/9/2007	Providing Full-Text Search functionality for an application using SQL Server 2000 can be tricky enough even without errors in the population process. There are a number of ways to evaluate and diagnose errors that occur. One way is to look in the Windows Event Logs, but if you've seen information recorded in the logs, you've probably closed Event Viewer with more questions than answers. Although the information in these entries are general, you can find out specific information about what happened by using CScript and the gthrlog.vbs file.
ROW_NUMBER() Function 2/8/2007	For many applications searching and providing a subset of a large result set is the core application functionality. Often the result sets are large and it is a resource intensive process to gather and display the data. This is because the same query is issued each time a new page is rendered, but a different portion of the result set is displayed. Once this technique becomes too much of a performance burden, custom solutions are built to meet the need. With all of the improvements in SQL Server 2005, does a simpler and less resource intensive approach exist to efficiently page through a large result set?
Database mirroring and its witness 2/7/2007	When configuring database mirroring, one option is to use the High Availability mode. This option allows for synchronizing of transaction writes on both servers, as well as offers the ability of automated failover. When using the High Availability mode, you need to have three instances of SQL Server: the principal, mirror and the witness. Here is a summary of what each component does.
Scheduling Backups for SQL Server 2005 Express 2/7/2007	One problem with SQL Server 2005 Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server.
Getting operating system content into SQL Server 2/6/2007	Every once in awhile you may have the need to access some data from the Windows operating system or from the contents of a file. The need may be to find out what files exist in a directory, to read the contents of a log file into SQL Server or for some other purpose. Whatever the need, what is a simple way of pulling this data into SQL Server?
Guest User Account 2/5/2007	Who invited all of these guests to my database? Did you know that the guest user account even exists in your SQL Server databases? Do you know that SQL Server needs the guest user account for particular functionality that differs in SQL Server 2000 vs. 2005? Do you know if your SQL Server 2000 and 2005 databases have the guest login in their respective databases? Do you consider this a problem or the reality for your SQL Server environment?
Energy Policy Act of 2005 for SQL Server Notification Services 2/2/2007	In an earlier tip (Energy Policy Act of 2005 Implications on SQL Server) we outlined how SQL Server 2000 and SQL Server 2005 leverage Windows for their date and time needs. As such, applying patches to Windows would properly support the overall SQL Server date and time needs for the Energy Policy Act of 2005. Unfortunately, that is the not the case with SQL Server 2000 Notification Services and SQL Server 2005 Notification Services, additional steps must be taken for proper compliance. Let's examine how Notification Services needs to be patched to comply with the date change on March 11, 2007 at 2:00 AM.
Troubleshooting Performance Problems in SQL Server 2005 1/31/2007	With the introduction of SQL Server 2005 came many changes. One of these dramatic changes was how to find and troubleshoot performance issues. SQL Server 2005 introduced dynamic management views which gives you insight into various aspects of SQL Server. A previous tip was written about DMVs to introduce the topic as well as highlight some of the DMVs. Even thought Microsoft has exposed a lot more performance related information in the DMVs, interrupting the results or even knowing what to do is still a problem. Although there is not a hard and fast rule as to what to do, I did come across a great whitepaper that does shed some light as to what can be done and also how to interpret some of the results.
Getting Started with Analysis Services 1/30/2007	SQL Server is packed with so many different components that finding the time to discover what is included as well as finding the time to learn about these different features is often a challenge. SQL Server is great as a relational engine, but there are two other parts of SQL Server that offer a wealth of functionality, automation and insight into your data. These two tools are Reporting Services and Analysis Services.
Replicated stored procedure options with SQL Server 2005 1/29/2007	Replicating stored procedures between databases can be a straightforward task if the right processes and technologies are available in SQL Server. These would include the native replication features or log shipping. Without them, you can still use manual processes to achieve the same result. In fact, the alternatives may be preferable in particular environments and scenarios.
Managing Fragmentation 1/25/2007	There are several things that should be done on a regular basis and one of these things is to manage database fragmentation. Depending on the tables, queries and indexes that are being used fragmentation can cause performance issues as well as using unnecessary space in the database. Database fragmentation is similar to disk fragmentation in that the data is stored in various places in the database file instead of sequentially or next to like data within the database. This often occurs where you have non-sequential keys and the constant inserting, updating and deleting of data causes the data to become fragmented as well as the use of additional data pages to store the data. So what steps should be taken?
RAID as your line of defense in SQL Server 1/25/2007	As hardware becomes more and more advanced there are several safeguards that companies employ to ensure their systems stay online. This includes: multiple power supplies, multiple network cards, multiple processors, multiple controllers, hot swap memory, hot swap disks, hot swap processors, clusters, redundant disk storage systems, failover sites and varying levels of RAID to support the data redundancy. With all of these measures in place why would your database servers ever go down? That's a good question, but even with the best defenses there is always the possibility of a failure.
Full-Text Temporary Folder-Where Exactly Is It? 1/24/2007	We've mentioned in previous tips that the process of creating, building, and maintaining full-text search capabilities is quite a daunting task. During the installation of Full-Text Search for SQL Server 2000, one of the folders gets installed in a not-so-conspicuous location that could potentially be troublesome as the server gets busier. To get a good start on building and maintaining an efficient Full-Text Search process, move the temporary folder used by the Microsoft Search service.
SQL Server 2005 - Service Pack 2 1/22/2007	As with past versions of SQL Server, Microsoft is releasing service pack updates for SQL Server 2005. The product officially launched in November 2005 and there has already been one service pack release on March 18, 2006 and service pack 2 is in the works. Microsoft is making these pre-releases available with their CTP version or Community Technology Preview. The problem comes into play as to who should test these versions and how should they be tested?
Dynamic SQL 1/19/2007	In some applications having hard coded SQL statements is not appealing, because of the dynamic nature of the queries being issued against the database server. Because of this sometimes there is a need to dynamically create a SQL statement on the fly and then run that command. This can be done quite simply from the application perspective where the statement is built on the fly whether you are using ASP.NET, ColdFusion or any other programming language. But how do you do this from within a SQL Server stored procedure?
Drag and drop query result columns in SQL Server 2005 1/18/2007	There are so many new little features in SQL Server 2005, finding all of them is a challenge and sometimes you just accidentally find things without even looking. One nice feature of the query tool is the ability to display your results in a table format. This makes it very easy to copy and paste the results for SQL Server into an Excel spreadsheet, Access table or some other application. Although this is great there are times when it would be nice to reorder the columns in the query results. For queries that you write this is pretty easy by just changing the column order, but if you have a query that took a long time to run or if you have no control over the output from a stored procedure or some other system function it is sometimes easier to just copy the results into Excel and then change the column order for the results. Luckily in SQL Server 2005 there is a new way to do this.
SQL Server Log Shipping 1/17/2007	There has been a lot written about SQL Server Log Shipping as well as several scripts and techniques for implementing this process. Basically Log Shipping is the process of backing up your database and restoring these backups to another server for failover. To keep the data current and to minimize risk of data loss the process involves using SQL Server transaction log backups. By using transaction log backups the size of the backups are much smaller than full backups and the restore process can be done on a continuous basis. So how do you implement Log Shipping?
Connection Managers in SQL Server 2005 Integration Services 1/16/2007	In SQL Server 2000 connections were made by dragging a connection type to the designer. The problem was that the number of connections was limited, and many of them had to be made using a scripting language like VBScript or JScript. The arrival of SQL Server 2005 included a brand-new SQL Server Integration Services that offers many ways to extract, tranform, and load data. Along with those new methods came a certain re-arrangement of objects as well as new ways to connect to the objects needed to move and manipulate data. Perhaps the most important is the new Connection Manager.
Troubleshoot SQL Server queries and correct I/O 1/16/2007	When SQL Server performance is critical, inevitably you find a problem related to a CPU, I/O or memory bottleneck. The culprit is typically related to the architecture (hardware or software) or the code being executed. With such a broad scope to determine the root cause of the performance issue, you must first conduct some discovery. Once the problem is pinpointed through that process, the next step is to implement corrective actions for a final resolution, especially with I/O related issues.
Memory Error Entries in SQL Server 2000 1/15/2007	You're reviewing your SQL Server error logs and a series of words catch your eye...STOLEN, DIRTY, KEPT, WAITING. And those words are surrounded by a bunch of numbers. The sight is more than a little ominous, particularly if you've never seen it before. So what is it? Quite simply, it is the status of memory allocation after a memory exception. But finding the reason why your server decided to throw all of this information in the log can be a little tricky. Here are some ideas on why it happened.
Opportunities with SQL Server 2005 Express Edition 1/12/2007	Have you heard of the SQL Server 2005 Express edition, but not sure where it fits into your overall architecture? Are you concerned it is too constraining and will not meet your business needs? Are you looking for ways to continue to use SQL Server in niche capacities? Are you trying to balance distributing your applications without causing a future server consolidation project? If so, this tip will outline the opportunities with SQL Server 2005 Express edition in addition to considerations when adopting this version of SQL Server 2005.
SQL Server 2005 Error Log Management 1/11/2007	My SQL Server 2005 error log is getting very large due to auditing login activity and freezes Management Studio when it loads. On another SQL Server I have lost much of the historical error log data from SQL Server service restarts and Windows reboots. Do I have any options to address these two needs to review and retain this data in an easy manner?
Password management options for the sa login 1/10/2007	In earlier tips from the sa series we outlined 'When not to use the sa password' and 'When was the last time the sa password changed?'. In this installment of the sa series we will be outlining options for password management. In a nutshell, depending on the security needs of the organization dictates how the sa password should be managed. Although at a certain level, the sa login needs to be protected even in environments without specific legal or regulatory requirements. As the security needs increase, then it is necessary to implement additional measures to manage and protect the most privileged (out of the box) login in SQL Server, the sa login.
Security Vulnerabilities - SQL Server 2005 Installation Log Files 1/9/2007	Where can I find the SQL Server 2005 installation log files to ensure no sensitive data, specifically clear text passwords, are stored in these files? I can remember from earlier versions of SQL Server that sensitive data, specifically clear text passwords, were stored in the installation and service pack log files. Is this the case with the SQL Server 2005 installation?
Standardized Error Handling and Centralized Logging 1/8/2007	Error handling was not always used in SQL Server 2000 because the techniques were cumbersome. With SQL Server 2005 the TRY and CATCH syntax is available which simplifies the error handling process in all T-SQL code. To take the error handling to the next level, why not standardize the error handling across your T-SQL code and centralize the location of the errors? Seem like a good idea, right? So let's jump into how to do this.
Identify all of your foreign keys in a database 1/5/2007	SQL Server Enterprise Manager and Management Studio are pretty good tools for giving you information about a particular object, but when you want to get information across your entire database or server this becomes a bit of a challenge. There are several system stored procedures and now dynamic management views in SQL 2005 to provide some of the information, but there is still a bit of missing functionality if you want to get data across the database or server. One of these recent challenges was getting foreign key information across the entire database. Management Studio has some nice additions where you can see the FK constraints on a table by table basis, but getting all of the foreign keys is a bit more a challenge. There is a stored procedure called sp_fkeys, but this procedure requires you to include at least the primary key table name. So how can you get a complete list?
What's in your backup files? 1/4/2007	Sometimes you come across SQL Server backup files stored on your file system and it is hard to determine what is in the file. From the filename you may be able to decipher that it is a full backup, differential backup or transaction log backup, but how do you really tell what is in the file. Luckily SQL Server offers a few additional commands that you can use with your database backup files to determine the contents of the backup files. These options include HEADERONLY, FILELISTONLY and LABELONLY.
Date/Time Conversions Using SQL Server 1/3/2007	There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.
SQL Server bulk-logged recovery 1/3/2007	SQL Server offers three types of recovery models: simple, full and bulk-logged. These models offer varying levels of recovery for restoring your databases from your backups. Bulk-logged recovery advantages include minimal space requirements for transaction logs and the best performance for bulk operations. Watch out, though. One disadvantage is that with a bulk-logged transaction in your backup file, you cannot do a point-in-time recovery. In this tip, I'll explain the circumstances where you'd want to opt for bulk-logged recovery in SQL Server and what steps you'll need to take to restore a database set in that model.
Data cleansing techniques in SQL Server 1/2/2007	When it comes to decision making, the value of data is in its accuracy. If the data is inaccurate, decision makers can make flawed assumptions, which could have disastrous consequences. In this tip, I will outline techniques to ensure data accuracy, including: Collection validation, Referential integrity, Lookup table usage, Leverage the application logic, Cross checks, Mapping tables, Summary validation, Spot check validation, Document the norms, NULL value management, Validation logic and Exception handling and remediation.
You might be a DBA... 12/29/2006	To bring some comic relief to MSSQLTips.com at the end of the year, check out today's newsletter. It is in the spirit of Jeff Foxworthy. So here you go, you might be a DBA, if...
Performing SQL Server Maintenance with No Maintenance Window 12/28/2006	Performing database maintenance on a 24X7 SQL Server is a unique problem in and of itself. The issues are numerous. First is the realization that database maintenance is important, but not important enough to jeopardize losing data or stopping business operations. Second is that database maintenance is needed to ensure the databases are free of errors and are performing well, so it is up to you to be creative to figure out how perform database maintenance. Third, once you have figured out when you can perform database maintenance, you need to ensure you do not break the high availability\fault tolerant\disaster recovery plan or any down stream processes while performing maintenance.
When was the last time the sa password changed? 12/27/2006	With the rights that the sa login has by default in SQL Server, it is imperative to change this password on a regular basis whether it is monthly, quarterly or semi-annually. In addition, as DBAs move on to other opportunities, it is wise to change the sa password as well. Changing the sa password should be a relatively easy process requiring little to no impact on the organization. Unfortunately, changing the sa password on a regular basis is not a common practice at most organizations because the impacts of changing the password are unknown.
Change Tracking Full-Text Population 12/26/2006	Creation and maintenance of full-text catalogs and indexes in SQL Server 2000 can be taxing on both the server and the administrator, as well as take a lot of time to populate. This can result in increased deployment times and potentially inaccurate search results. To ease the deployment process and increase the uptime of your application, you can use Change-Tracking population.
How to tell what SQL Server version you are running 12/21/2006	One issue that I am often faced with is determining what service pack is installed on the SQL Server. Until recently the version of SQL Server that was installed was just a number, but now with the later service releases for SQL Server 2000 you can also see what service pack is installed. For SQL Server 2005 it still only shows you the version number that is installed. So how do you know what service pack is installed?
Automatic cleanup of scheduled jobs 12/20/2006	One problem that people often have is a large number of SQL Agent jobs on their servers and they don't want to remove them because they are not exactly sure what the jobs are used for or why they were created. In most cases a lot of these jobs were created to run one time, but no one took the time to enter a description or better yet delete the job after it was run. So because of this you end up with a lot of extra scheduled jobs that you probably don't really need.
Giving and removing permissions in SQL Server 12/19/2006	Often times when auditing SQL Servers I notice that most environments use the default database roles to grant access to users. These default roles such as db_owner, db_datareader and db_datawriter are great, but often times they give the users a lot more access then they really need. Giving permissions and removing permissions is not that hard to do within SQL Server, it just takes some time to determine what permissions should be applied.
Installing a SQL Server 2005 instance 12/19/2006	To most DBAs and developers, installing and configuring a new SQL Server is exciting because it brings on a whole new set of challenges and opportunities. The installation is just the first of many new learning opportunities with the latest version of SQL Server. In this guide we'll walk through the steps to properly install a SQL Server 2005 instance and the current service pack (SP1) to get you started.
Supporting tables for SQL Server 2005 style maintenance plans 12/18/2006	As you have probably found out there are a lot of things that have changed with SQL Server 2005 and quite a few things that have remained the same. One of the changes that has occurred is how to setup maintenance plans in SQL Server 2000 vs. SQL Server 2005. This differences in setting up a maintenance plan were covered in this previous tip. Another change that has taken place is where the data about maintenance plans is stored. The old tables for maintenance plans still exists, but where is the data for the new plans?
When not to use the sa password 12/15/2006	Since SQL Server creates the sa standard login when mixed mode authentication is selected during the installation process it is common entity, making it a natural target to try to penetrate SQL Server. With many of the SQL Server installations using mixed mode authentication for custom applications, web based applications or third party application needs, managing the usage and password for the sa login is critical. It is not something to take lightly. This known login is a natural target because it has the highest privileges in SQL Server. As such, there are times that the sa login just should not be used to prevent unnecessary exposure.
SQL Server Business Intelligence Tools 12/14/2006	One of the fastest growing segments in the SQL Server market is the Business Intelligence area. With vast amounts of data, managing the data and gaining insight has become more of a challenge. Luckily, many organizations are focusing on products to improve the insight into the data for IT professionals and users alike. This give us more time to focus on our needs and data, rather than having to build sophisticated custom products as a portion of the requirements. As such, in this tip we will outline the vendors providing Business Intelligence tools in the SQL Server market.
SQL Server disaster recovery: Recreating historical data 12/14/2006	There is one task I hope you never face: May you never have to recreate data at different points in time in order to unfold a suspicious act or uncover lost or stolen data. Most databases store data at a core data level, which reveals only the most recent state of the data to the end user and the database administrator. This issue of only having the latest version of the data leaves you unable to identify the whereabouts of particular data at different points in time during the data lifecycle.
So what's the plan? 12/13/2006	Do you feel like all of your projects are going in circles with little progress being made on a weekly basis? Do these projects prevent you from working on the latest and greatest technologies? If so, consider taking the first step in trying to move forward with your projects in a more straight forward manner.
Performing Maintenance with Databases in Full Recovery mode 12/12/2006	When I have performed database maintenance in the past the transaction log backups were huge and I was unable to restore the transaction logs. This caused more problems for me so I have stopped performing SQL Server maintenance. My SQL Server performance is now an issue, so I am looking for a process to be able to perform database maintenance for my databases in full recovery mode during a pre-defined maintenance window.
Dynamic Management Views and Functions - SQL Server 2005 Exposed 12/11/2006	With SQL Server 2000 it was difficult to capture real time statistics on many of the core database engine features without issuing DBCC commands, running Profiler or scheduling the execution of custom scripts. With add-on features such as Full Text Search it was difficult to capture metrics on portions of the application and troubleshooting performance was less than efficient. From SQL Server 2000 to 2005, the number of add-on features has grown and troubleshooting overall SQL Server performance has the potential to be even more of an issue.
Selectively rebuilding indexes with SQL 2005 maintenance plans 12/8/2006	With SQL Server 2000 you had the ability to rebuild indexes using maintenance plans. This was great if you wanted or needed to rebuild every index in your database, but for very large databases or tables that were not changed that often you were forced to create scheduled jobs with the appropriate T-SQL to only rebuild indexes on the tables that you specified. With SQL Server 2005 this has all changed and now there is an option to select which tables or views you want to rebuild or reorganize indexes on vs having to rebuild every single index in the database.
SQL Server backup and restore across the network 12/7/2006	Sometimes there is a need to backup or restore your database to another server because of a lack of available disk space on the current server. In this situation you have no other choice but to either remove unneeded files, find additional local storage or backup/restore across the network to another server. When using Enterprise Manager you can only see the local drives, so how do you address the drives on the other server?
Auditing Products for SQL Server 12/6/2006	With the many needs (SOX, HIPAA, internal, etc.) for auditing sensitive data, what are the options available in the market place to audit SQL Server data and code changes? Do these products automatically collect the data as well as build reports and selectively alert on critical issues?
Managing and maintaining identity values 12/5/2006	One nice feature that SQL Server offers is the ability to have an automatic counter or identity value column in your table. You have the ability to have one identity value column per table and this is a simple way of having an automated process of generating a sequential number for each of the records in your table. Once in awhile you may delete the data in the table and what to reset the value or maybe you just want to know what the next number will be. So how do you figure this out?
How to rename a database 12/4/2006	Sometimes there is a need to change the name of your database whether this is because the original name was based on some other project that is no longer relevant to the data stored in the database or maybe it was because you restored a database and at the time just gave it a temporary name, but long term it ended up being a database that needed to remain on your system. Regardless of the reason there may come a time when you want to or need to rename a database. Unfortunately this is not one of the tasks you can do via Enterprise Manager for SQL Server 2000, so how do you rename a database?
Easing the Capacity Planning Burden 11/30/2006	A common headache for database and system administrators is capacity planning in SQL Server 2000. A good guess is a good start, but it is equally important to keep a close eye on databases and tables to make sure disk space shortage doesn't creep up on you. One of the easiest ways to keep up with your databases is to use some of the system functions to your advantage, like sp_spaceused and xp_fixeddrives.
Report Models in Reporting Services 2005 11/29/2006	Users have all but fallen in love with Reporting Services. First, for its presence anywhere an Internet browser can be opened and second for the opportunities to export data to numerous formats. But users also like their toys and want to manipulate the data many ways.
Archiving Data in SQL Server 11/28/2006	Every system has it, do not feel like you are the only one. Step up to the plate and correct it. That is large amounts of data that were once needed for the business that can now be archived. This data could be a large performance issue, because you are not able to perform maintenance on the table(s) because the time needed exceeds your maintenance window. As a DBA or developer just because you do not think the data is needed may not mean that it can just be deleted. Many industries have legal requirements to maintain the data. On the other side of the coin though, just because a business person says the data is needed does not mean it has to be kept in the primary OLTP database that your entire company uses. Seek out the facts and determine which strategy is right for your organization.
Using Multiple Filegroups 11/27/2006	With the recent filegroup tip (Filegroups in SQL Server 2005), the next natural question is: when should I start thinking about using multiple file groups? Since SQL Server uses a single filegroup for each database, moving to multiple filegroups is not a simple decision that should be made on Monday morning. Understanding the key indicators to move to multiple filegroups should serve as a means for implementing multiple filegroups both proactive and reactive IO bound scenarios.
Logging Options for Reporting Services 2005 11/22/2006	Reporting Services is one way of providing important business information to those who need it most. But there are certain questions to ask yourself when utilizing Reporting Services as a delivery mechanism: Who is accessing what reports? How often are reports being accessed? How many reports are being executed at any given time?
DBCC CHECKDB with DATA_PURITY 11/21/2006	One process that should be run on a set basis to ensure integrity in your database is to run either DBCC CHECKDB or DBCC CHECKTABLE. These processes check allocation, structural, and logical integrity of the object or objects and report back any inconsistencies that are found. One thing that these processes have not check for in the past is the data itself to see if there are column values that are not valid or out-of-range.
Replication support only option with SQL Server 2005 replication 11/20/2006	When setting up transactional replication with SQL Server 2000 there were only two options for getting your subscribers the data and the objects necessary for replication. The first option was "none" which meant you had to do everything manually and the second option was "automatic" which did everything from creating the tables, creating the stored procedures needed for replication and synchronizing the data. The automatic option was great if you needed to do all of these steps, but in some cases the data already existed and you really only needed the support objects to handle replication. The issue with the "none" option is you had to do everything manually which often led to mistakes.
OSQL and SQLCMD: Finding Servers On Your Network 11/17/2006	A common problem noted among database managers and network administrators is the number of unauthorized SQL instances on a network. Without proper application of service packs and hotfixes these instances create holes through which hackers and others can exploit the database infrastructure. Another situation you may find yourself in is needing to connect to a server instance whose name you can't recall. In either case you can find what you need by using OSQL or SQLCMD.
Filegroups in SQL Server 2005 11/16/2006	With the anticipated growth of my database what native features does SQL Server 2005 have to help me scale to support a large amount of data? I have a relatively small database today, but based on my capacity planning, I anticipate that the growth will be 3 to 7 times my current database in the next 18 months. What steps can I take from a planning perspective to ease this growth?
Default Trace in SQL Server 2005 11/15/2006	I have been running SQL Server 2005 server side traces to address some SQL Server 2005 performance issues. I have noticed an unusual trace session that is running. I know I have not been running this trace and know I have been diligent about running only a single trace to not impact performance. So where did this trace session coming from?
Breakpoints in SQL Server 2005 Integration Services 11/14/2006	In SQL Server 2000's Data Transformation Services (DTS) the tool had the ability to issue each portion of a package one step at a time. With some custom coding, it was possible to determine variables and the package status by creating T-SQL tasks to write the items to a table. Unfortunately, this process could become very tedious for large DTS Packages.
Data model access after a database restore 11/13/2006	After a database restore, why is it possible for me to access all of my objects except for the data model I created? Did I back it up correctly? Is it a separate backup and recovery process? Has it been corrupted?
SQL Server query design: 10 mistakes to avoid 11/13/2006	With the substantial and sustained data growth of SQL Server databases, coupled with the sub-second response times expected by users, it is critical to avoid poorly written queries. In this tip we will outline 10 common query design mistakes to avoid. Take a look to make sure you are not falling victim to these mistakes and consider the recommendations as a means of correcting your queries.
Deleting duplicate rows when there is no primary key 11/10/2006	Every once in awhile a table gets created without a primary key and duplicate records get entered. The problem gets even worse when you have two identical rows in the table and there is no way to distinguish between the two rows. So how do you delete the duplicate record?
Setting up Database Mail for SQL 2005 11/9/2006	Many things have changed with SQL Server 2005 and one of these changes is the replacement of SQL Mail with Database Mail. This is a good thing, because SQL Mail relied on having a MAPI mail client installed such as Outlook in order for it to work. With SQL Server 2005, this has changed and now the mail services use an SMTP server to send out emails which makes it a whole lot easier to setup and maintain. So how do you setup Database Mail?
SQL Server backup encryption for improved security 11/9/2006	With the growing need to keep data secure, by all means, don't overlook securing your database backup files. With native SQL Server backups, the data in the backup file is stored in plain text and can be easily read just by using a text editor. Depending on the data types used in your tables, some data is much easier to view than other data.
Using passwords with backup files 11/8/2006	One issue when creating backups for SQL Server databases is that these backup files can be restored to any other SQL Server as long as the version of SQL Server supports the backup file that you are trying to restore. In most cases this is not an issue unless you are trying to restore a backup from 6.5 or earlier onto a SQL Server 7.0 or later installation. Because of this ability to restore the backups to any other SQL Server this exposes your data to potential theft or misuse of your data. Most of the data in backup files is highly compressible, so a backup that maybe 1GB can easily be compressed to about 200MB. So even your largest backup files could be vulnerable to someone copying them to their laptop or burning them to a DVD and then using the data in a way that you never intended.
Copying column headers with grid query results 11/7/2006	One of the nice features with SQL Server is the ability to create result sets from queries into a grid result set. This data can then be copied and pasted in other application such as Excel. The downside to saving the results in a grid is that the column headers don't get copied along with the data. To get around this you could query the data in the text format, so you could copy the results along with the column headers, but then you are faced with formatting issues.
SQL Server Four-part naming 11/6/2006	With some applications there may be a need to pull data from other instances of SQL Server or from other databases within the same instance. Often this can be achieved by having multiple connections from your application pointing to each of these data sources. This is great for this one application, but what if there is a need to do this within SQL Server or for stored procedures or views that are called from several applications?
SQL Server 2000 to 2005 - Crosswalk SQL Server User Defined Objects 11/3/2006	With the recent tips on data modeling (SQL Server 2005 Exposed = Data Modeling Tools and SQL Server Data Modeling Tools), a natural question that arose is: where are all of the underlying objects stored in SQL Server? As such, in this tip we will outline where the user defined objects in user defined databases for both SQL Server 2000 and 2005.
SQL Server Management Studio - Comma Delimited Result Set (with more tips and tricks) 11/2/2006	How can I return a comma delimited result set with my queries? I am currently using SSIS to be able to generate a comma delimited result set to a text file, but this is just over kill. I thought about using the graphical result set and then massaging the data in Excel or just stringing the results together with a comma, but this just seems like the wrong approach. What is the best way to return a comma delimited result with SQL Server 2005 Management Studio?
Maintenance Tasks: Automating the RESTORE VERIFYONLY Process 10/31/2006	No doubt you likely have a monstrous database infrastructure to manage and the tasks required to keep it maintained and healthy can be daunting. For that reason those tasks should not be performed all in one sitting, nor during peak-hours of use. As a general rule, maintenance tasks should be performed during down times or periods of relative inactivity, which likely means you aren't sitting in the office when it needs to be done.Taking time to think about maintenance strategy and dividing up the tasks into smaller, manageable chunks saves resources on your server and allows for reaction to issues that arise. The first and most important aspect of database maintenance is the backup and restore strategy. To make sure your backup files are readable you can create a procedure to test each file and email the results of unsuccessful tests to whomever needs the information
Boost data warehouse performance 10/30/2006	Data warehouses are typically the largest databases at any organization. Building and managing systems is a large undertaking and these projects can quickly become unwieldy with numerous users providing incompatible input. Boosting the system's query performance is possible but takes planning, followed by insightful design and development phases. In this tip we will outline techniques to capture and plan for performance needs and then we'll boost your data warehouse performance on SQL Server.
Modifying DTS Packages Using Disconnected Edit 10/30/2006	During the creation of Data Transformation Services (DTS) packages, DTS checks to see if the objects that are referred to actually exist (i.e., connections). If the package is created on one server, but deployed on another, then some modification is necessary, otherwise the package will fail. There may also be times when you want to transfer DTS packages stored as SQL Server packages or perform other tasks using system tables. These tables do not appear in the source and destination lists of Transform Data Tasks, so you must change the table name manually. One method you can use to make corrections or access database objects not available through traditional means is the Disconnected Edit method.
Finding Code Errors using Query Analyzer and a Double Click 10/27/2006	When dealing with large scripts it is sometimes difficult to find all errors in your code prior to executing the code. You can review the code and look for syntax issues as well as do searches to make sure the code is intact, but when dealing with a large amount of code some problems are sure to surface.
Setting Up Alerts for All Jobs 10/26/2006	One of the things you can do with SQL Server 2000 is setup Operators to be notified when there is a job failure or an alert has been triggered that should notify your DBA team. The problem with setting up notifications for jobs is that you need to edit each and every job in order to turn on notifications. If you only have a few jobs this is not that much work, but if you have several jobs and have several servers making this change may take some time. In addition, if you are using Maintenance Plans there is not a way to automatically setup notifications for the jobs, so you would have to do this manually as well. Luckily there is a stored procedure that can help make this task easier.
Speed up SQL Server backup and restore time 10/26/2006	One of the most important tasks that occurs on every SQL Server is running backups and restores. Backup copies of your database give you the security that if any issues surface with your production database you have a complete copy to fall back on. In most cases the restore process is done for non-production critical means such as refreshing development\test environments or refreshing a reporting environment. But in the most critical mode you are restoring these backup copies to replace or fix a production environment.
Database Maintenance Plans and Backup File Management in SQL Server 2005 10/25/2006	Database maintenance is vital to the health of an organization's data delivery infrastructure. In SQL Server 2005 Microsoft redesigned the method through which Database Maintenance Plans are created and managed. These plans can be created through a wizard in SQL Server Management Studio (SSMS). They are handled mainly through the Business Intelligence Studio (BIDS), and custom plans can be created in BIDS using SSIS. Although the process is comprehensive, the management of physical backup files is not automatically handled in the Database Maintenance Plan Wizard, but history cleanup is completed. In this tip we will outline a number of methods that are available to manage these files.
Transforming Invalid Data Formats in DTS Using ActiveX Script 10/24/2006	Date formats don't always come in the acceptable formats used by SQL Server, requiring transformation prior to bringing the data into the database. The use of views to transform date formats can be done but requires moving the data twice-once to the working table and again to the production table. This may cause performance issues. Another method for transforming data is through the use of ActiveX transformations.
Converting Invalid Date Formats Using SQL Server 2000 Data Transformation Services 10/23/2006	Importing data into SQL Server 2000 using Data Transformation Services isn’t always picking a source and destination and letting it fly. Sometimes data is formatted in a way that SQL Server won’t accept. In these cases the phrase “Extract, Transform, and Load” (ETL) need to be treated as “Extract, Load, and Transform", with dates being especially troublesome. There are a number of ways to transform data as it comes into the database, the most popular of which is either through a view or ActiveX script transformation. This tip will discuss using a view to manipulate the data as it gets moved to the final table.
Recovery Model Options in SQL Server - Expert Podcast 10/23/2006	Recovery models available in SQL Server include full-recovery, bulk-log recovery and simple recovery. In this podcast, SQL Server Backup and Recovery expert Greg Robidoux describes each of the three models. Robidoux, president of Edgewood Solutions, will guide you toward making the right recovery model choice for your SQL Server environment.
Verifying Backups with the RESTORE VERIFYONLY Statement 10/20/2006	There are a number of ways to make sure data in your organization is highly available, yet technology always seems to find a way of making data disappear. Data backups should be the cornerstone of every organization's disaster recovery plan. So, how do you know whether the backups are actually readable? One way is by issuing a RESTORE VERIFYONLY statement against the backup file.
Unattended Installation of SQL Server 2000 10/19/2006	SQL Server 2000 installations are tedious and time-consuming, particularly if there are many servers to roll out. Having someone sit in front of the server during installation can also be very expensive. Microsoft created a method through which SQL Server can be installed in an unattended fashion. Using an unattended installation method can provide consistent server installations throughout the organization, and reduce the Total Cost of Ownership (TCO) of the database system.
SQL Server 2005 Login Properties - Enforce password policy + Enforce password expiration 10/18/2006	For some reason your web site is up, but none of the data is loading on your web site? Is your SQL Server up and running? Are you able to access the data with either your Windows account or sa? What does your SQL Server error log report? What is the culprit?
Microsoft Full-Text Engine for SQL Server 2005 10/17/2006	Finding information in large text or Binary Large Object (BLOB) columns is a daunting task. Using the LIKE keyword, although effective in most cases, will not always find the needed information. Full-text searching and indexing is a useful yet under-utilized feature of SQL Server that assists developers and users in finding the information. Versions prior to 2005 use a shared service to provide full-text functionality, making the process inefficient. The backup and restore process was also tedious and separate from the usual database backup process. In SQL Server 2005 Microsoft designed a new version of the engine, called Microsoft Full-Text Engine for SQL Server (MSFTESQL) to make full-text searching and indexing more efficient and easier to maintain.
SQL Server 2005 Management Studio Query Designer 10/16/2006	When writing queries I either need to reference an existing data model or visualize the tables in my head in order to complete my query. Does a visual query tool exist in the SQL Server 2005 tool set? If so, how can I access it and use it to streamline my development process?
Step-by-Step Guide: Installing SQL Server 2005 Reporting Services 10/16/2006	Working with a new piece of technology can be intimidating. Not having the technology installed correctly can only lead to more difficultly. This guide outlines steps to properly install SQL Server 2005 Reporting Services and the current service pack to get you going in the right direction.
Dynamic Flat File Connections in SQL Server Integration Services 10/13/2006	In SQL Server 2000 DTS, creating a connection to an object is relatively straightforward, but limited. Making a connection to a file, particularly if you need a dynamic connection string, likely requires a global variable, a dynamic properties task, and ActiveX scripting. Using ActiveX scripts in DTS packages tends to slow the package down because the code needs to be compiled at run-time. In SQL Server 2005 SSIS a connection to a flat file is much easier and makes use of new programming techniques, making the package run more efficiently and smoothly.
Disaster recovery: What your SQL Server plan should look like 10/12/2006	There are several components to your disaster recovery plan. Enabled people, processes and technology are areas that rely upon one another. I'll discuss the level of importance all three have on a successful DR plan.
Finding SQL Server Agent Job Failures 10/12/2006	With most, if not all organizations, when executing processes during non business hours it is imperative to know if and when the these processes fail. At a minimum, full backups are typically executed off hours, but many organizations execute resource intensive batch processes which are critical to the core business for the subsequent day. With staff members stretched to meet numerous needs, validating that the jobs have executed successfully on a daily basis can be time consuming, but it is often better to know about the failures ahead of time rather than having the users notify IT. The typical solution to determine if a job fails is to notify a SQL Server operator, but SQL Server Agent mail is not always as reliable as needed. As such, in this tip we will provide scripts to determine recent SQL Server Agent Job failures.
Deleting Data: TRUNCATE vs DELETE 10/11/2006	There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although each achieves the same result, the methods employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.
Free Job Scheduling Tool for SQL Server Express and MSDE 10/10/2006	With the release of SQL Server 2005, Microsoft also released SQL Server 2005 Express Edition. This is the free version of the SQL Server engine that existed in prior releases under the MSDE namesake. While there were several great enhancements with SQL Server 2005, one great change was the release of SQL Server Management Studio Express a GUI based management environment for this free version of the database engine. Although having a GUI to manage this version is great, one thing that is still lacking is the ability to schedule jobs, since SQL Server Agent is not part of this version. If nothing else job scheduling is extremely helpful for scheduling backups.
Snapshot Isolation in SQL Server 2005 10/9/2006	Row versioning, although not specifically documented under that phrase, was around in previous versions of SQL Server but was reserved for maintaining data integrity (i.e., during UPDATE statements) and replication. In SQL Server 2005 Microsoft implemented row versioning as a method of gaining access to data, known as Snapshot Isolation Level is a means for read transactions to not block write transactions.
SQL Server Data Modeling Tools 10/6/2006	With the recent tip on the native SQL Server data modeling capabilities, questions arose about data modeling alternatives in the marketplace. The native tools are a very valuable free solution, but may lack advanced functionality needed in some circumstances. As such, this tip outlines the data modeling tools available for SQL Server which can be leveraged by DBAs and Developers to meet a variety of data modeling needs.
SQL Server 2000 to 2005 Crosswalk - DTS Package to SSIS Package Execution 10/5/2006	With the many changes from SQL Server 2000 to 2005, the Extraction, Transform, and Load process in many respects has changed the most. Even the name changed from Data Transformation Services in SQL 2000 to SQL Server Integration Services in SQL 2005. Not only did the product change, but so did the method for executing packages.
The NORECOMPUTE option of UPDATE STATISTICS 10/4/2006	Updating statistics is valuable for ensuring the SQL Server optimizer has the current statistical information to most efficiently process the query results. As a best practice, the UPDATE STATISTICS command should be issued on a regular basis to provide SQL Server with the most recent data. Automatically updating statistics is possible with the 'Auto Update Statistics' database configuration, but this could cause performance issues if a large table's statistics are updated during the middle of the day. To prevent this problem, the 'Auto Update Statistics' option can be disabled on a per database basis. This paradigm creates the need to rebuild all of the statistics in a manual manner. Does a happy medium exist to let SQL Server automatically manage the statistics on small tables and let me manage the statistics on the larger tables?
SQL Server 2000 to 2005 Crosswalk - The SQL Server Import and Export Wizard 10/3/2006	The SQL Server Import/Export Wizard is a way to either perform a one-time data import/export, or create a DTS/SSIS package that can be further developed. Because the Import/Export Wizard is a part of Data Transformation Services, it was re-written in SQL Server 2005, as was the entire SQL Server Integration Services process. Because of this, the methods of accessing the Import and Export Wizard have changed.
Improve Performance with SQL 2005 Covering Index Enhancements 9/29/2006	The concept of index creation has always been a tricky orchestration. To maximize performance and indexes by the optimizer, queries should be covered by an index-that is, the index should include all columns requested in the query. There are limitations to creating indexes that make covering queries difficult, such as a 900-byte size limit on indexes, a maximum of 16 columns can be named in the index, columns with data types like nvarchar(max) or text or ntext cannot be used in indexes.
BCP XML Format Files with SQL Server 2005 9/28/2006	One very common process that occurs in most SQL Server environments is the need to import and export data. With SQL Server 7.0 and 2000 we had DTS (Data Transformation Services) and now with SQL Server 2005 we have SSIS (SQL Server Integration Services). These are great GUI environments and give you a lot of flexibility to move data in and out of SQL Server. In addition to these GUI tools there is the tried and true BCP (Bulk Copy Program) that has been around for a lot longer then DTS or SSIS.
Auditing your SQL Server database and server permissions 9/27/2006	One of the things that DBAs need to be aware of is the permissions that are granted at the server level and at the database level. Using Enterprise Manager or Management Studio it is pretty easy to look at one object at a time, but what if you want to look at permissions you have granted across the board. Pointing and clicking is not so bad if you have a couple of objects to look at, but most instances of SQL Server have several layers of permissions that are granted. So what is the best way to get an overall look at your permissions that have been set?
Database access for SQL Server business and reporting users 9/26/2006	SQL Server business users and reporting users share a common goal: a successful business. Business users capture the line of business data critical to place orders, assist customers or schedule a service. Once that data is captured and the product or service is delivered, the role of reporting becomes of equal importance. Typically, reporting users focus on another portion of the business to determine business trends based on the data collected. Their reports reveal potential opportunities to capitalize on new business.
Using Identity Insert to keep table keys in synch 9/26/2006	One thing that DBAs are often faced with is moving data from one database to another database to populate lookup tables or some other key table in your database. This may be to keep a test or development environment in synch or maybe there is a need to populate like databases on other servers with the same data values. Another thing that is often common with SQL Server is the use of identity values or auto incrementing of a key value for new records as they get inserted. Using identity values is a simple way to make sure you have a unique primary key for new records, but there is no simple way to control what identity value will be given for a certain row in a table. When you combine these two items together, having an identity column as a primary key and having the need to push like data to other databases this is where the problems begin. If your tables on these different databases are setup exactly the same way where both tables have an identity value there is no way to control what identity value one table will get vs the other table and therefore you can have issues where the data is not in synch.
SQL Server 2005 tablediff command line utility 9/25/2006	One problem that DBAs often face is maintaining lookups tables across multiple servers or sites. These tables could either be replicated or manually updated, but in any case sometimes the data in these tables get out of synch. In a previous tip we talked about SQL Server comparison tools that allowed you to compare database objects or even the data itself between two different databases. These tools are great and definitely offer a lot of advantages, but there is a new tool in SQL Server 2005 that might help as well.
SQL Server Performance Monitoring Tools 9/22/2006	Being able to identify SQL Server performance issues at the drop of a hat is easier said than done. Without a means to collect and analyze the performance data it is difficult at best to understand and correct the items in a timely manner. SQL Server ships with a handful of tools to include Profiler, Sysmon\Perfmon and the Database Engine Tuning Advisor\Index Tuning Wizard. Much of the time these tools meet the needs for manual collection and analysis, but what if you need to go beyond the tools that are available and to resolve a performance issue quickly?
Cross Walk - SQL Server Query Plans 9/21/2006	With system performance on the mind of most business and technical teams (management, DBA, developer, network admin), determining the issues is the first major challenge. So once you have determined which queries are causing issues, now comes the time to determine how to improve the performance of the query while reducing the reads and writes. In SQL Server 2000 the primary interface was Query Analyzer's graphical interface where it was necessary to hover over the object to determine the statistics. Does SQL Server 2005 have a more elegant approach reviewing the query plans?
SQL Server 2005 Exposed = Data Modeling Tools 9/20/2006	Rarely do we (Developers and DBAs) have sufficient time for our projects and every day tasks. So when it comes to building a new system, inevitability what happens is time is limited and individual tables end up being built instead of a comprehensive data model. As the system grows, we hope that the front end code is correct and keeps the data related, but when you have a data problem it may take hours to check and validate that the data is correct. So how can we break this cycle and migrate from building table by table to a comprehensive data model?
Cross Walk - Returning XML Result Sets with SQL Server 9/19/2006	With XML being used in so many application and data exchange capacities, extracting XML from SQL Server should not be a challenge. Yet, some organizations are building complex applications to do so and overlooking some of the native SQL Server features. If the data tier has a viable option to extract XML in a native format, should that option be considered?
How and why should I use SQL Server 2005 synonyms? 9/18/2006	Changing the name of an table once an application has been deployed has traditionally been a difficult task in SQL Server 2000. The typical solution in SQL Server 2000 for referencing a different object was to use a View. A second option was to use the sp_rename system stored procedure to rename objects as needed. Unfortunately, this limits the capabilities of referencing other objects. The need to be able to reference another object can become critical during system migrations, application testing, data corruption, etc. Does SQL Server 2005 handle this in a more elegant manner and support more than just tables?
SQL Server 2000 Database Restores Mapping Users to Logins 9/15/2006	When restoring a database to a different server there is often the problem of matching up logins and users. The reason for this is that login information is stored in the master database and user information is stored in the specific database you are working with.The login information is stored in the sysxlogins table in the master database. This table contains a column that holds the SID (Security Identifier) which is tied to a specific login. A corresponding table sysusers is stored in each user database and uses the SID to determine if a login has database access. So even though you may already have the same logins on your new server the SID may not match. What makes this even more confusing is that you can see the actual names of the logins and users, so you would think they would automatically match up, but the SID is what is actually tying the security together not the name.
<-- Security Bulletin --> How do I secure an extended stored procedure? 9/14/2006	Extended stored procedures in SQL Server 2000 were the only option to extend the capabilities of the relational engine such as access to the file system or to send and receive emails directly in SQL Server. Securing these objects is not straight forward so few organizations have done so leaving SQL Server 2000 in an insecure state.
Database backup and restores: Corruption you haven't considered 9/14/2006	Some primary things that should be done on every SQL Server include daily backups and routine scheduled maintenance. Backups are common practice ensuring that if there is data loss you are able to restore it. Maintenance tasks, i.e. integrity checks ensure your database is running as smooth and clean as possible. In this tip, I'll explain the benefit of integrity checks and the process for fixing corruptions. You'll then avoid using corrupted backup files when restoring your database, a not so uncommon practice.
Simple script to backup all databases 9/13/2006	Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks. One of these tasks may be the need to backup all databases on your server. This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server. You could use Enterprise Manager to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.
Standardize your SQL Server data with this text lookup and replace function 9/12/2006	Have you ever had the need to replace multiple words in a data column within SQL Server with a new word or a new phrase for an entire table? A good example would be to update a product catalog with new words either for standardization or just to reflect a new name for the product. A simple technique would be to use the REPLACE command as outlined in this prior tip. This is great if you only need to update a few words, but what if you need to scan the entire product catalog and make several changes across the board, sometimes multiple changes for one data value?
SQL Server Comparison Tools 9/11/2006	There is often the need to compare both data and database structures from two databases either on the same server or on different servers. Most text editors have a built-in process to allow you to do a diff and identify any differences between the two files, but comparing data is not quite that easy. Using this diff process is great if you only want to compare a few files, but what if you need to scan your entire database to look for differences?
Who are the hosting companies that support SQL Server? 9/8/2006	Finding a hosting company that will meet all of your needs can be a complex task. Knowing where to turn and trusting that the hosting company will take the same level of care as you do is a challenge. This is balanced with the notion that the hosting company has the needed expertise and will remove the burden of managing the infrastructure in order for you to focus on core business needs.
SQL Server 2000 to 2005 Crosswalk - Index Builds 9/7/2006	Among many other changes between SQL Server 2000 to 2005, the index creation code has some subtle changes that are important to know and handle appropriately in scripts. It is important to be aware of these changes due to the beneficial performance gains with proper indexes. Check out the index creation differences between SQL Server 2000 and 2005.
SQL Server 2000 to 2005 Crosswalk - Database Maintenance Plan Wizard to SQL Server Integration Services (SSIS) 9/6/2006	For better or for worse, the most common tool for performing database maintenance in SQL Server 2000 is the Database Maintenance Plan Wizard, as opposed to the maintenance T-SQL commands. The Database Maintenance Plan Wizard's point and click interface enables backups, DBCC's, UPDATE STATISTICS, etc. to be configured quickly in order to be executed on a regular schedule. So how is this accomplished in SQL Server 2005?
<-- Security Bulletin --> How do I enforce SQL Server 2000 password changes? 9/5/2006	One of the simplest security best practices is changing passwords on a regular basis. For some organizations that could be quarterly while others may have a more aggressive policy. Regardless of the policy, the issue remains the same, how can I enforce SQL Server 2000 password changes for my logins?
Indexes made handy in SQL Server 9/1/2006	When starting an application, you should be able to identify many of the indexes based on a reasonable set of rules. As the application grows and changes, the indexes should be reviewed to ensure no good index candidates are overlooked. It should be based on how the application is used not based on theory. In the same light, make sure erroneous, duplicate or valueless indexes are removed. This is a precautionary measure to make certain your SQL Server does not have to manage unneeded indexes. In this tip we will identify index recommendations, index creation and index validation.
Alerting capabilities in SQL Server 2005 8/31/2006	Native alerts were valuable in SQL Server 2000, but at the same time they were unreliable in many cases due to SQL Server Mail's dependency on MAPI with Outlook. Many 'work-arounds' were created to improve the inherent functionality based on individual needs. SQL Server 2005 has alleviated this issue with the introduction of Database Mail. Thus far, this solution seems to meet industry needs for an SMTP based email system and has enabled the native SQL Server alerting capabilities to shine. In this tip we will outline the prerequisites for alerts, setup options and then highlight valuable alerts you'll want.
COPY_ONLY Backups with SQL Server 2005 8/31/2006	One issue with creating backups is that the LSNs (log sequence numbers) get stored in the backup files and these LSNs are referenced when you do a restore of your database. The reason the LSNs are stored with the backup file is to verify that the backup files are being restored in the proper sequence when you go to do your restores. So if you try to restore your database files out of sequence you will get an error message from SQL Server.
Recursive queries with SQL Server 2000 8/30/2006	In SQL Server 2000 there is no simple way to create recursive queries that have several levels of data (hierarchical data). Generally a recursive query is needed when you have a parent and child data stored in the same table. One example may be employee data where all employee data is stored in one Employee table and there is an indicator that specifies the employees supervisor that points back to another record in the same table. This is done quite often for other data as well.
Simple way to create table structures using an Excel spreadsheet 8/29/2006	When creating a new application that requires a data model to support it, the best approach is to use a data modeling tool to develop the logical and then physical data model. Although this is the best approach, not everyone does this for a full blown application let alone for a small utility application or even add on features to an existing database. The development process along with the database design becomes an iterative process where things are developed and then a certain milestone is hit and the database tables need to be modified to accommodate the change. Again, using a tool to assist with the data modeling is the best approach, but here is a simple approach to generate table structures, use them as they are and then regenerate them when a change is needed.
<-- SQL Server Security Note --> Windows Groups to Support SQL Server 2005 Applications 8/28/2006	Have you had an issue trying to find out how to assign rights for some of the new applications (SQL Server Integration Services, Full Text, OLAP, Express, Reporting Services, etc.) in SQL Server 2005? If so, you are not alone. With SQL Server 2005, some of the new security is assigned via Windows Groups that can be managed directly via Computer Manager. Unfortunately, these are no where to be found or configured in SQL Server 2005 Management Studio (SSMS) in the same light as database engine permissions.
How and why would I use multiple instances of SQL Server 8/25/2006	SQL Server 2000 and 2005 have the capability to use multiple instances of the database engine on a single server. But is going down this path really worth it? In some situations the answer should be a resounding 'yes'. So, how are they setup? When would I want to use them?
Specifying Max Degree of Parallelism for a Query 8/24/2006	The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs. This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel. MAXDOP is very beneficial in a number of circumstances, but what if you have a reporting like query that runs in an OLTP system that monopolizes much of the CPU and adversely affects typical OLTP transactions. How can you scale back the CPU usage just for this query?
Decrypting SQL Server database objects 8/23/2006	SQL Server offers a way to encrypt your Stored Procedures to make sure that prying eyes can not see what is going on behind the scenes. The problem with this method is that it is not a very secure way of encrypting the contents of your stored procedures. In addition, since SQL Server basically stores your source code vs. a compiled version most people rely on the code that is in the database server instead of moving the code to a source control application. Because of the need to access this code this tip outlines various methods of decrypting your encrypted database objects.
Restoring an existing database on the same server 8/22/2006	One task sometimes needing to be accomplished is that of restoring an older version of an existing database to the same server. It may be necessary for auditing purposes, restoring or recovering older data or even for reporting. In most cases you wouldn't use your production server,, but it could easily be the case for test or development servers.
Scripting Database Objects Using DMO (Distributed Management Objects) 8/22/2006	One of the nice things you can do with Enterprise Manager is script out all of your objects to a source file. You can then use these files to load your source control, make a backup of your database objects or use the scripts to load another database. The way this scripting is done is by using DMO (Distributed Management Objects). The scripting can be done with many different languages and it gives you control over doing repetitive tasks or making your own interface into SQL Server.
Testing Options with SQL Server 2000 and 2005 8/18/2006	Testing database applications for many organizations is a challenging task that is difficult to setup, automate and validate. Luckily, more time is getting allocated into development projects to conduct more rigorous testing. In this tip we will outline some of the realities to perform SQL Server 2000 and 2005 testing and opportunities to streamline the process.
<-- SQL Server Security Note --> SQL Server Agent Fixed Database Roles 8/17/2006	One mantra across the industry is the need to fine tune security for all infrastructure components. In SQL Server 2005, Microsoft responded with an almost overwhelming number of granular security rights which in some respects requires DBAs and Developers to think about SQL Server security in a brand new way. One key area that we are going to look at today is a few of the new security options for SQL Server Agent. SQL Server 2000 had some semi granular fixed server roles for the database engine but no roles specifically for SQL Server Agent. With SQL Server 2005 some of those same server role principals have been applied to the SQL Server Agent giving this portion of the architecture a big step forward.
Using Derived Tables to Simplify the Query Process 8/16/2006	Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output. Unfortunately there are problems with both of these approaches if you are trying to query data on the fly.
Server Info = SQL Server 2000 to 2005 Crosswalk 8/15/2006	When it comes time to review server related information from your SQL Server be sure to know how and where to access the information. With SQL Server 2005 some of the resources have changed and new resources have popped up. This tip outlines core server related information from SQL Server 2000 to 2005.
System Monitor (Perfmon) Counters for SQL Server 2005 8/14/2006	Capturing performance monitor counters is of great value to understand how SQL Server is behaving at a macro level, that being how overall resources are being used within the engine. Without this data it is difficult to determine where the performance issues are occurring. Capturing the metrics has been traditionally from Performance Monitor either on an ad-hoc basis or setting up a log to capture the values on a predefined basis. If you dug a little further in SQL Server 2000's system tables you probably found the dbo.sysperfinfo table in the master database. Unfortunately, this table has been converted to a view with SQL Server 2005 and dbo.sysperfinfo is only available for backward compatibility. Since this is the case, how can I capture the Performance Monitor values on an as needed basis with SQL Server 2005?
Track resource usage with SQL Server 2005 SQL OS 8/14/2006	To achieve optimal performance, SQL Server and Windows depend on one another, as does Windows and the hardware platform. A problem in any one area can severely impact the other two. Unfortunately, gleaning insight into SQL Server's hardware usage through Windows in any supported manner has been difficult, at least with older versions of SQL Server. Only a few commands were available to capture statistics, yet this information was needed in troubleshooting performance issues. To address these needs, SQL Server 2005 ships with the SQL OS a means to view key resource usage within SQL Server, in Windows and in the hardware platform.
Retrieving SQL Server Index Properties with INDEXPROPERTY 8/11/2006	In a previous tip we talked about the built-in function DATABASEPROPERTYEX. With this function you were able to find out information about the various database properties. SQL Server has other built-in functions that allow you to retrieve other data such as information about indexes. In prior tip there was a stored procedure that returns information about all of your indexes, but there is other index data that may be helpful to have when analyzing your indexes.
Sources for Database Information - SQL Server 2000 to 2005 Crosswalk 8/10/2006	Accessing SQL Server system information is necessary for administrative scripts and very important when troubleshooting particular issues. Unfortunately, in the transition from SQL Server 2000 to 2005, some of the objects that we have grown to rely on are no longer the recommended information source. In this tip we will outline core sets of data that need to be retrieved for databases and map the objects from SQL Server 2000 to 2005.
Finding and troubleshooting SQL Server deadlocks 8/9/2006	One thing that will you most certainly face at some time as a DBA is dealing with deadlocks. A deadlock occurs when two processes are trying to update the same record or set of records, but the processing is done in a different order and therefore SQL Server selects one of the processes as a deadlock victim and rolls back the statements.
Restoring from previous SQL Server versions 8/9/2006	With several SQL Server versions in use -- 4.2, 6.0, 6.5, 7.0, 2000 and now 2005 -- you will likely need to restore databases from a previous version to a later version. Microsoft made some of its most dramatic changes in version 7.0 in terms of how the database engine works and how data is stored. And that makes the restore path far from straightforward for SQL Server versions released prior to 7.0.
SQL Server Performance Statistics Using a Server Side Trace 8/8/2006	When troubleshooting a SQL Server performance problem, one of the tools to use is Profiler. This tool allows you to collect metrics on statements that are run on your SQL Server for analysis and troubleshooting. The problem with using Profiler is that it is a client tool and unless it is run on the server itself the connection may be lost and your trace stops. This usually happens right before the problem your trying to troubleshoot occurs and you don't end up collecting that valuable information you need.
SQL Server 2005 - Peer to Peer Replication 8/7/2006	SQL Server 2005 has a lot of new features and sometimes these new additions are overlooked as a new way of configuring and managing your SQL Server environment. One of these new features is Peer to Peer transactional replication. In the past, people have used transactional replication for load balancing, to keep a read only version for reporting purposes or possibly for a failover solution. With Peer to Peer replication all of these tasks are made much simpler, because all nodes act as both a publisher and subscriber.
Retrieving SQL Server Database Properties with DATABASEPROPERTYEX 8/4/2006	Finding out information about database settings is not that hard when you use Enterprise Manager or Management Studio, T-SQL commands such as sp_helpdb or query the system tables directly. But the way the information is returned may not always be the most useful. When using the GUI tools you can't easily grab just the information that you need and you can only look at one database at a time. With the T-SQL commands such as sp_helpdb the database options are all strung together and not listed out individually. And when querying the system tables, you often need to join multiple tables to get the results you need as well as convert the results to make sense of them.
Case Sensitive Search on a Case Insensitive SQL Server 8/3/2006	Most SQL Server installations are installed with the default collation which is case insensitive. This means that SQL Server ignores the case of the characters and treats the string '1 Summer Way' equal to the string '1 summer way'. If you need to differentiate these values and are unable to change the collation at the server, database or column level, how can you differentiate these values?
SQL Server 2000 to 2005 Crosswalk = Waitstats performance metrics 8/2/2006	From SQL Server 2000 to 2005, many of the core SQL Server system metrics have migrated from static commands to dynamic management views and functions that can be queried to gather statistics in real time. The new dynamic management views and functions offer a great deal of flexibility to troubleshoot system issues, especially for WAITSTATS where the the number of milliseconds are captured for threads that are waiting on resources to complete their current batch or query. This information is very valuable to gain an insight into how applications are using SQL Server and the cause and effect of waiting for resources (memory, CPU, disk, etc.). Armed with this information, it should be possible to better tune your applications for greater levels of concurrency and throughput.
<-- SQL Server Security Note --> BUILTIN\Administrators Group 7/31/2006	The principal of least privileges is a cornerstone to most security implementations. The premise behind the principal is to only grant users, developers, DBAs, network administrators, etc. the needed rights and nothing more. If additional rights are required, evaluate the rights and grant the access accordingly.
SQL Server Command Line Tools To Manage Your Server 7/28/2006	There are several useful commands and functions that are available in SQL Server, but not knowing what they are or where to find more information about them is sometimes a problem. Having these commands at your fingertips is very helpful when trying to solve a problem or for just doing general analysis on your database instances.
SQL Server 2005 Crosswalk - Max Degree of Parallelism for Index Commands 7/27/2006	In SQL Server 2000, the max degree of parallelism or MAXDOP option is a server wide configuration indicating the total number of CPUs that the optimizer can use in parallel to complete a process i.e. index creation. When creating an index, the MAXDOP configuration was based on the server configuration as well as the current workload of the server. Typically, the MAXDOP was equal to the total number of logical CPUs, because this was the default on a multiple processor machine whether the logical CPUs were derived from multiple physical CPUs, dual core CPUs or multi-threaded CPUs. Unfortunately, no MAXDOP options were available when rebuilding indexes or when using the DBCC DBREINDEX or DBCC INDEXDEFRAG commands.
Stored procedures: Who is running backups and restores and when 7/27/2006	Backup and restore commands can be issued by running T-SQL, Enterprise Manager or Maintenance Plans. However, it's still not easy to determine who issued backups and restores -- and when. Such information is stored in the msdb database; an entry is logged in msdb whenever a backup or restore runs. This data is not easy to access via the GUI tools, but it can be retrieved through queries. The following tables contain backup and restore information whenever one of these commands is issued.
SQL Server 2005 - Try Catch Exception Handling 7/26/2006	Error handling in previous versions of SQL Server was not the easiest thing to do. You could test the value of @@ERROR or check @@ROWCOUNT, but if the error was a fatal error you did not have a lot of options.
SQL Server 2000 Text Data Manipulation 7/25/2006	Sometimes there is a need to manipulate string values using T-SQL code. With other languages such as Visual Basic, C++, C#, VBScript, etc... there are a lot of commands at your finger tips to manipulate string values. With SQL Server 2000 you don't have all the same options, but there are enough commands that if correctly used together can result in the same functionality you get with other programming languages.
SQL Server 2005 - Crosstab queries using PIVOT 7/24/2006	In SQL Server 2000 there was not a simple way to create cross-tab queries, but a new option in SQL Server 2005 has made this a bit easier. We took a look at how to create cross-tab queries in SQL Server 2000 in this previous tip and in this tip we will look at this new feature in SQL Server 2005 to allow you produce cross-tab results.
Free SQL Server Troubleshooting Tool from Microsoft 7/21/2006	Getting insight into a SQL Server issue can be difficult without a comprehensive set of information. To first determine all of the configurations and information sources is difficult enough, but to capture the information in an automated manner on a regular basis can quickly become a major challenge. But, if you have an issue, this information is extremely valuable to determine changes to the system over time and their subsequent impacts.
Find and fix resource-intensive SQL Server queries 7/20/2006	Taming resource-intensive SQL Server queries is no small task. Finding them can be a challenge and fixing them is typically unique to the query. In this tip I will outline the means to identify resource-intensive queries in SQL Server 2000, as well as five common resource-intensive queries with possible resolutions.
SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package 7/20/2006	In SQL Server 7.0 and 2000 DTS was one of those freebies that did not catch on immediately, but when it did, the industry flooded to use the technology. As SQL Server 2005 was rolled out, one of the first items many SQL Server Professionals checked out was SSIS due to the reliance on DTS Packages in their SQL Server 2000 environment. Once their eyes rolled back into their heads from seeing Management Studio as a replacement for Enterprise Manager and Query Analyzer, they popped back out when reviewing the Business Intelligence Development Studio to build SSIS Packages among other sets of solutions (i.e. Reporting Services, Analysis Services, etc.) based on the rich feature set. With the many interface and development differences between DTS and SSIS, do not be surprised by the deployment differences either. In this tip we will outline the steps used to migrate DTS Packages and the steps necessary to migrate SSIS Packages.
SQL Server 2005 Exposed - CHECKSUM Functions 7/19/2006	Determining if two rows or expressions are equal can be a difficult and resource intensive process. This can be the case with UPDATE statements where the update was conditional based on all of the columns being equal or not for a specific row. To address this need in the SQL Server environment the CHECKSUM, CHECKSUM_AGG and BINARY_CHECKSUM functions are available in SQL Server 2005 to natively create a unique expression, row or table for comparison or other application needs. In this tip we will focus on the common questions related to the CHECKSUM code and provide an example to begin to leverage the CHECKSUM commands in your T-SQL code.
SQL Server 2000's DTS Authentication Alternative = UDL Files 7/18/2006	Many of the objects (Execute SQL Tasks, ActiveX Scripts, Connection Objects, etc.) in SQL Server 2000 Data Transformation Services (DTS) Packages require authentication to SQL Server when extracting, transforming and loading data. It is quick and easy to just add your SQL Server user name and password. Some even say 'better yet I know the sa password, so I will add it'. Wrong! In the short term embedding the user name and password may be quick and easy, but in the long term when passwords need to be changed or employees leave the organization, the password management problem quickly becomes unwieldy. The first alternative that is typically in the right direction is to use a trusted connection in the DTS objects when authenticating. This ensures that the user or service account that is executing the DTS Package has sufficient privileges to SQL Server and the underlying objects. Unfortunately, depending on the application calling (web server, legacy system, etc.) the DTS Package or the overall application architecture, a trusted connection may not be feasible.
SQL Server 2000 to 2005 Crosswalk - Database Fragmentation 7/17/2006	Database fragmentation is a hindrance to SQL Server performance because with excessive fragmentation more data pages need to be traversed to fulfill a query request. Luckily, fragmentation is a manageable problem that is resolved by re-building the indexes to reduce the fragmentation at the index or table level. Determining the tables that have high levels of fragmentation was a potentially time consuming process in the SQL Server 2000 environment because DBCC SHOWCONTIG had to be issued against the table or index. Then this data needed to be analyzed to determine which indexes required rebuilding based on your personal tolerance for fragmentation. Unfortunately, there was and still remains little agreement on the threshold when indexes should be rebuilt. Some DBAs use a threshold in the 90% range while other DBAs were closer to the 70% range, but it seems that DBAs agree beyond the 70% fragmentation range that the indexes should be rebuilt.
SQL Server 2005 replication enhancements and discontinued features 7/17/2006	SQL Server replication has become a core infrastructure component for many organizations and a mainstay that allows DBAs to quickly and easily move data. Unfortunately, replication has not been given its fair share of attention, compared to many other new SQL Server 2005 enhancements. In this tip, we will outline some of the new SQL Server replication features.
SQL Server 2005 Exposed - Log File Viewer 7/14/2006	System and application log data is essential when troubleshooting a new or complex SQL Server issue. Unfortunately, in SQL Server 2000 this was a bit cumbersome because critical data had to be reviewed from multiple logs and from different interfaces. Determining the sequence of events among the multiple logs became tedious when comparing the data across multiple sources and in different formats. The complexity grows when the logs have thousands or tens of thousands of entries with limited filtering capabilities to only review the needed data for the issue at hand.
Database mirroring setup in SQL Server 2005 7/13/2006	One of many new features in SQL Server 2005, database mirroring allows you to automatically mirror database contents from one SQL Server database to another. It also offers failover capabilities, which may be made automatic depending on how you configure mirroring. The mirrored copy is a standby copy that can not be accessed directly. It is used only for failover situations. With SQL Server 2005 Enterprise Edition you can also use database snapshots with the mirror, but we will cover that another time.
SQL Server 2005 - Enabling xp_cmdshell 7/13/2006	Sometimes there may be a need to run some external processing from within SQL Server. So to make this task easy for both DBAs and developers Microsoft has a built-in extended stored procedure called xp_cmdshell. With this extended stored procedure you have the ability to run any command line process, so you can embed this within your stored procedures, jobs or batch processing. In SQL Server 2000 this option is enabled by default, but to limit access to using xp_cmdshell only members of the sysadmin server role have default rights. To extend rights to other users you can use the GRANT statement to allow execute rights.
SQL Server 2000 to 2005 Crosswalk - Index Rebuilds 7/12/2006	Index rebuilds are a core component for database maintenance which ultimately improve performance and the user experience with the application. DBAs and Developers alike have become accustom to much of the syntax in SQL Server 2000 and know the purpose for each set of code. In SQL Server 2005, the original syntax to support index maintenance is still supported, but will be removed in a future version of SQL Server. As such, now is the time to get up to speed on the new syntax to start to incorporate this code into your environment.
How do I find a local SQL Server user group? 7/11/2006	Researching SQL Server information on the internet and in books is very valuable and certainly good means to learn. But learning from other people in the same boat is extremely valuable. Most times the same issues have been encountered and you can cut to the chase and point to the issue on your screen. One means to learn about SQL Server in your community is via a user group. Unfortunately, many DBAs and Developers are not aware of the local user groups that are right around the corner.
Managing SQL Server 2000 Transaction Log Growth 7/10/2006	We have been running SQL Server 2000 for a few years and the transaction log file has become very large for some of our databases. In some circumstances, the transaction log is a more than 5 times larger than our database. How can I reduce the size of this file?
SQL Server - Performing maintenance tasks 7/7/2006	With any database systems there are a couple of key items that help with performance. These items include, good database design, properly written SQL code, correct indexes, current statistics, defragmented indexes and data.
Free SQL Server tools from Microsoft 7/6/2006	There is often the need to do something with SQL Server that does not necessarily come shipped with the product. Thanks to Microsoft there are plenty of additional free tools that can be used to enhance your SQL Server environment.
SQL Query Analyzer Shortcuts 7/5/2006	Using Query Analyzer is a great tool for testing queries, running ad hoc queries, troubleshooting queries etc... There is a lot of functionality as well as a lot of shortcuts built into the tool, but finding the shortcuts or remembering them is sometimes not that easy.
SQL Server 2005 - Database Snapshots for Reporting, Auditing and Data Recovery 6/30/2006	Sometimes there is need to have a static set of data from your database. The need may be for reporting, auditing or even recovery of data. This can be pretty easily done using SQL Server backups and restores, but the time that it takes to perform the backup and restore task can be quite long depending on the size of the database. If the need is to only periodically create a static set of data, then this approach will probably work for you. But what if you need to create a static set of data every day or if you need to do it multiple times a day for some type of reporting or auditing. To handle this approach you could do full and transaction log backups and restores, but there is still a lot of overhead to run these processes.
SQL Server Randomly Retrieve Records 6/29/2006	Have you ever had the need to randomly retrieve a set of records from a database table to display to users? One way of doing this is to use the RAND() function to generate a random number and then tie this back to your original record set.
SQL 2000 to 2005 Crosswalk - Startup Parameters 6/28/2006	SQL Server offers the ability to use startup parameters when your SQL Server instance starts. The default items include, location of master database data file (-d), location of master database log file (-l), location of SQL server error logs (-e).
SQL Server statements currently running with fn_get_sql 6/27/2006	If you have ever had the need to troubleshoot current processing that is occurring on your SQL Server there are a few different ways to see what is going on.
SQL Server DTS command line utility 6/26/2006	Have you ever wanted to or needed to run a DTS package from outside of SQL Server? This could be from an application you develop or even just from a command line. Well SQL Server gives you the ability to run a DTS package by using the DTSRUN command in your applications, but in order to get this to work properly you need to construct the DTS command correctly. This could be pretty tricky unless you are very familiar with the command syntax. Luckily Microsoft included a tool to allow you to construct the command using a GUI.
SQL Server 2005 Backup Product Options 6/23/2006	For DBAs, backups are one of the top operational items on their mind. Why is that? Why do backups get so much attention at every company? The simple answer is that backups are the core to all disaster recovery solutions. For some companies backups are the first and last line of defense and for others backups are the absolute last line of defense and are used when the remainder of the high availability options are not feasible. Couple this with the critical role that SQL Server databases play in the organization and backups are not only important to the DBAs and IT, but to the entire organization at critical points in time.
SQL Server 2005 Exposed - New DDL Auditing Capabilities 6/22/2006	With the growing need to audit activity in your database servers there is a need to figure out the best approach to collect changes on your system. There are several requirements these days such as Sarbanes Oxley, SAS70, GLBA, etc... that require you to keep a close eye on your database activity. With SQL Server 2000 you were limited to auditing login information and capturing data changes using DML (Data Manipulation Language) triggers, but there was no easy way to track DDL (Data Definition Language) changes in your database. As always there are third party tools that simplify the process, but most of the time the initial look is at what can be done natively with SQL Server.
SQL Server 2005 Exposed - Dedicated Administrator Connection 6/21/2006	In years and versions of SQL Server gone by, at times it was impossible to gain access to SQL Server when the server had a spinloop, maxed out resources or severe corruption. No matter how you tried to gain access to the server (i.e. Enterprise Manager, Query Analyzer, OSQL, ISQL, a third party IDE, etc.) it was not possible. With pressure mounting to get SQL Server back online a last resort was to reboot the server. At times, SQL Server was able to checkpoint the databases and shutdown gracefully other times your were not so lucky. No matter how patient you were with SQL Server on the shutdown and subsequent startup, the roll back process was long and at times yielded database corruption or access violations.
How does Enterprise Manager get its data 6/20/2006	Have you ever wondered where Enterprise Manager gets its information from and how you can retrieve the same data without using Enterprise Manager? Well it's not a big secret. Most of the information that is displayed in the GUI is based on information gathered from queries, but what queries? If you have ever looked at the system tables to try to figure this out, it is not as straight forward as you think. So how do you find out what queries Enterprise Manager is using?
Online restore feature in SQL Server 2005 6/20/2006	Microsoft has added its new online restore option to SQL Server 2005, which allows you to restore a backup while the database is still online. In the past you needed exclusive access to the database in order to restore, but that's not the case with SQL Server 2005. Before you get too excited about this new feature, there are a couple of things to note: (1) This option only exists in the Enterprise and Developer Editions and (2) You can only restore at the filegroup level.
Step-by-Step Guide: Using Copy Database Wizard to migrate from SQL Server 2000 to 2005 6/20/2006	A common scenario will be the migration of a database from a SQL Server 2000 server to a SQL Server 2005 server. As it pertains to upgrading a single database and its objects, I think the Copy Database Wizard will be a popular option for small to mid-sized databases because it can include dependent objects (logins, DTS packages, jobs, etc.). As such, this guide will outline steps to migrate from SQL Server 2000 to 2005 by using the Copy Database Wizard method. At a high level, this tool creates one or more SQL Server Integration Services (SSIS) packages to complete the migration either by detaching and attaching the database or using SQL Management Objects (SMO) to move the data.
Index Analysis Script - All Indexes for All Tables 6/19/2006	Sometimes there is a need to review all your indexes across your entire database. The need might be for simple analysis, optimization or maybe just for documentation purposes. There are several built in utilities in SQL Server that allow you to take a look at your indexes, but none offer a simple approach to list all indexes for all tables.
Speed up the SQL Server 2005 migration process 6/16/2006	It just figures that when you have a large database you have little down time to upgrade, but for the small databases you have an entire weekend to complete the process. This gives you plenty of time to double check the output and test the application with little or no pressure. Insufficient time to upgrade is a significant issue that at times is difficult bordering on impossible to negotiate with the business and user community. They certainly understand the upgrade to SQL Server 2005 is needed, but are also well aware of the business implications for down time.
Cross tab queries with SQL Server 2000 6/15/2006	In SQL Server 2000 there is no simple way to create a cross tab query. Generally crosstab queries are only used for reporting or if you want to display some information in a grid on an application screen. Other then displaying or reporting on data there isn't a real need for this type of query. But when the need arises for either one of these reasons, there is no simple way of doing this in SQL Server 2000. This may come as a big surprise to people that have been doing this in Microsoft Access for quite some time. With Microsoft Access there is a built in option to produce crosstab queries, but not so with SQL Server 2000.
Developing with SQL Server 2005 Express 6/14/2006	Who ever imagined Microsoft would be offering a free version of its enterprise database management platform? From the original SQL Server 2000 MSDE to the current SQL Server 2005 Express edition, we never before had a fully featured Management Studio to build and deploy SQL Server database -- until now. Couple SQL Server 2005 Express Edition with the Visual Web Developer 2005 Express Edition or the Visual Basic 2005 Express Edition and developers get an unprecedented free tool set.
Websites that focus on SQL Server 6/14/2006	Time is of the essence when SQL Server is down or when pressure is mounting from multiple project deadlines. So finding accurate information quickly is difficult at best. When you are not sure where to turn for valuable information stress levels only increase.
How do I perform post installation tasks for SQL Server 2005 Reporting Services? 6/13/2006	During the SQL Server 2005 Reporting Services installation it is possible to complete the installation without completing the configuration. This may be needed if for example the encryption key is unknown at the installation time or if it is updated at a later date.
Database Backup and Restore Failure Notifications 6/12/2006	My backups and restores fail infrequently, but when I find out it is too late. How can I find out sooner?
SQL Server Column Encryption 6/9/2006	When the most valuable component in your environment is storing sensitive data, doing so in clear text and relying on other security mechanisms to protect the data has come and gone. As companies begin to use SQL Server for all their mission critical applications, data encryption has becomes a feature that is often needed to protect the data.
Configure RAID for maximum SQL Server I/O throughput 6/7/2006	The name of the game is I/O throughput. Running backup and restore sessions is one of the most I/O intensive activities you will perform on your SQL Server. That's because when you run a backup and/or restore you are reading the entire contents of the database and then writing the entire contents of the database or vice versa. The one big difference between that and executing queries is that the system reads the database file sequentially rather than in a random fashion, making it a much more efficient process. Because such a large amount of data needs to be accessed and written, the best approach is to split up your I/O activity as much as possible.
SQL Formatter Tool 6/7/2006	One of the things that drives me crazy is having to format my SQL statements. It is hard enough writing the statements in the first place, but then having to take the time to format the code, make keywords uppercase, using tabs to align column names, tables names etc... is just a lot of work. But it is one of those things that if you don't do it now it never happens except for the next time that you edit that code and spend a bunch of time formatting it anyway.
SQL Server 2000 and 2005 Certification Resources 6/6/2006	Answering the question 'Why should I get certified?' yields many different answers. This is certainly the case for current versus aspiring DBA's. Some responses are personal and career driven while others are related to the excitement of learning new technologies. No matter how you slice it the drivers and inherent benefits are there, although perception of value of the certification differs from person to person as well. For example, one perception with the SQL Server 2005 track is that the tests are a better representation of reality than previous certifications. Another important factor to take into consideration is the time and financial investment because this too can be costly. So, weigh your options and see if your employer can help you achieve this goal or see if this is something you can chip away at slowly, but surely.
Backup to multiple files for faster and smaller backup files 6/5/2006	Have you ever wished you could get your backups to run faster? Well there may be a way, by writing to multiple files. Creating SQL Server backups is pretty simple to do; you can use the SQL Server management tools or you can use T-SQL commands to issue the backup. But sometimes with large databases it takes a long time to run and it is difficult to copy this very large file across the network or even to your backup tapes.
Hardware considerations for simpler SQL Server failovers 5/25/2006	Failing over to another system in a disaster recovery situation is the last thing you want to do. Although you won't have to do this often, you may need to eventually -- and it's better to be prepared than not. You may assume that the simpler the implementation the simpler the failover process will be. From a hardware perspective that may be the case, but from a SQL Server performance and high availability standpoint, simple is probably not the way to go. In the following tip I'll describe what a simple SQL Server hardware implementation would look like and why that may be an ideal configuration for failover but not for performance and availability. I'll also discuss failover methods. Use the following table of contents to navigate this tip.
Reporting Services tool set in Management Studio goes real-time 5/23/2006	In SQL Server 2000 Enterprise Manager, the Task Pad view was really the only native-reporting interface available and it was limited to database and object-related information. In SQL Server 2005, Microsoft integrates Management Studio and Reporting Services to provide snapshots of real-time performance and database metrics.
Checklist: Getting ready for SQL Server 2005 5/16/2006	If you haven't done much yet with SQL Server 2005, the time has come. It has been six months now since Microsoft released the new DBMS to manufacturing (RTM), and a month since it released its first service pack -- yet many people haven't taken the time to prepare or even think about the upgrade process.
Checklist: Archiving SQL Server backups 5/11/2006	Archiving database backups is probably relatively low on most DBA to-do lists. Unfortunately, it's one of those things that has to be done. Whatever the driver may be, good or bad, I will discuss some points to consider when archiving your database backups for long-term storage. In most cases, long-term archiving is not the DBA's responsibility, but someone in the organization needs to make sure it happens. So you should be aware of the process in place and ensure all of the pieces you need are there when you need to recover an archived database.
Using SQL Server Profiler with Analysis Services 5/2/2006	Analysis Services was originally introduced with SQL Server 7.0 as the core backend technology for Microsoft's business intelligence offering. Since the original release, the overall business intelligence offering has improved immensely for both the presentation and processing layers. Unfortunately one area that has been a mystery is the internal processing and tuning of Analysis Services. Most technical folks accepted the fact that the shear volume of data supported by Analysis Services equals long running queries. For this reason, beefy SQL Servers were required to support a small segment of the user community as compared to line of business applications.
Step-by-Step Guide: How to interpret performance metrics in SQL Server 2005 4/27/2006	SQL Server 2005 Profiler allows you to associate Profiler data and Performance Monitor metrics in one interface. Profiler is the native SQL Server tool used to capture SQL Server transaction performance metrics. Performance Monitor is the native tool to capture macro-level SQL Server and Windows metrics. One major performance-tuning challenge has been to determine the affect of a single SQL Server transaction or set of SQL Server transactions on overall Windows metrics (i.e., processor, memory, I/O) for the entire server.
Testing SQL Server restores 4/20/2006	Testing is key to ensuring a process actually works when it comes time to implement, rather than finding out when time may be of the essence. This holds true for testing SQL Server restores as well.
Configure SQL Server 2005 TempDB for performance 4/18/2006	Properly configuring TempDB in SQL Server 2005 is imperative with some of the system database's new responsibilities. With SQL Server 2005, TempDB is responsible for managing temporary objects, row versioning and online index rebuilds. Some of this processing has moved from the transaction log in SQL Server 2000 to TempDB in SQL Server 2005. As such, configuring this database can have major performance implications.
SQL Server 2005: When and how to use Database Snapshots 3/28/2006	SQL Server 2005 offers many features that did not exist in previous versions of the product. One such feature is Database Snapshots. Database Snapshots allows you to create a read-only copy of your database that can be used for other purposes, such as reporting, auditing or recovering data. Currently, you can do that by running a backup and restoring it to another database. However, the big advantage you gain by using Database Snapshots instead is the speed at which a snapshot occurs, as well as the ability to create multiple snapshots at different points in time quickly.
Step-by-Step Guide: Planning your migration to SQL Server 2005 3/23/2006	Ladies and gentleman, start your engines! With all of the new features in SQL Server 2005, the race is on and adoption is in full swing. Many organizations are leveraging these features to ease current issues or gain competitive advantage in the marketplace. So how are DBAs accomplishing this task? In this tip I will outline technical options to consider when migrating to SQL Server 2005.
Restore basics: How to restore using T-SQL commands 3/16/2006	Another approach to SQL Server backup and restore, aside from using Enterprise Manager as discussed in my previous tip, is to use T-SQL commands. Enterprise Manager can be a quick and easy way to run backups or restores, but T-SQL offers a lot more flexibility. With T-SQL you can script your backups or script to restore several backup files.
Tracking query execution with SQL Server 2005 Profiler 3/9/2006	A single transaction in SQL Server is the lowest common denominator for performance tuning. When it comes to capturing and analyzing data at this level, the best native tool is SQL Server Profiler. Analyzing the results from SQL Server Profiler enables you to discern a great deal of information about your SQL Server; the collected data reflects the actual processing on the server, not just theory or conjecture.
SQL Server Integration Services: Simplify database maintenance 2/22/2006	SQL Server took a leap forward in database maintenance functionality with the introduction of SSIS (SQL Server Integration Services) in SQL Server 2005. The SQL Server Development Team at Microsoft adapted SSIS to offer point-and-click database maintenance, offering vast improvements over DTS (Database Transformation Services) and SQLMaint in SQL Server 2000. The Database Maintenance Wizard still exists to build the initial process, but the modifications are all made to an SSIS package stored locally on the server.
SQL Server 2005 Database Mirroring primer 2/21/2006	Since SQL Server 2000, you have been able to create a standby server using replication, log shipping and backup and restore -- but now Microsoft has introduced a built-in tool that enables automated failover. Database Mirroring is a new feature in SQL Server 2005 that allows you to mirror database contents from one SQL Server to another SQL Server. It also enables you to failover to the mirrored database in case of a failure.
Indexing Guide: The art and science of SQL Server indexing 2/14/2006	You can improve your SQL performance by using indexes. But you have to choose the proper indexes and make sure the ones you do choose fit into your business situation. In this indexing guide, you will find answers to common indexing questions, indexing dos and don'ts, and tricks for working with the Index Tuning Wizard to improve overall system performance for SQL Server 2000 servers. This is particularly useful for servers that need a boost before they are upgraded to SQL Server 2005.
Checklist: Maximize SQL Server backup performance 2/6/2006	The following steps will help you improve the performance of your SQL Server backups. Not every option will be right for your environment, but using as many of these best practices as possible will significantly improve your backup performance.
SQL Server Clinic: Stop locking and blocking 1/30/2006	Locking is a necessary component of the SQL Server engine to properly ensure transactions are processed accurately with high levels of concurrency. Unfortunately, those locks can quickly become unwieldy and reduce SQL Server from a high concurrent database to a single-user machine with the click of a button. Now that is contention. So how would you go about identifying, resolving and preventing such locking issues? In this SQL Server Clinic, I'll walk you through a typical locking example and the steps necessary to address the problems.
Restore basics: How to restore SQL Server using Enterprise Manager 1/28/2006	You probably already have a process in place to back up SQL Server databases on some set schedule, but what do you do when you need to recover a database? Database Maintenance Plans make it easy to automate the backup process, but there is no equivalent for restore. To restore a database, you can either use Enterprise Manager or T-SQL commands. I will explore the Enterprise Manager command in this tip and T-SQL in a future tip.
Database Engine Tuning Adviser: How to tune your new SQL Server 2005 1/24/2006	Business and IT teams alike are demanding high-speed SQL Server 2005 installations right out of the gate. Getting the DMBS up and running is not usually the greatest challenge; it's ensuring that the platform performs as expected. This is no small task when you already have little time available to plan for a system already supporting production users or the platform was inherited from another department and is now your responsibility.
SQL Profiler: Features, functions and setup in SQL Server 2005 1/16/2006	Trying to find the proverbial needle in the haystack of your SQL Server transactions is no small task. SQL Server Profiler not only helps you find that needle, it gives you details on all the other needles in a single interface. Profiler is truly the best native SQL Server resource to understand micro-level processing occurring on any SQL Server. The data captured by this graphical event-monitoring tool is as valuable to a DBA as the business-related data captured by SQL Server Analysis Services is to analysts.
How to restore from a transaction log 1/12/2006	Having a backup plan in place is half the battle in keeping your SQL Server up and running. The other half involves the restore process. You need to restore SQL Server after a failure, when you need to restore to a standby server or when refreshing a reporting or development environment. All of these scenarios can involve restoring transaction log backups.
SQL Server performance tuning myths 1/11/2006	Your SQL Server is growing and performance is quickly degrading. What's your first reaction? Throw more hardware at the problem! Wrong. The one big myth that many people share in the SQL Server community is that throwing more hardware at a SQL Server performance problem will resolve the issue. This is simply not the case. In this webcast, Edgewood Solutions' Jeremy Kadlec will talk about how to resolve this myth and many SQL Server performance tuning worst practices.
Ready, set, go! SQL Server performance-tuning checklist 1/3/2006	What do I need to do? There is so much, where do I start? What should I do next? Wow, my SQL Server is flying! If you start out not knowing where to go and end up with an exceptionally performing SQL Server, you made it. Take a look at this SQL Server performance-tuning checklist to achieve high performance.
Managing backups for a large number of SQL Servers 12/15/2005	Large SQL Server installations bring added challenges, particularly how to back up all those databases and ensure minimal data loss in the event of a failure. Here I'll detail some points to consider when setting up a backup plan for a large SQL Server environment.
Step-by-Step Guide: Hunt down SQL Server performance problems 12/12/2005	Finding the root cause of a SQL Server 2000 performance issue can be difficult to say the least, but not knowing where to start and what to look for increases the difficultly tenfold. Here I will outline many common SQL Server 2000 performance-tuning issues and offer suggestions on how to address them with free tools and code.
Checklist: How to maintain an effective SQL Server DR strategy 12/5/2005	When most people think of disaster recovery, their initial thoughts are about recovering from a complete disaster like Hurricane Katrina. In reality, this type of disaster is rare and often hits specific areas prone to earthquakes, hurricanes, tornadoes and other types of natural phenomena. Although it is good to be prepared for a complete disaster, the likelihood of needing this type of recovery is rare.
How to store database backup media 11/22/2005	To date, most backup and recovery tips talk about best practices for database backups, checklists to follow and troubleshooting backup problems. Usually this is where the discussion stops, and not much is written about what to do with all of these backup files for long-term storage. There are probably many reasons for that, and among them is one simple explanation: DBAs are not usually responsible for long-term tape storage. However, they are responsible for getting databases back online, retrieving old data for auditing and replacing lost data.
SQL Server 2005: To be or not to be an early adopter 11/1/2005	Ready or not here it comes! The long-anticipated release of Microsoft's latest installment to SQL Server is finally here. Previously known as Yukon, SQL Server 2005 is slated for release on Monday, Nov. 7, 2005. Several versions of this product were made available through beta programs and community technology previews (CTPs) over the past several years. But even with all the advanced notice and pre-release version availability, many CTOs still aren't aware of the benefits and risks this new product offers and many DBAs still aren't ready to invest their time and energy into pushing for it.
SQL Server 2005: 10 steps to justify and plan your upgrade 10/31/2005	In light of the impending SQL Server 2005 launch, many organizations are considering the upgrade but they don't know where to start or how to get from point A to point B in a timely manner. This tip outlines the top 10 high-level steps to get you on the right path to a SQL Server 2005 upgrade.
Top 10 new features in SQL Server 2005 10/27/2005	In the business world, everything is about being "better, faster and cheaper" than the competition -- and SQL Server 2005 offers many new features to save energy, time and money. From programming to administrative capabilities, this version of SQL Server tops all others and it enhances many existing SQL Server 2000 features. Here I'll outline the 10 most significant new features in order of importance:
Change management controls for SQL Server - Expert Webcast 10/25/2005	Change Management for your SQL Server environment is not an easy task without a formalized process. It takes a combination of people, processes and technology to successfully implement a viable solution. Without these three areas your change control process can quickly breakdown. The session will look at these three areas and focus on specific roles, formalized procedures and existing technology solutions.
Step-by-Step Guide: How to properly back up a SQL Server 10/25/2005	Like any IT project, having a good plan is always the best approach. Here is a guide for the things to consider when putting together a SQL Server backup process for your company. It is broken into planning, development and testing, and implementation. Consider the items as an overall technique to backing up all of your servers. It is always better to have a standard approach instead of doing something different for each of your SQL Servers.
What's new with T-SQL in SQL Server 2005 10/19/2005	In preparation for the SQL Server 2005 launch this November, I recently conducted a survey of Edgewood Solutions' customers, asking them what they consider to be the most valuable features in Microsoft's new database release. One common response across all positions, industries, company sizes and years in business was T-SQL enhancements. These findings serve as a basis for the following tips on Transact-SQL programming extensions.
Backup and recovery enhancements in SQL Server 2005 10/17/2005	As with any new software release, you'll often find new features you didn't know existed or you didn't know you needed. Many backup and restore features that exist in SQL Server 2000 will remain the same in SQL Server 2005, but there are several new enhancements worth your consideration.
Troubleshooting SQL Server backup and restore dilemmas 10/4/2005	Running backups and restores is a pretty straightforward process, but issues do arise on occasion. This tip is a list of five common problems and steps to help you resolve them.
Query Analyzer: 10 tricks for simple querying 9/29/2005	Back in the heyday of SQL Server 6.5, I remember writing many T-SQL statements in Enterprise Manager. Such was the case until Enterprise Manager locked up for the first time and I became a firm advocate for isqlw.exe. Also known as Query Analyzer, this tool has been my development and administration utility ever since.
Worst Practices for SQL Server Backup and Recovery 9/6/2005	Having a reliable backup and recovery process is key to protecting your data when things go bad. Several tips have been written that explain the key components in setting up a backup solution, but the following highlights some of the common worst practices that I often come across when evaluating a company’s backup and recovery practices. Sometimes it is the worst practices that make more of an impact then the best practices. Take a look at what you should not be doing along with recommendations on rectifying these worst practices.
Automate SQL Server Restores to Refresh Test Environments 9/1/2005	You most likely refresh a development or test environment frequently with a recent production environment backup. Depending on what you are testing or developing, current data may be critical to ensuring your results are valid. Creating an automated task to restore the database regularly (weekly, daily or even several times a day as needed) will save lots of time and guarantee that you do not miss any necessary restore steps.
SQL Server Normalization Rules You Must Follow 8/30/2005	In this tip we will take a look at database normalization and the advantages and disadvantages of normalization for SQL Server databases.
Solving SQL Server 2000 index fragmentation 8/25/2005	When it comes to raw database performance, one of the biggest performance gains can be achieved through beneficial indexes. The indexes improve access to the data so you don't have to scan an entire table, a costly endeavor in terms of CPU, IO and memory usage. Over time, indexes can become fragmented, yielding lower then optimal SQL Server performance, longer transaction times, blocking, locking and lower throughput.
SQL Server's DaVinci code for data modeling 8/11/2005	If a picture is worth a thousand words, then a free data modeling tool is worth a terabyte of data. That is just what you get with the SQL Server 2000 DaVinci data modeling tools. SQL Server 2000 Enterprise Manager has a number of hidden goodies, and the DaVinci tools seem to elude more DBAs and developers than just about any other feature.
Using SQL Server Profiler to capture performance baseline 8/4/2005	Information from Performance Monitor information can assist in troubleshooting from a macro level, SQL Server Profiler can be used to capture a typical set of processes from one user on your system to replay the transactions if an issue is occurring or following a change to the system.
Develop a performance baseline 7/28/2005	Rather than release software and hope no issues occur, a performance baseline would be a much less stressful option. A performance baseline is a set of metrics to determine the performance of the SQL Server during normal circumstances. You would use it to determine if the system is performing in accordance with the baseline or if the system resources are exceeding the baseline and require your attention regarding the performance issue. Since no system is stagnate, the performance baseline should be recalculated for accurate comparisons after each major change is made to the system, whether it's a change in software, hardware, number of users and data, processes and so on.
Top 10 Performance Monitor counters 7/28/2005	Performance Monitor offers a simple means to collect macro-level performance statistics on a single SQL Server. If the SQL Server is reported by the users as experiencing an issue, Performance Monitor can be started to determine how the SQL Server is performing relative to the baseline. So, it is important to capture the baseline after each change is made to the system (hardware, software, and so on) or at least every three months to have relatively recent statistics. Below are 10 recommended Performance Monitor counters to help you capture a baseline and use as a comparison when you are researching a system issue.
10 Steps to Faster Backups 7/19/2005	Running SQL Server backups is one process that can take a long time. With databases continuing to grow, the time it takes to run backups also continues to grow. At one point, a 100 GB database was considered a very large database. Today it's the norm, and many databases are beginning to surpass the terabyte range. We will look at a couple of ways to achieve faster SQL Server backups.
Performance tuning freebies to add to your favorites 7/14/2005	I recently spoke on SQL Server performance tuning at CFUNITED, a conference focusing on Cold Fusion, where I found a large number of developers who use SQL Server every day. In my presentation, I offered a variety of free tuning tools that attendees were not aware of. Here I'll share the list with you and provide more detailed tips in the future.
Optimize SQL Server hardware performance 7/7/2005	Tuning SQL Server for performance is in many ways like constructing a house. You must find a suitable location with rich soil, build a solid foundation to support two or three levels, install the electrical and plumbing, finish the walls for painting and decorating, and finally conduct ongoing maintenance.
Restoring a Database From Another SQL Server 6/29/2005	There are many reasons that you would want to move a database from one server to another, disaster recovery, refreshing a test environment, data analysis or maybe something else. Restoring the database is the easy part, but then you may be left with mismatched logins and database users. In this tip we will look at some of steps to go through when restoring a database from a different server.
SQL Server performance-tuning worst practices, part 2 6/21/2005	In late March 2005, the first installment of SQL Server Performance-Tuning worst practices was released, and people in the community responded to the article with other worst practices they have observed. In this installment, we will explore some new observations and provide recommendations as well as share some additional experiences from members in the community.
Unite people, processes and technology for performance tuning 5/31/2005	All too often, organizations attempt to resolve performance issues with a quick fix or magic bullet to keep the business moving ahead of the competition. The perception is that there is no time for planning, designing or testing a solution; you have to move from problem identification to immediately implementing the solution. Unfortunately, the reality is that accurate solutions derived in this manner are few and far between.
Checklist: Backing up SQL Server 5/24/2005	The key to a successful recovery in the event of a database crash -- or worse yet -- a system crash, is to have the key elements for SQL Server backups in place. SQL Server offers many options for performing backups and the following checklist highlights some of the more important options to think about.
Checklist: Load testing - the magic bullet for SQL Server performance tuning 5/5/2005	Few things are worse than rolling out a new application and its performance is so bad that it brings the business to a screeching halt. This is not a new phenomenon; it's a reality that I have seen from time to time. I am willing to bet that you have experienced it, too. So what is the solution for preventing these performance problems?
SQL Server Backups Made Easy Using Maintenance Plans 4/29/2005	If you're new to SQL Server and not sure how to set up backups, Maintenance Plans may be the way to go. Although not all of the backup functionality is available using Maintenance Plans, Microsoft has developed an easy interface to set up backups for all of your databases.
SQL Server OLTP vs. data warehouse performance tuning 4/28/2005	SQL Server delivers a number of functions for small to large organizations with needs ranging from internally developed applications to third-party off-the-shelf software. With all systems, data collection, entry and analysis at some level is required to meet organizational needs.
Selecting a SQL Server Backup Model 4/12/2005	In a previous article we discussed the different recovery models SQL Server offers and decision points on which to base your recovery model selection. After you've selected the appropriate recovery model, you need to put in place the proper backup strategy to minimize data loss and downtime in case of a database failure.
SQL Server performance-tuning worst practices 3/29/2005	Sometimes the best practices do not hit home, but the worst practices do. As such, in this article, the first part in a two part series, we will address a number of the worst practices for SQL Server performance that have been found in the field. These primarily relate to the system performance over the lifecycle of the application. We also will make recommendations for correcting these practices and improving overall system performance.
Selecting a SQL Server recovery model 3/22/2005	SQL Server 2000 offers three recovery models for each database: Full Recovery, Simple Recovery and Bulk-Logged Recovery. The recovery models determine how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed.
Alert! Alert! Backup and Restore Baby! 4/12/2004	At many organizations I doubt red lights flash with the sound of blaring sirens when backups fail in production environments, but I believe at every organization backups are one of the most critical IT functions. Of all backups, database backups, by most accounts, are considered the most critical. I know this is certainly true for me as a SQL Server DBA. I am sure at one point in all of our careers we were too busy to check all of our backups across all SQL Servers, with a million responsibilities on a daily basis.
Spotlight - Lumigent Entegra Business Justification 3/16/2004	So what does it take to purchase an IT product? Just think about what it entails to purchase anything from a consumer good to a luxury item. Now, when you have the responsibility of purchasing an IT product for your organization, the same due diligence is needed to make a confident decision that will benefit the organization. As Lumigent Entegra is considered to meet your auditing needs, one common question is: ‘what else can achieve the same results?’ The typical answer for data collection is database triggers. See how Lumigent Entegra compares to trigger based auditing in a side by side comparison and how Entegra benefits organizations facing auditing compliance.
Change Management – Buzz Words or Real Value? 2/4/2004	A lot of people look at change management as something they should have, but typically do not put the effort into creating processes that are easy to follow and repeatable. At most organizations, processes and procedures are usually ‘nice to haves’ and only become important when there is a problem. So how do you move forward? The first step is to identify what components are necessary for your environment.
...And how do you expect me to audit SQL Server? 10/20/2003	A DBA's guide to Enterprise SQL Server Auditing with Lumigent Entegra 2.0.With the release of Entegra 2.0, Lumigent offers the IT organization, specifically the DBAs, a new set of capabilities to audit all of the activity in a SQL Server database with real time alerting and reporting capabilities to solve your compliance needs.
SQL Server Upgrade Recommendations and Best Practices - Part 2 5/28/2003	This article is the second of a multi-part series detailing the SQL Server Upgrade process from the technical, logistical and business perspective. The article details the technical components faced by the DBAs and Developers during the Upgrade process related to: ANSI NULLS, Quoted Identifiers and other items Upgrade Wizard vs BCP and DTS Redundant Upgrade Architecture
SQL Server Upgrade Recommendations and Best Practices - Part 1 2/27/2003	This article is the first of a multi-part series detailing the SQL Server Upgrade process from the technical, logistical and business perspective. In the coming weeks, expanded articles will be published in the following areas: Part 1 - Upgrade Overview and Project Planning SQL Server 6.5 and 7.0 Critical Upgrade Decisions and Redundant Upgrade Architecture SQL Server 6.5 and 7.0 Upgrade Checklist and Application Access Upgrades to SQL Server 2000 Upgrade from SQL Server 2000 to Yukon Sybase, Oracle and Data Upgrades to SQL Server 2000 Post SQL Server 2000 Upgrade Recommendations
Who Needs Change Management? 1/10/2003	Have you ever found yourself in this situation? Something gets changed and you don’t know about until there’s a problem. Or someone makes a change and says “Don’t worry it’s a small change. No one will even notice.” I think we have all found ourselves in these situations. The only way to find things like this is to bolt down your servers and make the users sign their life away if they want to use your server. Not too likely, but it’ll work if you could get it implemented.
Learn About SQL Server Disaster Recovery from Greg Robidoux of Edgewood Solutions 12/31/2002	Disaster recovery is a topic on the minds of many DBAs. Learn some of the basics about SQL Server disaster recovery from a leading expert in the area, Greg Robidoux of Edgewood Solutions. Greg is currently the Vice Chair for the PASS DBA Special Interest Group (SIG). In addition, he recently gave two presentations at the PASS Summit in Seattle on Change Management and Project Management for DBAs.
SQL Server Index Checklist 3/11/2002	Indexing a SQL Server database in some respects is considered both an art and a science. Since this is the case, what are some considerations when designing indexes for a new database or an existing one in production? Are these the same types of steps or not? Do any best practices really exist when it comes to indexing? Where does indexing fall in the priority list from an application or production support perspective?
Addressing HIPAA Auditing Requirements with Lumigent Entegra - Whitepaper	This paper explores a core aspect of HIPAA compliance that IT organizations must contend with - knowing who's doing what to which data when, and by what means. This need for data access accountability can be accomplished through an effective means of capturing and auditing data access. The paper identifies the necessary data audits for HIPAA compliance. Entegra is introduced, as an enterprise data auditing and security solution, enabling IT organizations to monitor, verify and investigate database activity, while helping manage the costs associated with HIPAA compliance, including minimizing performance overhead.
Free SQL Performance Monitor Tool - download	Free Performance Monitor for SQL Server - Free download! Award-winning SQLcheck provides an easy to use performance "dashboard" for a SQL Server. Monitors critical information about your database server, hardware, operating system, and network. Runs on demand or as a screensaver.
Free SQL Server Tips	Free daily SQL Server tips about all aspects of SQL Server.
Performance Tuning Software - download	Indepth for SQL Server for SQL Server provides a complete view of application performance by capturing, measuring, and correlating performance metrics from all critical system components. When problems are detected, Indepth for SQL Server helps you pinpoint the cause, identify the most effective course of action, and quickly solve the problem to restore peak performance.
Reach the SQL Server Performance Tuning Pinnacle - Whitepaper	Long-term, highly performing systems serve as a tremendous asset to an organization, but do not happen by mistake. They are initially a result of comprehensive design and development (from both the hardware and software perspectives) to meet high throughput requirements validated by rigorous load testing. However, as a system matures, this initial effort needs to be complimented by ongoing performance tuning to support evolving needs and to ensure the database platform is performing properly. As more companies rely on SQL Server for mission-critical applications and server consolidation, the need for performance tuning on the SQL Server platform continues to grow. This whitepaper explains how companies can move from reactive performance monitoring to proactive performance management.
SQL Auditing and Compliance Software - download	Lumigent Audit DB provides comprehensive monitoring and auditing of data access and modifications. Audit DB’s trusted auditing approach provides an unimpeachable audit trail of who has accessed or modified what data, and supports best auditing practices including segregation of duties.
SQL Auditing and Compliance Software -Download	Auditing and Continuous Compliance for SQL Server provides powerful, low overhead auditing and compliance for Microsoft SQL Server to ease compliance with internal audit or federal regulatory requirements such as HIPPA, GLBA or Sarbanes Oxley. Also offers a central repository for all audit data, a central management console, pre-defined or ad hoc compliance reporting, a secure auditors console, data for forensic analysis, and efficient, secure data archival.
SQL Backup Software - download	Enterprise-scale Backup and Recovery for SQL Server provides high-performance backup and recovery for Microsoft SQL Server enterprises. SQLsafe provides fast compressed and secure encrypted backup, reduced storage costs, automated multi-server backup management, and enterprise manageability. SQLsafe will speed database backups by up to 50% over native backups and will reduce disk space requirements by up to 95%.
SQL Performance Monitoring Software - download	Real-time Performance Monitoring and Diagnostics for SQL Server provides a powerful performance monitoring and management, alerting and diagnostics for SQL Server enterprises. Provides extensive real-time and historic performance metrics and details for multiple SQL Servers from a central point of control. Helps to pinpoint, diagnose and solve performance issues faster. Easy-to-use and understand.
SQL Serer Remote Administration Tool - download	SQL mobile manager is a portable monitoring and administration tool for Microsoft SQL Server. It enables database administrators to securely manage their SQL Server environments from a remote location using a Windows Mobile device such as a cell phone or PDA. Easy-to-install and use, SQL mobile manager combines real-time monitoring and instant alerting with powerful diagnostics and remediation capabilities enabling database administrators to assure the performance and availability of their SQL Servers 24 X 7 no matter where they are.
SQL Server Backup and Restore Fundamentals - Expert Webcast	As a SQL Server database administrator you must always be prepared to respond quickly to data corruption or system failure, which also means you must always have reliable backups and a dependable recovery plan. Attend this expert webcast to get tips and best practices on developing such a plan for your organization. In this webcast, industry expert and Edgewood Solutions' Greg Robidoux helps you ensure your backup and restore procedures are properly established and up to par.
SQL Server High Availability Software - download	Neverfail for SQL Server is the most comprehensive high availability, low cost software solution for Microsoft SQL Server as well as being the only high availability solution that proactively monitors your entire SQL environment.
SQL Server Job Management Software - download	Enterprise-class Job Management for SQL Server Schedule and manage all SQL Server jobs from a central point of control an easy to use calendar-baed interface. Increase availability and performance with reduced job failure rates and overlap. Provides historic and future job schedule information. Allows for quick and easy resolution of job conflicts or load balancing, simply drag and drop to adjust any job.
SQL Server Job Scheduling Management Software - download	sqlSentry is the only comprehensive job management system designed and built specifically for SQL Server. It provides visual job scheduling, job performance monitoring, robust MAPI-less alerting, job queuing & chaining features, block detection, and powerful reporting. sqlSentry empowers you with an extensive arsenal of capabilities never before available for the SQL Server platform.
SQL Server Load Testing Software - download	SQLscaler provides a powerful load-testing solution for predicting the behavior and performance of SQL Server databases under stress. SQLscaler is an integrated suite of tools that enable the user to create and inflate test databases, create and automate test case execution and perform comprehensive analyses of test results from data stored in a central repository.
SQL Server Transaction Analysis and Data Recovery Tool - download	Log Explorer is the leading transaction analysis and data recovery solution that improves the availability and integrity of your data. By providing unprecedented access to the database transaction log, Log Explorer gives you the ability to understand and resolve elusive database problems. Easily identify the source of data changes, selectively recover modified, deleted, dropped, or truncated data, and export data for follow-up analysis and reporting. With online data repair and recovery features, Log Explorer helps you maximize database availability and avoid the headaches of traditional methods of data recovery.
The CIO’s Seven P’s of Enterprise SQL Server Management (PDF) - Whitepaper	The quiet buzz has turned into a big bang with enough people saying IT needs “to do more with less!” Now is the time to satisfy these demands for your Enterprise SQL Server implementation. As your staff takes on this initiative “to do more with less,” do not fall victim to sacrificing the quality, accuracy and efficiency of your services, i.e. internal departments, business partners or the general public, while attempting to improve efficiency and cut costs.
The Rational Guide to IT Project Management (paperback) Jeremy Kadlec of Edgewood Solutions	So often, in today's business world, an essential part of any team project is the project manager. Jeremy Kadlec, an independent project manager, offers readers a step-by-step introduction to the art of project management. Geared toward an IT professional, The Start-to-Finish Guide to Project Management supplies checklists to aid you throughout a project, problem-solving techniques and practical people-skills advice. This book provides a strong base for anyone newly tasked with project management or someone looking to break into one of the fastest growing and most lucrative positions in today's workforce.
The Start to Finish Guide to IT Project Management (eBook) Jeremy Kadlec of Edgewood Solutions	So often, in today’s business world, an essential part of any team project is the project manager. Jeremy Kadlec, an independent project manager, offers readers a step-by-step introduction to the art of project management. Geared toward an IT professional, The Start-to-Finish Guide to Project Management supplies checklists to aid you throughout a project, problem-solving techniques and practical people-skills advice. This book provides a strong base for anyone newly tasked with project management or someone looking to break into one of the fastest growing and most lucrative positions in today’s workforce.
Web-based SQL Administration Software - download	Powerful Web-based Administration for SQL Server provides powerful web-based control of SQL Servers. Easy to use for one SQL Server or thousands around the globe. Loaded with over 350 features, actions, and shortcuts to help databases managers to control and administer SQL Servers remotely. Will dramatically reduce administration workload.

Click Here to Register to Access Edgewood's Free SQL Server Resources

Title

Summary