Tips

Tips

• Determining SQL Server database storage requirements

  The first time you calculate storage requirements for a SQL Server database application is significantly important and should be done during the development phase. Here are two methods for pinpointing future storage needs.  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

• Setting up SQL Server clusters on a SAN

  Storage area networks (SANs) allow you to connect expandable storage to your SQL Server installations, but care must be taken when working with SQL Server clusters.  Continue Reading

• Custom VB.Net scripting in SQL Server Integration Services

  Get the power of Visual Basic.Net within your SQL Server Integration Services (SSIS) package by adding the Script component. In this tip, you'll get the step-by-step method on how to add a Script component and extend any SSIS package.  Continue Reading

• SQL Server encryption vs. hashing for data security

  Choose carefully between encryption and hashing algorithms in SQL Server 2005 because not all methods fit all data security purposes.  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

• Configure aliases for SQL Server databases and servers

  Aliased names can save you from changing your code and configuration files when you need to point your application to another server or move the location of database objects. In this tip, you'll find options and strategies for aliasing database ...  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

• Creating SQL Server columns: A best practices guide

  When creating SQL Server columns, improve database performance by following these best practices for defining data types, keys and NULL configurations.  Continue Reading

• Creating SQL Server tables: A best practices guide

  Tips for Creating SQLServer Tables  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

• Run DTS packages within SQL Server Integration Services

  Do you have a DTS package that performs just right in SQL Server 2000 and you'd like to have it do the same in SQL Server 2005? A method does exist. SQL Server expert Eric Johnson walks you through the steps to run a DTS package in your SQL Server ...  Continue Reading

• Managing permissions in SQL Server Reporting Services

  SQL Server Reporting Services offers ease when creating and deploying SQL Server reports for your entire organization. But when it comes to SSRS security, there's some work to be done on your part.  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

• SQL Server 2005 Express vs. Desktop Engine (MSDE)

  SQL Server 2005 Express Edition and the SQL Server Desktop Engine (MSDE) each provides a medium where database administrators can work before leaping to the full version of SQL Server. Windows expert Serdar Yegulalp examines these products to help ...  Continue Reading

• Best SQL Server indexing strategies

  SQL Server supports only two index types for most data types: clustered and non-clustered. Learn how and why building the appropriate index type during your database design will significantly impact SQL Server performance.  Continue Reading

• SQL Server filegroups for backup and restore

  SQL Server filegroups have hundreds of significant uses in your database. What will be your backup and restore strategy for these filegroups? Database administrator Eric Johnson examines filegroup sizes and scenarios and shows you how to optimize ...  Continue Reading

• SQL Server patch pros and cons

  Are you afraid to patch your SQL Server systems? Installing a patch can bring your database server down and corrupt data. Yet, unpatched software leaves your system vulnerable to attacks by unruly insiders. IT security specialist Kevin Beaver ...  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

• Differences between varchar and nvarchar in SQL Server with examples

  Discover the key differences between varchar and nvarchar for SQL Server data types from expert Serdar Yegulalp. Learn length requirements, how each data type is stored differently and how to mix and match them.  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

• Building a data warehouse in SQL Server: Eight tips to get started

  When building a data warehouse using SQL Server technologies, there are some challenges you may face. SQL Server database expert Baya Pavliashvili addresses some of the most common issues.  Continue Reading

• T-SQL error handling with Try...Catch blocks

  T-SQL Try…Catch blocks in SQL Server 2005 are key for transaction management via error handling. Also called exception handling, error handling now offers developers improved capabilities and system functions to handle errors when executing ...  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

• Mimic SQL Server Management Studio reports for Reporting Services

  SQL Server Management Studio has standard reports that can be mimicked to appear in Reporting Services. Find out how to get the reports' source code to do so, and also how to add custom reports to Management Studio.  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

• Maintain DTS packages in SQL Server 2005

  What if you've migrated to SQL Server 2005 and want to hold off upgrading your DTS packages to SSIS? Learn how to edit, maintain and even develop existing DTS packages using the SQL Server DTS Designer Tool.  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

• Copy-only backups: Another useful tool in SQL Server 2005

  Copy-only backups of your database are a new option with SQL Server 2005. This useful backup tool doesn't interfere with your regularly scheduled backup sequences. If creating a spontaneous full backup is necessary, you'll avoid negative impacts to ...  Continue Reading

• MDX functions: SCOPE, THIS and FREEZE

  MDX in SQL Server 2005 Analysis Services brings exciting improvements, including query support and expression/calculation language.  Continue Reading

• CASE statements now supported by MDX script in SQL Server 2005

  MDX in SQL Server 2005 Analysis Services brings exciting improvements including query support and expression/calculation language.  Continue Reading

• T-SQL in SSIS: The power and the weaknesses

  Using T-SQL vs. the Data Flow task in SQL Server Integration Services (SSIS) is a decision dependent on a number of factors. Serdar Yegulalp shares some things to consider, including data origin, operation complexity and stored procedure purpose.  Continue Reading

• 32-bit to 64-bit SQL Server migrations

  Migrating from 32-bit to 64-bit in SQL Server is an involved process, but you can build a bridge away from any 32-bit dependencies. Serdar Yegulalp discusses 32-bit and 64-bit platform issues including data source providers, user-defined functions ...  Continue Reading

• Replicated stored procedure options with SQL Server 2005

  Replicating stored procedures between SQL Server environments is a process for which you have many technologies to choose from, including native replication features, log shipping and manual and automated deployments.  Continue Reading

• Clustered and non-clustered indexes in SQL Server

  Rules for using a clustered index vs. a non-clustered index are tough to decipher. In this tip, you'll see the fundamentals to consider when making your choice, along with some best practice techniques. Get an overview of tradeoffs and proper index ...  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

• Logging for security compliance in SQL Server

  Logging for security compliance in SQL Server can be tricky. Recommendations come from all directions: vendors, auditors and lawyers. As a SQL Server administrator, it's your job to determine what's reasonable based on risk. Contributor Kevin Beaver...  Continue Reading

• SQL Server Integration Services programming basics

  SSIS is more than a tool to move data around in SQL Server 2005. It creates a system where the data sets that you import and the packages you create are programmatic objects that can talk to one another, rather than existing as static data.  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

• Migrating SQL Server 2000 DTS packages to SSIS

  SSIS is more than a replacement for SQL Server 2000's DTS packages. The scale of changes is vast and this SQL Server 2005 ETL platform more powerful. In his tip, contributor Joe Toscano helps make the migration as painless as possible by guiding you...  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

• Restoring from previous SQL Server versions

  Restoring your SQL Server 7.0 or 2000 machine to 2005 may be fairly straightforward. Restoring an earlier version is another story entirely. Edgewood Solutions' Greg Robidoux explains how to restore an older database to a new SQL Server 2005.  Continue Reading

• SQL Server Reporting Services analysis and tuning

  Commands running in your report server may be preventing real-time performance. Analyze the impact of performance hogs and get help tuning Reporting Services.  Continue Reading

• Database mirroring setup in SQL Server 2005

  SQL Server 2005 database mirroring allows you to automatically mirror database contents from one SQL Server database to another. Edgewood Solutions' Greg Robidoux explains how it works, what you need to make it work and how to get it up and running...  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

• Moving data between MySQL and SQL Server

  If your objective is to move data back and forth between MySQL and SQL Server, you must take into account data typing restrictions, normalization standards and migration tools.  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

• Use cube partitions to improve Analysis Services performance

  Analysis Services performance may falter when reporting on huge volumes of data. Contributor Baya Pavliashvili explains how to use cube partitions to improve query performance and reduce downtime.  Continue Reading

• Exception handling best practices in SQL Server 2005

  SQL Server 2005 brings you TRY/CATCH, a new T-SQL feature that helps find and address errors before they reach application code. Get best practices for working with the new feature.  Continue Reading

• SQL Server 2005 backward compatibility issues to consider

  Do you fear a SQL Server 2005 upgrade will break server applications? Contributor Serdar Yegulalp addresses key backward compatibility problems.  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

• Working with schemas in SQL Server 2005

  If you're managing too many databases with too many objects, it may be time to take advantage of SQL Server 2005's ANSI SQL feature: schemas.  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

• Setting up and maintaining standby servers

  Standby servers may be used for continuous log shipping in the event of a database failure or to verify SQL Server backups. Here you'll find tips for setting up and maintaining a standby server.  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

• SQL Server Integration Services to simplify database maintenance

  SQL Server Integration Services (SSIS) eases your database maintenance workload in SQL Server 2005. Edgewood Solutions' Jeremy Kadlec reviews SSIS benefits and commonly asked questions.  Continue Reading

• How to reuse datasets with Reporting Services

  Rather than continually create, destroy and recreate the same datasets to run a report repeatedly, get a trick to create the dataset once and retain it until no longer needed.  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

• Watermarking reports in Reporting Services

  Reporting Services will not watermark reports to designate a document's status. Get other options for watermarking your reports in this tip.  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