SQL Server General DBA

Replicating A Volume Of Large Data via Transactional Replication

During weekend maintainence, members of the support team executed an UPDATE statement against the database on the OLTP Server. This database was a part of Transactional Replication and the database on the subscriber was used by the SSRS reports. The UPDATE statement impacted around 3500000 records. Once the UPDATE statement was executed, the Replication procedure […]

Using Decision Trees in SQL Server

As a practical introduction to decision imagine you are assessing the risk of a PC being infected with a virus. The below data on factors influencing affecting the risk of a PC being infected with a virus is available on MSDN. Shares files Uses scanner Infected before Risk Yes Yes No High Yes No No […]

Does Transactional Replication Include Structural Changes Made to a Database

We typically use Transactional Replication for Reporting purposes. In Transactional Replication, the idea is to replicate the changes done on the Publisher Database (OLTP) onto the Subscriber Database (Reporting).  The main challenge in Replication is whether it will replicate the structural changes made on the Publisher database successfully onto the Subscriber database. For Demonstration purposes, […]

Does LogShipping Include the Structural Changes Made to the Database?

Recently a client asked me “ Does LogShipping ship the Create Table, Alter Table, Drop Table commands onto the Secondary Server?”. Obviously the answer to his question was “Yes” but I wanted to be 101% sure before committing something to him. In this article we are going to prove that LogShipping not only propogates the […]

SQL Server 2014 – Delayed Transaction Durability

Prior to SQL Server 2014, SQL Server used a Write Ahead Log (WAL) mechanism which means that changes are written to the transaction log before they are committed.  The major advantage with this mechanism is that there will be no data loss . However, since IO is the slowest component, there will be a high […]

SQL Server 2014 Performance Tuning – Columnstore Indexes

SQL Server 2014 updatable in-memory columnstore (abbreviated to xVelocity where appropriate) index is another compelling performance-related feature of SQL Server 2014. Columnstore indexes enable you to deliver predictable performance for large data volumes. Columnstore indexes were first introduced with SQL Server 2012, to significantly improve performance of data warehouse workloads. For certain data warehousing analytical […]

Four Key New Performance Features SQL Server 2014 – Part 1

The latest release of SQL Server 2014 has several new performance-related features as well as significant enhancements to the existing performance features. With these new performance-related enhancements of SQL Server 2014 we can significantly improve the performance of high transactional OLTP applications. In this first of the three part article series, we will take a […]

Improved Online Operations in SQL Server 2014

Online indexing rebuilding was a major breakthrough from SQL Server to support users re-indexing while the clustered index (or table) and other indexes are available during the index rebuild operation.   However, in real world there are multiple issues with these online operations as it does not have much flexibility. In SQL Server 2014, there are […]

Manage and Monitor Identity Ranges in SQL Server Transactional Replication

Problem When using transactional replication to replicate data in a one way topology from a publisher to a read-only subscriber(s) there is no need to manage identity ranges. However, when using  transactional replication to replicate data in a two way replication topology – between two or more servers there is a need to manage identity […]

Practical Introduction to XML Manipulation Using XQuery in SQL Server

XQuery was developed by W3C as a query language designed to programmatically interact with and manipulate Extensible Markup Language (XML).  XQuery uses XPath methods and expressions to search and retrieve XML data.  In this article, I’d like to show a few simple, practical examples of these implementations. First, let’s create a table to hold sample […]