Tips

Tips

Microsoft SQL Server Administration

• Monitor database mirroring and replication after a SQL Server upgrade

  Part five in this series describing the case history of a database upgrade to a Windows Server 2003/SQL Server 2005 Active/Active cluster examines how to monitor database mirroring and replication to be sure all data is flowing following setup.  Continue Reading

• How to configure Database Mail in SQL Server 2005 to send mail

  Sending mail via SQL Server has become a much more reliable process. Here, SQL Server expert Roman Rehak explains how to configure Profiles and Accounts within the Database Mail architecture for sending and monitoring email.  Continue Reading

• How to use rank function in SQL Server 2005

  SQL Server 2005 T-SQL includes a set of functions that let you rank the rows in your result set. SQL Server expert Robert Sheldon defines and gives examples of how to use the four ranking functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE. Include ...  Continue Reading

• Create a computed column in SQL Server using XML data

  In this tip, SQL Server expert Robert Sheldon demonstrates how to create a function that runs the XQuery expression and then use that function within your computed column definition.  Continue Reading

• SQL Server memory configurations for procedure cache and buffer cache

  To determine how much memory your SQL Server database uses for buffer cache and procedure cache, factor in the SQL version you're using, whether it's x86, x64 or Itanium and the amount of memory allocated to the SQL Server instance.  Continue Reading

• How insiders hack SQL databases with free tools and a little luck

  It only takes a few free security tools, a little luck and a malicious intent to put your SQL Server database at risk. IT security specialist Kevin Beaver walks you through a real-world scenario of how a network insider can hack seemingly harmless ...  Continue Reading

• Storage area network (SAN) basics every SQL Server DBA must know

  Storage area networks mean better management and improved performance for your SQL Server environment – but only if people in charge understand them. In this tip, SQL Server expert Denny Cherry explains concepts such as the importance of ...  Continue Reading

• SQL Server backups using SAN database snapshots

  In his overview of SAN snapshots for the DBA's tool belt, SQL Server expert Brent Ozar discusses performance impact, filegroup and file layout, replication, transaction logs and snapshots for data warehouses.  Continue Reading

• Using the OUTPUT clause for practical SQL Server applications

  The OUTPUT clause returns results of all the rows affected by an INSERT, UPDATE or DELETE command' has provides many options for simplifying your T-SQL code. In some scenarios, it can even eliminate the need for triggers. Learn how to use the OUTPUT...  Continue Reading

• Check SQL Server database and log file size with this stored procedure

  Knowing the size of a SQL Server database is one of the many DBA responsibilities that you can accomplish easily with the stored procedure sp_SDS. Not only will sp_SDS determine "SQL Database Space," but it can also be used to monitor database ...  Continue Reading

• Sarbanes-Oxley compliance checklist: IT security and SQL audits

  If your SQL Server environment is not yet expected to meet Sarbanes-Oxley (SOX) Act compliance standards, it soon will be. In this tip, SQL Server expert Michelle Gutzait presents security practices, procedures and documentation that prepare you for...  Continue Reading

• Create DDL table in SQL Server 2005 to audit DDL trigger activity

  Each time DDL statements in SQL Server 2005 make changes with commands, such as CREATE TABLE and ALTER, the DDL trigger fires an event. It's possible to log these events with an audit table and custom-made DDL trigger. In this tip, SQL Server expert...  Continue Reading

• Configure SQL Server Service Broker for sending stored procedure data

  The Service Broker feature in SQL Server 2005 allows you to queue data of stored procedures and other application development components. Instead of processing the data, it's sent to a queue where it can be automatically read at a later date and ...  Continue Reading

• SQL Server 2005 log shipping setup using the wizard

  When you set up log shipping in SQL Server 2005, there are many options to choose from to get the optimal configuration between primary and secondary servers. Along with some best practices, SQL Server MVP Hilary Cotter shares a log shipping setup ...  Continue Reading

• Find size of SQL Server tables and other objects with stored procedure

  When determining SQL Server disk space used by an object, Microsoft's sp_spaceused has limits. Here's an original stored procedure, sp_SOS, that calculates specific object space in SQL Server. Use it to get an overview of user table sizes in a ...  Continue Reading

• Retrieve XML data values with XQuery in SQL Server 2005

  When you want to access specific XML data values -- instead of XML as a single data type value -- the XQuery is the scripting language you're looking for. Microsoft designed XQuery specifically to access XML data in SQL Server 2005, allowing you to ...  Continue Reading

• Tips for scheduling and testing SQL Server backups

  Whether you're using SQL Server or SQL Server Express Edition, these tips for scheduling backups will lead you to a successful restore. You'll learn how to schedule backups in SQL Server via the SQL Server Agent and in SQL Server Express using ...  Continue Reading

• SQL Server tempdb best practices increase performance

  Take your SQL Server tempdb's performance to new heights by making a few adjustments to the standard database settings. SQL Server expert Denny Cherry shares some best practices for tempdb, including physical file settings, storage array ...  Continue Reading

• Ten common SQL Server security vulnerabilities you may be overlooking

  Securing SQL Server takes more than firewalls and strong passwords. Ask pertinent questions to expose the weak points in your SQL Server system: Who is granted permissions to execute SQL Server commands? Do you grant permissions to remote databases?...  Continue Reading

• How to maintain SQL Server indexes for query optimization

  Maintain your SQL Server indexes with these techniques for page splits, table partitions, statistics updates and for limiting fragmented indexes. You'll also learn why query optimizer might choose to ignore your non-clustered index and instead opt ...  Continue Reading

• Stored procedure to monitor long-running jobs in SQL Server 2000

  Monitoring long-running jobs in SQL Server 2000 is not a simple practice, but it is a best practice among DBAs. In this tip, try out a stored procedure that easily identifies long-running jobs. You'll also receive email alerts about these jobs that ...  Continue Reading

• Five sqlcmd features to automate SQL Server database tasks

  Make your life easier with the sqlcmd tool in SQL Server 2005 that automates administration and maintenance via scripting. In this tip, you'll get five useful features and examples to execute sqlcmd commands, such as for utilizing and populating ...  Continue Reading

• Simplify queries with SQL Server 2005 common table expressions (CTEs)

  Common table expressions (CTEs) are a handy alternative to using derived tables and views in SQL Server 2005 for retrieving data. There's no need to repeat complex code because CTEs separate code into unique units and they're self-referencing within...  Continue Reading

• Designing SQL Server non-clustered indexes for query optimization

  Non-clustered indexes improve SQL Server query performance by focusing queries on a subset of data. In this tip, you'll find guidelines for designing non-clustered indexes, including choosing index fields, when a clustered index should be used ...  Continue Reading

• Configuring SQL Server memory settings

  While SQL Server has only a few simple memory settings, those settings are crucial. In this tip, SQL Server expert Denny Cherry shares configuration recommendations for optimal RAM amounts, how to enable AWE, the best number of gigabytes for maximum...  Continue Reading

• SAN considerations for your SQL Server environment

  SANs surpass the performance of locally attached storage for its high availability and high performance access to data. In this tip, Contributor Hilary Cotter highlights what you need to consider when using SANs and how to test your SQL Server ...  Continue Reading

• Make changes to SQL Server stored procedures with batch editing

  Many common SQL Server database tasks are performed with stored procedures. Here's a step-by-step process for editing a group of stored procedures at one time, whether you're using SQL Server 2000 or 2005.  Continue Reading

• XML data type in SQL Server 2005 vs. VARCHAR (MAX)

  When choosing to use the XML data type or the VARCHAR (MAX) data type, in SQL Server 2005, DBAs and developers should know the potential performance impact of each. Here's a look at two tables, one inserted and queried with XML and the other with ...  Continue Reading

• SQL Server clustered index design for performance

  Clustered index design choices are vital for system performance and future maintenance of your SQL Server database. Find out why clustered indexes should be narrow, static, increase over time and how they use many-to-many tables. Also explained in ...  Continue Reading

• Optimize T-SQL data types in SQL Server

  Data types seem like a small issue at design time, but they have a huge impact on SQL Server performance as the system grows.  Continue Reading

• Tricks to increase SQL Server query performance

  Increase query performance in SQL Server by having a few tricks up your sleeve to avoid system table locking and improve query response time.  Continue Reading

• Database mirroring factors to consider before setup

  Before setting up database mirroring in SQL Server 2005, consider a few pre-deployment factors. SQL Server MVP Hilary Cotter outlines database and server needs and network requirements that will guide you toward optimum SQL Server availability.  Continue Reading

• Stored procedure to find fragmented indexes in SQL Server

  Fragmented indexes and tables in SQL Server can slow down application performance. Here's a stored procedure that checks for fragmented indexes in all servers and databases.  Continue Reading

• SQL Server backup and restore commands to limit downtime

  Whatever SQL Server backup and restore solution you choose, you must be sure to back up your data in an acceptable amount of time and restore it in an acceptable amount of time. SQL Server MVP Hilary Cotter shares some best practices for using ...  Continue Reading

• Update SQL Server table statistics for performance kick

  Here's what you need to know about using auto and manual update commands and avoiding false statistics -- two of many techniques that will fine-tune SQL Server statistics and boost performance and accuracy.  Continue Reading

• Optimize tempdb in SQL Server by striping and splitting to multiple files

  Optimize tempdb performance by striping and splitting to multiple files. Here are some pointers to effectively allocate temporary databases across filegroups in SQL Server 2005.  Continue Reading

• Best practices for SQL Server backup maintenance

  Improve SQL Server performance by removing backup history from the msdb database and reducing its size. Database architect Denny Cherry shares some backup commands for optimal SQL Server maintenance.  Continue Reading

• Database security testing terms: Setting the record straight

  There are differences among three commonly used database security testing terms. IT security specialist Kevin Beaver shares the characteristics that set apart security audits, penetration tests and vulnerability assessments.  Continue Reading

• Problem connecting to SQL Server 2000 Analysis Services?

  Have a problem connecting to SQL Server 2000 Analysis Services? SQL Server expert Michelle Gutzait examines possible problems and ultimately shares a solution. Hint: It could be your memory settings.  Continue Reading

• SQL Server blob data types for accessing and storing data

  When storing blob data, you can choose from various data types. In this tip, SQL Server architect Denny Cherry examines each data type and explains how to store and access the blob data.  Continue Reading

• Examples of SQL Server stored procedures and parameters

  How can SQL Server stored procedures be helpful if you don't know they exist? Database architect Denny Cherry shares 18 examples of handy stored procedures and their parameters that Microsoft has not documented.  Continue Reading

• Temporary tables in SQL Server vs. table variables

  Once you've written your T-SQL code, it's time to execute it. You'll most likely need a table in which to store data temporarily. SQL Server database architect Denny Cherry explains your four options while offering the pros and cons of using ...  Continue Reading

• Managing identity columns with replication in SQL Server

  Managing identity columns with replicated tables in your SQL Server 2005 database requires some TLC. In these scenarios, see how to replicate tables with identity columns and also get options to manage identity seeds for replicated tables. You'll ...  Continue Reading

• T-SQL commands vs. XML AUTO in SQL Server

  XML functions may use many more resources than the standard T-SQL commands, depending on the amount of data. SQL Server database expert Michelle Gutzait shows performance implications for each when comparing the XML AUTO function with T-SQL commands.  Continue Reading

• Optimize disk configuration in SQL Server

  Proper disk configuration can result in a lifetime of high performance for SQL Server databases. Go beyond storage capacity requirements and consider drive performance. Database architect Denny Cherry outlines best practices for designing your disk ...  Continue Reading

• SQL Server cluster design: One big cluster vs. small clusters

  Clustering design in a SQL Server environment is a key strategy. You must consider options including load balancing, disk array failure, the number of nodes and instances.  Continue Reading

• Performance analysis tools for SQL Server

  Just being equipped with SQL Server tools is not enough. Database administrators and developers need to know how to use and ultimately optimize SQL Server tools like Query Analyzer, Index Tuning Wizard and SQL Profiler. Serdar Yegulalp explains how ...  Continue Reading

• 'Out-of-range datetime value' error in SQL Server when working with dates

  Have you ever been stumped by this error message: "conversion of char data type to datetime data type resulted in an out of range datetime value"? Contributor Serdar Yegulalp shares causes and fixes for the frequently seen error message in SQL ...  Continue Reading

• Debugging SSIS packages in SQL Server

  Debugging SSIS packages is a feature in SQL Server 2005 that offers many methods for finding causes of problems. Contributor Eric Johnson takes you through a step-by-step process for two of these methods: breakpoints and data viewers.  Continue Reading

• Monitoring server disk space in SQL Server

  Monitoring your SQL Server database files is an essential operation of all DBAs. Avoid downtime and data loss with the three methods described here to monitor auto-growths of database files.  Continue Reading

• Optimize merge replication performance

  Improve merge replication performance with methods explained in this tip and foster a highly scalable SQL Server environment. SQL Server expert and Microsoft MVP Hilary Cotter explains the tuning process and helps you determine if merge replication ...  Continue Reading

• Troubleshoot SQL Server queries and improve I/O

  SQL Server performance success often relies on I/O activity. Edgewood Solutions' Jeremy Kadlec first takes you through the steps applying Performance Monitor to troubleshoot I/O problems.  Continue Reading

• Database security policies to think about

  Government and industry regulations are tightening up on information security policies. Is it time to update your organization's database security beyond basic policies for passwords and data backups? IT security specialist Kevin Beaver gives you ...  Continue Reading

• SQL Server bulk-logged recovery

  Minimal space requirements for transaction logs and best performance for bulk operations are among the advantages to the bulk-logged recovery model in SQL Server. However, one disadvantage is that if there is a bulk-logged transaction in your backup...  Continue Reading

• 32-bit vs. the 64-bit SQL Server performance surge

  The 64-bit platform in SQL Server is setting a new computing standard. Will you make the leap from 32-bit to 64-bit? Contributor Serdar Yegulalp compares the two editions and highlights 64-bit advantages, including sustaining additional concurrent ...  Continue Reading

• Active/Active clusters in SQL Server

  Multi-instance, or Active/Active SQL Server cluster design is an involved process. To optimize high availability and performance, you must consider a number of components in ensuring both instances can run on a single node. Hilary Cotter walks you ...  Continue Reading

• SQL Server query design: 10 mistakes to avoid

  SQL Server queries should guide you toward successful, trouble-free data retrieval. Edgewood Solutions' Jeremy Kadlec has put together a Top 10 list of query design mistakes to avoid.  Continue Reading

• Extend SSIS packages with scripting and programming

  SQL Server Integration Services brings new meaning to importing data, compared to DTS. Microsoft also offers two methods of extending SSIS functionality. Contributor Serdar Yegulalp shares how to extend Control Flow and Data Flow with scripting, and...  Continue Reading

• Boost SQL Server data warehouse performance

  Boost SQL Server data warehouse performance with these tips on improving query performance by expert Jeremy Kadlec.  Continue Reading

• Speed up SQL Server backup and restore time

  Backup and restore are perhaps the most critical tasks you'll run on SQL Server. They should run in minimal time as they both utilize system resources. In fact, restores require exclusive access to the database. Edgewood Solutions' Greg Robidoux ...  Continue Reading

• Database backup and restore corruption you haven't considered

  Backups are common practice to minimize data loss. Equally important is scheduled routine maintenance, i.e. integrity checks. These checks ensure no corruption exists in your database on the SQL Server level. Taken a key step further to examine the ...  Continue Reading

• Database security options to protect data in SQL Server

  Encryption and data separation in SQL Server are not easy or cheap methods. But customers want security controls in place to protect their sensitive data. In this tip, contributor Kevin Beaver shares alternative tools and techniques to protect ...  Continue Reading

• Stored procedures vs. dynamic SQL: When should you use each?

  Stored procedures and dynamic SQL each have their place in the SQL Server world. You must consider parameters, tables being queried, the number of databases and so forth. In this tip, Serdar Yegulalp maps out the best scenarios for each.  Continue Reading

• Restoring an existing database on the same server

  Restoring an older version of a database to the same server is an occasional duty for DBAs. Backup and Recovery expert Greg Robidoux takes you through the process in T-SQL and Enterprise Manager.  Continue Reading

• Service Broker in SQL Server: How and when to use it

  Service Broker allows database developers to write applications that work across multiple instances in SQL Server. Contributor Serdar Yegulalp explains the benefits of this queued messaging system.  Continue Reading

• IIS tips to secure SQL Server

  IIS security measures can be implemented outside SQL Server to protect databases from malicious attacks. CISSP Kevin Beaver offers 10 Internet Information Server tips to harden SQL Server security.  Continue Reading

• SQL Server clustering best practices

  SQL Server clustering provides high availability to clients by sharing server resources over several nodes, but only when all of its dependencies are addressed. Contributor Hilary Cotter identifies clustering topologies, dependencies and best ...  Continue Reading

• Merge replication pros and cons in SQL Server 2000

  Merge replication should typically be avoided when achieving high availability in SQL Server 2000, according to contributor Michelle Gutzait. She explains the pros and cons of using application load balancing with merge replication and offers a ...  Continue Reading

• Configure RAID for maximum SQL Server I/O throughput

  Backup and restore is one of the most I/O intensive activities you will perform on your SQL Server. Edgewood Solutions' Greg Robidoux explains how to maximize I/O throughput by isolating disk activities and choosing the appropriate RAID level.  Continue Reading

• SQL Server collation conflict fixes

  Fixing SQL Server collation conflict errors when using temp tables in an application and your database collation differs from tempdb.  Continue Reading

• Using Microsoft SQL Server triggers

  Not sure why or when to use SQL Server triggers? This series breaks down the basics, including design considerations and performance issues, and shows examples of triggers.  Continue Reading

• Configure Windows Firewall to allow SQL Server connections

  To access a SQL Server instance on a computer protected by Windows Firewall, you must configure the security tool to receive incoming connections. This tip explains how.  Continue Reading

• Configure SQL Server 2005 TempDB for performance

  Some processing once reserved for the SQL Server 2000 transaction log, such as row versioning and online index rebuilds, has been moved to SQL Server 2005 TempDB, making it essential that you properly configure your database -- or suffer the ...  Continue Reading

• Using Surface Area Configuration to lock down SQL Server

  The SQL Server 2005 Surface Area Configuration tool allows you to manually enable only the services you absolutely need, minimizing the attack surface.  Continue Reading

• Stored procedure: Determine last database backup

  You have dozens or hundreds of SQL Servers to administer and you just want to know when the last backup was taken for each database on each instance. This stored procedure will help.  Continue Reading

• Undocumented DBCC: Command to track memory usage

  This DBCC gives you the top 20 cached items in SQL Server at any given time to help you analyze which objects are spending the most time in or out of the cache.  Continue Reading

• SQL Server cursors pros and cons

  SQL Server cursors can be useful despite their reputation as poor performers, resource hogs and an overused tool by junior developers. This tip explains how cursors work and their pros and cons.  Continue Reading

• Discover and lock down vulnerable SQL Server services

  Be sure to protect those "naked" SQL Servers -- databases unnecessarily exposed to internal and external hackers. Contributor Kevin Beaver explains how to test for vulnerable SQL Server services.  Continue Reading

• View SQL Server transaction logs using DBCC

  View SQL Server transaction logs using this undocumented DBCC command.  Continue Reading

• Restore basics: How to restore using T-SQL commands

  SQL Server Enterprise Manager may be a quick and easy way to run backups and restores, but T-SQL commands offer much greater flexibility. Find out how to script your SQL Server backups or restores.  Continue Reading

• T-SQL performance problems and solutions

  Don't assume all SQL Server performance problems are database-related. Poorly written T-SQL code could be to blame. Learn about common problems and workarounds in this clinic.  Continue Reading

• Tricks for using the SQL Server Index Tuning Wizard

  Learn how to improve overall SQL Server 2000 performance with these Indexing Tuning Wizard tricks from Edgewood Solutions' Jeremy Kadlec.  Continue Reading

• Checklist: Maximize SQL Server backup performance

  Improve the I/O throughput of your SQL Server backups with the steps in this checklist by Edgewood Solutions' Greg Robidoux. It includes optimal backup configuration and scheduling options.  Continue Reading

• SQL Server Clinic: Stop locking and blocking

  Locking ensures SQL Server properly processes transactions, but too much locking can bring transactions to a grinding halt. This SQL Server Clinic walks you through a typical locking scenario and the steps needed to address locking and blocking ...  Continue Reading

• Restore basics: How to restore SQL Server using Enterprise Manager

  To restore a SQL Server database you have two basic options. Edgewood Solutions' Greg Robidoux explores the first of two in this SQL Server restore series.  Continue Reading

• SQL Server batch rules

  SQL Server batch rules are explained in this tip by contributor Baya Pavliashvili who warns of potential failures if certain guidelines aren't followed when grouping T-SQL commands into batches.  Continue Reading

• Stored procedure: Easily filter for SQL Server connections

  In monitoring usage patterns on SQL Server connections, you need a tool to filter for connections of interest. Enterprise Manager has a basic tool to list connections, but it's not particularly flexible or convenient. The sp_ListConnections routine ...  Continue Reading

• SQL Profiler: Features, functions and setup in SQL Server 2005

  SQL Profiler is often considered the best native SQL Server resource because, for one thing, it understands micro-level processing on any SQL Server. Edgewood Solutions' Jeremy Kadlec introduces you to SQL Server 2005 Profiler and explains how to ...  Continue Reading

• How to restore from a transaction log in SQL Server

  A successful SQL Server restore often involves retrieving data from transaction log backups, which means restoring multiple files rather than one large full backup. Edgewood Solutions' Greg Robidoux walks you through the process of backing up and ...  Continue Reading

• Stored procedure: Keep track of SQL Server Agent jobs

  Enterprise Manager's standard tools aren't much help in examining SQL Agent jobs. To get details about jobs currently running, try this sp_ListJobInformation routine instead.  Continue Reading

• How to store database backup media

  As a DBA, you may not personally be responsible for long-term storage of backup files, but you must always be ready to replace data quickly in the event of a SQL Server failure. Edgewood Solutions' Greg Robidoux offers some important best practices ...  Continue Reading

• Stored procedure: Simplify comma-separated value (CSV) exports

  Comma-separated value (CSV) exports are often used to provide SQL Server data to Excel -- but there is no simple way to export SQL Server data within a stored procedure to a file. The sp_SaveDelimitedColumns routine makes CSV exports a breeze by ...  Continue Reading

• Maintaining a healthy SQL Server database

  Just like following a checklist, you must regularly address, strengthen and tune several SQL Server maintenance processes to increase database availability and lower total cost of ownership.  Continue Reading

• Stored procedure: Create a fixed-width file to simplify data exports

  Exporting SQL Server data to a file from within a stored procedure is a common task, but there are different ways to do this --none of which are very easy.  Continue Reading

• Hiding SQL Server

  If you're running SQL Server in an environment where you don't want computers to access it, you must hide the instance from network discovery. Contributor Serdar Yegulalp outlines two ways to hide your SQL Servers.  Continue Reading

• Debugging SQL Server DTS packages

  Debug SQL Server DTS packages in production environments or risk poor performance and lost data integrity. You should use Dynamic Properties to debug in a QA or test environment first and then promote to production. Contributor Hilary Cotter offers ...  Continue Reading

• Stored procedure: Execute T-SQL code from a file

  Need to execute T-SQL code in a file from within other T-SQL code? The sp_ExecuteSQLFromFile stored procedure addresses the shortcomings and adds new functionality.  Continue Reading

• Stored procedure: A simple way to perform crosstab operations

  A crosstab operation usually involves complicated T-SQL coding or pulling data into an application, but sp_Crosstab makes it simple.  Continue Reading

• SQL Server 2000 Query Analyzer: 10 tricks for simple querying

  Many improvements can be made to the raw performance of your SQL Server statements, as well as to your job performance as a DBA or developer, by simply using SQL Server 2000's Query Analyzer -- a tool that Microsoft is excluding from SQL Server 2005...  Continue Reading

• Database partitioning methods in SQL Server

  Partitioning allows you to improve SQL Server read/write performance by distributing a table over multiple databases or servers. Contributor Hilary Cotter describes when to use each of the two partitioning methods -- vertical and horizontal -- and ...  Continue Reading