SQL Server database shrink – How and when to schedule and perform shrinking of database files

From time to time, DBAs find themselves in a situation where a SQL Server database becomes too large for their production environment, and needs to be shrunk in size in order to free space and allocate it back to the system.

Before shrinking a SQL Server database or database files, it is very important to understand how the process works and what are the immediate consequences of the shrinking process.

January 4, 2016

Why, when and how to rebuild and reorganize SQL Server indexes

The purpose of SQL Server index is pretty much the same as in its distant relative – the book index – it allows you to get to the information quickly, but instead of navigating through the book, it indexes a SQL Server database.

January 4, 2016

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 1

The PCI DSS (Payment Card Industry Data Security Standard) is a multidimensional security standard designed as a set of technical and operational requirements to protect data of credit card holders. The PCI DSS applies to all entities that store, process or transmit cardholder data, including software developers of applications and devices manufacturers when used in those transactions

This standard originated in 2005 and was created by the PCI SSC (Payment Card Industry Security Standards Council) organization. The PCI SSC organization is founded by American Express, Discover Financial Services, JCB International, MasterCard Worldwide, and Visa, Inc., with a goal to improve security of payment account data via the PCI Security Standards

December 11, 2015

How to continuously pump transaction log file audit data directly to a SQL Server database

In Automating daily transaction log reading, we’ve shown how to automate the process of pumping transaction log data into SQL Server tables with ApexSQL Log. The proposed solution revolved around the creation of a batch file which runs ApexSQL Log CLI commands. The batch file is then scheduled with a Windows scheduler to run on a daily basis. The result is a regular daily update of 2 tables, specifically created by ApexSQL Log to hold audited data, which are populated with fresh auditing results each night.

December 9, 2015

How to narrow a data comparison to affected rows only

When confronted with a disaster recovery scenario with a very large database, but a small group of effected records, an opportunity exists to both speed the process and reduce risk of further damaging data, by updating more rows than necessary, simply by narrowing the subset of compared records.

ApexSQL Data Diff offers the ability to narrow the rows that should be synchronized, so only affected rows are updated. But if the filtering isn’t done properly, you risk “false positives”, rows that you have flagged as different/changed and should be synchronized, even if they weren’t in the subset of affected rows. Synchronizing these rows will update “good” data and roll back any production changes that may have been made, further damaging your data.

November 6, 2015

How to work with SQL database source control labels

A SQL database source control “label” or “tag” (aka revision tag) (name depends on the particular source control system) represents a snapshot in time of the source control repository and its contents. It can be saved as a reference for the future use. When the database development cycles reach a particular milestone e.g. a new build, a source control label can be created as a checkpoint. The team can continue to work on the database but revert to the source control label at any time.

October 27, 2015

How to implement SQL Server source control using the shared development model

Having a team of developers working on the same (shared) database can be challenging for many reasons. It is critical to ensure that all changes are properly tracked and that each developer is informed about the status of objects currently used by the rest of the team. When using a shared database, all changes will be applied against the database before they are committed to the repository.

October 9, 2015

How to implement SQL Server source control using the dedicated development model

Having a SQL Server database under source control is rapidly becoming the norm vs the exception in many software development teams. Using any development model (dedicated or shared), requires the team to establish a workflow and a set of rules. The dedicated model, though, allows a developer to act as an independent part of the process mainly in case the central server/repository is down. In this case, the team can continue to work unhindered. This article will focus on using the dedicated development model for SQL Server source control

September 17, 2015