Tips

Tips

• 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

• 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

• 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

• Track changes to SQL Server 2000 and 2005 with one simple utility

  As a DBA, you need to be aware of what's happening within your SQL Servers. But if you have dozens of SQL Servers -- both 2000 and 2005 models -- and permissions are shared, you'll have to go beyond the limits of triggers.  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

• Five steps to event handlers in SQL Server Integration Services (SSIS)

  The Event Handler tab in SQL Server Integration Services (SSIS) lets you design event handlers that are based on package executables and events they generate. Its flexibility lets you take a variety of actions based on the events you want to monitor...  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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 tables: A best practices guide

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

• 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

• 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

• 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

• 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

• 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 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