Tips

Tips

Microsoft SQL Server Administration

• SQL Server virtualization pros and cons: Weigh the performance impact

  Virtual machines in the SQL Server world can save money and power. But what's the performance impact on I/O, RAM, memory and your Windows operating system? To help you decide whether virtualization is right for your system, SQL Server MVP Denny ...  Continue Reading

• Avoid cursors in SQL Server with these methods to loop over records

  SQL Server expert Matthew Schroeder adds his name to the list of DBAs and T-SQL programmers who wish cursors had never been introduced. Sure cursors have their place for data manipulation on a row-by-row basis. But are they worth the trade-off of ...  Continue Reading

• Implementing security audit in SQL Server 2008

  If you're a DBA tasked with producing detailed audits tracking database use, take a look at these capabilities in SQL Server 2008 where the process is much easier than in earlier versions.  Continue Reading

• SQL Server database replication tutorial

  SQL Server replication involves some decision making on the part of the DBA. What's the best method for your SQL Server environment -- snapshot, merge or transactional replication? Are there any areas where you can expect to be tripped up? SQL ...  Continue Reading

• SQL Server out of memory: Troubleshoot and avoid SQL memory problems

  Ever get a SQL Server out of memory error message? If so, you know that most often this indicates a problem with the system, not simply a memory shortage. SQL Server MVP Denny Cherry shares methods to tune SQL Server to relieve memory pressure, ...  Continue Reading

• SQL Server errors, failures and other problems fixed from the trenches

  SQL Server expert Matthew Schroeder gets down into the trenches to help resolve your SQL Server issues. Whether it's a failed cluster installation, a long-running stored procedure that causes a 3 a.m. phone call or a database in suspect mode, he's ...  Continue Reading

• Can you shrink your SQL Server database to death?

  In an effort to free up disk space, SQL Server DBAs often turn to shrinking the database as part of their SQL Server maintenance plan. When doing so, the database often needs to grow again to accommodate the new data inserted into the database. But ...  Continue Reading

• Tuning SQL Server performance via memory and CPU processing

  Part two of our series on tuning the hardware that supports your SQL Server system, Denny Cherry examines memory allocation and CPU processing and how their configurations impact performance. Learn how clustered indexes can drain SQL Server memory ...  Continue Reading

• Get SQL Server log shipping functionality without Enterprise Edition

  Log shipping provides great high availability and disaster recovery, but only after paying the hefty licensing costs for SQL Server Enterprise Edition.  Continue Reading

• SQL Server 2008 backup compression pros and cons

  Backup compression is a shiny new feature in SQL Server 2008, but what are the drawbacks? Here, SQL Server expert Roman Rehak examines some tradeoffs, such as increased CPU processing time.  Continue Reading

• How to create a SQL Server linked server to DB2

  Many SQL Server environments run DB2 servers, too, and often the two servers must be linked. Here's a step-by-step process that shows how to create a SQL Server linked server using Microsoft's DB2 driver.  Continue Reading

• Tuning SQL Server performance via disk arrays and disk partitioning

  As a DBA, much of your focus is on tuning SQL Server for peak performance. But have you spent any time tuning the hardware that supports your SQL Server system? Are you using the optimal disk array configuration? Are the disk partitions aligned? ...  Continue Reading

• SQL Server data conversions from date/time values to character types

  T-SQL supports two built-in methods for converting date/time data to character data and vice versa. SQL Server expert Robert Sheldon takes you through a step-by-step process for using each method -- implicit and explicit conversions. The steps ...  Continue Reading

• Using full-text search for symbols in SQL Server

  When querying exact SQL phrases that include symbols, you need to add your own logic with SQL Server's full-text search feature, as it does not index non-letters. Options include incorporating a WHERE clause with LIKE to contain your symbol and also...  Continue Reading

• 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