Tips

Tips

Microsoft SQL Server Administration

• Splitting SQL Server backups to multiple files

  Splitting database backups helps save space and run your SQL Server backups more efficiently. Get tips for executing this from Ashish Kumar Mehta.  Continue Reading

• The pros and cons of virtualizing SQL Server environments

  What are the pros and cons of virtualizing SQL Server environments? Basit Farooq takes you through the potential good, bad and the ugly.  Continue Reading

• Preparing for your SQL Server scale-out

  It's never too early to prepare for a SQL Server scale-out. Read this tip from Roman Rehak to learn three popular ways to scale out SQL Server.  Continue Reading

• Index tuning for the reluctant DBA

  Many reluctant DBAs find index tuning intimidating. Read this article by Don Jones to learn why index tuning is a must for your SQL Server shop.  Continue Reading

• Introduction to SQL Server 2012 Express LocalDB

  SQL Server 2012 Express LocalDB is a good way for developers to be able to play around with SQL Server 2012. Learn how to install and get started with it.  Continue Reading

• SQL Server stored procedures vs. CLR stored procedures: Time to migrate?

  Should you use SQL Server stored procedures or CLR stored procedures? Expert Serdar Yegulalp helps you decide which is more appropriate for you.  Continue Reading

• Managing linked server security in SQL Server

  What do you need to know to effectively manage your linked server? Find out in this tip from SQL Server expert Roman Rehak.  Continue Reading

• T-SQL analytic functions in SQL Server can help solve problems quickly

  With the help of T-SQL analytic functions, we can quickly solve complex analytical tasks. Learn more from SQL Server expert Basit Farooq.  Continue Reading

• Best practices for preventing a SQL injection attack

  One of the sneakiest ways for a hacker to invade your system is via a SQL injection attack. Expert Robert Sheldon explains how to keep hackers out.  Continue Reading

• Different ways to audit SQL Server security

  Strong SQL Server security has become a more important part of a robust defense. But what are your options for security auditing?  Continue Reading

• T-SQL tricks for SQL Server 2012: T-SQL statements

  In the final part of Robert Sheldon's series on features and tricks in T-SQL, learn about important T-SQL statements like LAG, LEAD and more.  Continue Reading

• Microsoft SQL Server tools: Native is nice

  What are the SQL Server tools provided by Microsoft? Expert Denny Cherry explains what the native tools are and why they’re useful.  Continue Reading

• Six SQL bulk copy command gotchas

  BCP is a great native tool, but it does have a few limitations. SQL Server expert Serdar Yegulalp discusses some problems and how to avoid them.  Continue Reading

• Pssdiag and Sqldiag Manager – the lifeblood of SQL troubleshooting

  What’s the most important part of SQL troubleshooting? Pssdiag and Sqldiag manager, says SQL Server professional Serdar Yegulalp. Find out what makes Pssdiag and Sqldiag so important.  Continue Reading

• Configuring SSRS the easy way

  Demystify the SQL Server Reporting Services (SSRS) configuration process. Get an overview of this tool and guidelines for how to set up each screen in SSRS from industry professional Roman Rehak.  Continue Reading

• SQL Server Upgrade Advisor, Upgrade Assistant can ease transition pains

  You probably think you’re ready to upgrade to the new release, but you might not be as prepared as you think. Before you upgrade, there are some tools to become familiar with, says Bob Sheldon.  Continue Reading

• ODBC drivers replacing OLE DB? Say what?

  After years of telling SQL Server users to avoid ODBC in favor of OLE DB, Microsoft has suddenly reversed its position and advised going back to ODBC. Why? And what should you do about it?  Continue Reading

• Pros and cons of SQL Server 2012

  Performance and support top the lists of pros for going to SQL Server 2012. But application compatibility and “the unknown” might keep you away for now.  Continue Reading

• Oracle vs. SQL Server face-off: Microsoft SQL cheaper, simpler than Oracle DB

  SQL Server expert Denny Cherry argues that SQL Server is better than Oracle Database because it is cheaper, simpler and has a better supportive community.  Continue Reading

• Microsoft SQL Server 2012 pricing, licensing packed with changes

  SQL Server 2012 pricing includes some significant adjustments, including a reduction in the number of editions and the change from processor- to core-based licensing.  Continue Reading

• When up isn’t enough: Techniques for scaling SQL Server out

  Scaling SQL Server out is not easy; that’s why most businesses choose to scale up their databases instead. But some applications give you no choice: You’ve got to look at options for scaling out.  Continue Reading

• SQL Server stored procedures supercharged in recent, upcoming versions

  Microsoft is forever tinkering with SQL Server stored procedures to give queries more thrust. Find out what’s new in SQL Server 2008, 2008 R2 and the next-generation 2012 release.  Continue Reading

• Three easy ways to improve SQL Server performance

  A sluggish database does no one any good. Consultant Don Jones offers tips to improve SQL Server performance.  Continue Reading

• Third-party tool for SQL Server outshines IntelliSense in SSMS

  IntelliSense in SSMS is Microsoft’s answer to developer pleas. But there is competition from a third-party tool for SQL Server -- SQL Prompt.  Continue Reading

• Bedeviled by SQL Server lock escalation? Trace flags can help

  SQL Server lock escalation can save on memory when working with a table strewn with locks, but it also can lead to blocking. Use SQL Server trace flags to disable lock escalation.  Continue Reading

• Finding your way around SQL Server data preservation

  Need to know what a table looked like before changes were changes were made? There are several tools for SQL Server data preservation -- SQL Server change tracking and SQL Server database mirroring among them -- each with its own uses and ...  Continue Reading

• Gain insight with SQL Server dynamic management views

  Dynamic management views, a feature in SQL Server 2005 and newer, let users say goodbye to complex queries for retrieving database information. In the first of this two-part series, have a look at a sampling of SQL Server DMVs and the database info ...  Continue Reading

• Firing up AlwaysOn for high availability in SQL Server Denali

  Microsoft was aiming for high availability in SQL Server Denali, and SQL Server AlwaysOn is the company’s latest design. Get an expert take on the steps necessary to configure AlwaysOn, available in the first community technology preview of the ...  Continue Reading

• Three SQL Server hardware upgrades to enliven performance

  You’re contemplating SQL Server hardware upgrades and want a shortlist of easy steps to boost performance. You’re in luck. Read on for tips to make it move like never before.  Continue Reading

• Tuning SQL Server table indexes: Adjusting the fill factor

  Don’t get bedeviled by SQL Server table indexes. They can fragment over time, but a little tuning goes a long way. Adjusting the fill factor for an index is common method. Read on for dos and don’ts.  Continue Reading

• Demystifying the Microsoft Distributed Transaction Coordinator

  Microsoft Distributed Transaction Coordinator is crucial for transactional consistency across multiple instances of SQL Server. Learn how to monitor MSDTC and troubleshoot issues, and get tips on how to ensure proper and efficient communications ...  Continue Reading

• Using bcp utility for data moving, sqldiag for troubleshooting

  Learn about three more command-line utilities. The bcp utility for data moving directs data into and out of SQL Server; sqldiag gathers diagnostic information and can be used in troubleshooting a problem; and sqlservr is an executable that starts ...  Continue Reading

• SQL Server command-line utilities: sqlcmd

  SQL Server command-line utilities give database administrators a new way to access the database engine and its components. Learn about each utility and its command syntax, starting with the sqlcmd utility.  Continue Reading

• Command-line utilities for SQL Server database tuning, comparing tables

  Get an inside look at a command line utility for SQL Server database tuning. See how the dta utility does an analysis on its given workload and provides performance recommendations. And read about tablediff, which allows for comparing tables.  Continue Reading

• Reading a SQL Server query execution plan

  Query Optimizer is responsible for carrying out one of SQL Server’s biggest day-to-day duties: analyzing queries and executing them. It does this by producing a query execution plan, which can be interpreted to fine-tune execution times and spot ...  Continue Reading

• Get all the angles on SQL Server database mirroring

  Database mirroring is a high-availability option in SQL Server that’s easy to set up, but for seamless failover, you’ll need to become familiar with the three recommended modes for SQL Server database mirroring, as potential pitfalls are numerous.  Continue Reading

• For better SQL Server security, get Extended Protection

  Want to ensure SQL Server security? Well, it just got a little easier. Extended Protection guards against attacks targeting the SQL Server database engine through enhanced security features in the Integrated Windows Authentication process.  Continue Reading

• Managing multiple SQL Servers the easy way

  Managing multiple SQL Servers has made for some tedious work, and human error is never far behind. But fret not; there are plenty of third-party and Microsoft solutions that can lessen the pain.  Continue Reading

• Working with SQL Server configuration functions

  SQL Server configuration functions let you retrieve information such as the current language used, the number of simultaneous user connections permitted or the version of SQL Server instance you’re connected to.  Continue Reading

• SQL Server merge replication using IIS

  In older versions of SQL Server, securely replicating data between sites or companies was not easy to do. Microsoft SQL Server merge replication simplifies the process using Internet Information Services (IIS).  Continue Reading

• Do you need to harden SQL Server 2008 R2?

  Some say systems like R2 come so secure out of the box that traditional hardening need not apply. But when it comes to security, there’s still plenty to do beyond the defaults.  Continue Reading

• Exploring T-SQL metadata functions in SQL Server 2008

  Metadata functions in T-SQL can help uncover information about database names, objects and more. Learn the basics of metadata functions and how to apply them here.  Continue Reading

• Using file_guid as a unique identifier for SQL Server instances

  DBAs can use the file_guid property to create a globally unique identifier for one or all of their SQL Servers by following a few simple steps.  Continue Reading

• SQL Server index tuning for peak performance

  Without the correct table indexes, SQL Server performance will quickly suffer. Here are a few tricks every DBA should know.  Continue Reading

• The basics of SMO scripting for database objects

  The use of SQL Server Management Objects streamlines the process of scripting database objects, allowing DBAs and developers to customize code using basic SMO scripting know-how.  Continue Reading

• Dissecting the SELECT query syntax in T-SQL

  With so many possible options and extensions, translating complex SELECT statements can be a challenge. DBAs can clear the air by breaking them down piece by piece.  Continue Reading

• Combining T-SQL subqueries with operators in SQL Server

  Using subqueries with different operators allows admins to collect even more information from T-SQL statement. Learn the basics of each and how to implement them here.  Continue Reading

• T-SQL subquery basics: When and how to apply them in SQL Server

  Subqueries in T-SQL give admins flexibility when it comes to analyzing the inner details of subsets of data. Learn how to apply subqueries with this comprehensive rundown.  Continue Reading

• Programming SMO applications for improved management, automation

  SQL Server Management Objects can be very helpful when used with (or even instead of) T-SQL, particularly when it comes to monitoring database backups and space utilization.  Continue Reading

• Free sqlmap tool helps ward off SQL injection attacks

  DBAs can minimize the risk of injection attacks on their databases with this open source utility designed for Microsoft SQL Server and other systems.  Continue Reading

• Getting ‘trigonometric’ with T-SQL mathematical functions

  Mathematical functions in SQL Server 2008 can connect admins to system information that they may not know exists. Learn to apply these functions in this in-depth breakdown.  Continue Reading

• Scalability options for really big SQL Server databases

  When a database outgrows its server, DBAs have the option of either scaling it out across multiple systems or moving to a more powerful server. Here are the factors to consider.  Continue Reading

• Walking through the database auditing process for SQL Server 2008 R2

  SQL Server 2008 R2 features powerful tools for creating database auditing policies that can be implemented using either SQL Server Management Studio or T-SQL scripts.  Continue Reading

• SQL Server tools take the time out of database searches

  Searching databases for specific objects can be frustrating – especially when you can’t recall exactly where said objects are. Fortunately, there are tools out there that can help.  Continue Reading

• The nuts and bolts of T-SQL aggregate functions in SQL Server 2008

  Understanding how to work with aggregate functions can open new doors to the types of information retrieved from your database -- and it may not be as tricky as you think.  Continue Reading

• Supporting packaged SQL Server apps: What you can get away with

  Before making changes in SQL Server, you have to consider how they might affect other applications. The challenge is knowing what you can and can’t do.  Continue Reading

• Top hacker tricks to exploit SQL Server systems

  Stay one step ahead of attackers by testing your SQL Server systems against some of the most common hacking techniques.  Continue Reading

• Loading data into a Master Data Services repository

  Configuration is an important first step for setting up Master Data Services. But what about loading the data? Here is the step-by-step process for R2.  Continue Reading

• Quick tips for monitoring SQL Server performance issues

  When it comes to performance, the most basic techniques can make all the difference. Learn which PerfMon counters to be on the lookout for when tuning your SQL Server environment.  Continue Reading

• Top tips for SQL Server database failover success

  Utilizing log shipping or database mirroring for high availability is only half the battle. There are still plenty of steps to take once your standby server is online.  Continue Reading

• Data collection the automated way in SQL Server 2008

  SQL Server 2008's data collector feature automates the collection of critical performance data, which DBAs can then analyze using brand new SSMS reports.  Continue Reading

• Open source tool simplifies database synchronization for SQL Server

  A free tool for Microsoft SQL Server could help take the time out of syncing up public and development databases.  Continue Reading

• Getting set up for multi-server management with SQL Server 2008 R2

  One of SQL Server 2008 R2's most talked-about new features allows DBAs to monitor the performance of multiple instances with a single glance.  Continue Reading

• SQL injection tools for automated testing

  Manual testing for SQL injection requires a lot of effort with little guarantee that you'll find every vulnerability. Fortunately, there is a better way.  Continue Reading

• Log shipping: Four tips to maintain RTO and RPO with SQL Server

  It's not uncommon for DBAs to be lulled into a false sense of security by log shipping, without realizing the negative effects it could have on disaster recovery.  Continue Reading

• SQL Profiler: A network trace for SQL Server

  SQL Server newbies should be sure to take advantage of Microsoft's SQL Profiler tool, which can be used as sort of a network monitor for the database.  Continue Reading

• Combining result sets from multiple SQL Server queries

  More than one operator can be used to bring multiple queries together for a single result set, but be aware that each one will provide a different result.  Continue Reading

• SQL Server replication: How it works and when to say no

  Each form of SQL Server replication offers its own benefits, but sometimes the best option is to avoid replication altogether.  Continue Reading

• Password cracking tools for SQL Server

  When performing SQL Server penetration tests and security audits, there is one probe you must not miss: password cracking. Check out which tools – some free – to use.  Continue Reading

• Using traces in SQL Server Profiler

  A SQL Server Profiler trace can provide critical information on a problem query in a matter of seconds. Learn how – and when – to run the application.  Continue Reading

• Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server

  Both statements can be used to delete SQL Server data, but some situations call for one over the other.  Continue Reading

• Securing the server and database in SQL Server

  Hardening SQL Server at the database and server levels is crucial to a secure environment. Learn why in part two of our SQL Server security series.  Continue Reading

• Hardening the network and OS for SQL Server security

  To help prevent a major breach of your DBMS, it's important to secure all individual systems in an environment, including the network and operating system.  Continue Reading

• SQL language crash course (just enough to be dangerous)

  You can't get far in the SQL Server world if you don't speak the language. Check out the basics of SQL language with examples of common SQL Server queries.  Continue Reading

• Troubleshooting Distributed Transaction Coordinator errors in SQL Server

  Microsoft Distributed Transaction Coordinator enforces transactional consistency over multiple systems in SQL Server. Learn how to solve common errors and properly configure MSDTC.  Continue Reading

• Clearing the Windows page file and its effect on server performance

  Before enabling the Windows page file setting, consider your server size, hard drive speed and the security of your Windows environment.  Continue Reading

• Push vs. pull: Configuring SQL Server replication

  Learn the difference between push and pull subscriptions and how to determine the best placement for SQL Server replication's Distribution Agent.  Continue Reading

• Setting up SQL Server Service Broker for secure communication

  Securely sending Service Broker messages from instance to instance requires a somewhat complex configuration. This tip walks you through the proper methods.  Continue Reading

• Top tips and tricks for SQL Server database development

  Check out these handy database development best practices, with tips on dealing with batch updates, stored procedures and more.  Continue Reading

• Top load balancing methods for SQL Server

  There are a number of way to achieve load distribution in SQL Server environments. Learn about some of the key methods, including merge replication, log shipping and SSAS server farm deployment.  Continue Reading

• Performance implications of transaction log autogrowth in SQL Server

  Take a closer look at the performance implications of shrinking transaction log files in SQL Server.  Continue Reading

• Maintaining high availability of SQL Server virtual machines

  Learn to take advantage of Hyper-V and Windows 2008 in order to achieve fault-tolerant and highly available SQL Server virtual machines.  Continue Reading

• The short course on how SQL Server really works

  Have you found yourself in the uncomfortable role of DBA simply because there's no one else to do the job? This series is for you. In part one, learn the basics of how data moves from disk to memory in Microsoft SQL Server.  Continue Reading

• Creating fault-tolerant SQL Server installations

  Several fault-tolerance configuration options are available with SQL Server 2008, from the traditional (failover clustering) to the updated (database mirroring).  Continue Reading

• Working with sparse columns in SQL Server 2008

  Sparse columns can provide significant storage benefits to those working with SQL Server 2008. Learn the proper way to implement sparse columns, as well as column sets and filtered indexes.  Continue Reading

• Determining the source of full transaction logs in SQL Server

  Many DBAs at some point have had to deal with a full transaction log. Check out these steps for finding the cause to quickly solve the problem.  Continue Reading

• Improving SQL Server full-text search performance

  What's the best full-text approach for your business? Check out these secrets to ensure high performance of SQL Server full-text search.  Continue Reading

• Scaling up vs. scaling out with SQL Server 2008

  To scale your SQL Server properly means to make it bigger and better. But what is the best method for 2008?  Continue Reading

• Using the OPENROWSET function in SQL Server

  Whether you're bulk loading data or connecting to an OLE DB data source, OPENROWSET is a handy tool for retrieving data. Find out how to use the OPENROWSET function for SQL Server and Microsoft Access.  Continue Reading

• Choosing a SQL Server disaster recovery solution

  Determining your goals is the first step to selecting a disaster recovery technology. Here's how to weigh the pros and cons of the myriad options depending on your business requirements.  Continue Reading

• Examining data files when SQL Server tempdb is full

  If your SQL Server tempdb database is full, the data files could be the cause. Find out how to use the SQL Server process ID number, along with T-SQL queries, to find the source of any unexpected growth in data files.  Continue Reading

• Loading data files with SQL Server's BULK INSERT statement

  The BULK INSERT statement enables the bulk-loading of data files into SQL Server databases. Get instructions on how to use it, along with details on the FIELDTERMINATOR and CHECK_CONSTRAINTS options.  Continue Reading

• Importing and exporting bulk data with SQL Server's bcp utility

  The bcp utility is a command-line utility in SQL Server that allows for the customization of importing and exporting data from text files and SQL Server tables.  Continue Reading

• Testing transaction log autogrowth behavior in SQL Server

  Shrinking SQL Server transaction logs can undermine server performance. An expert runs various tests to demonstrate how transaction log files respond to several T-SQL commands and to determine whether shrinking files is necessary.  Continue Reading

• Using dynamic management views to improve SQL Server index effectiveness

  Dynamic management views can improve the effectiveness of SQL Server's existing indexes. This guide outlines how to use DMVs to monitor fragmented indexes and retrieve currently executing SQL statements.  Continue Reading

• Securing SQL Server with access control, login monitoring and DDL triggers

  SQL Server login monitoring and access control are important elements of SQL database security. Learn how DDL triggers can help alert you to security issues. Setting up security configuration in the beginning can prevent data breaches. Learn how to ...  Continue Reading

• Using dynamic management views to monitor and tune performance of SQL Server

  Dynamic management views help monitor SQL Server health and tune performance. Learn how to use DMVs to retrieve critical data on the internal workings of SQL Server.  Continue Reading

• SQL Server security: Controlling access via database roles

  Database roles for SQL servers can increase the security of your enterprise. Matthew Schroeder explains how to set up database roles, and demonstrates how they function.  Continue Reading

• A guide to basic new features in SQL Server Management Studio 2008, part 1

  SQL Server Management Studio 2008 comes packed with the new features that have been on your wish list, including syntax help, word completion and a list of stored procedure parameters.  Continue Reading

• How to create a SQL inner join and outer join: Basics to get started

  When you're querying a SQL Server database and want to pull related data from more than one table, you'll need to create a join, and here's how.  Continue Reading