Tips

Tips

Microsoft SQL Server Administration

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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