A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Replication Gets Some Love

I really like replication in SQL Server. At least, I like it as a concept. It solves some hard problems and lets me move data around in a way that can handle larger loads, reduce queries to OLTP servers, and more. I've been hoping Microsoft would see this feature as critical to the future success of SQL Server and enhance it's tooling, reliability, and feature set. Each time a new version is completed, I'm hoping that the Release Notes include a lot of replication changes.

I'm usually disappointed, but not always. Recently I was excited by a change in a CU, not a new version. The SQL Server team added the ability to put the distribution database in an Availability Group. This is incredibly useful and helpful for ensuring HADR for replication scenarios. Prior to this, you could put the publication database in an AG, but not the distribution database. This is being added in CU6 for SQL Server 2017 and will be back ported to SQL Server 2016 in a future CU.

There are plenty of restrictions in this first version of the feature, including the fact that local distributors aren't supported. In fact, with all of the ways you can't use this, I bet many replication environments can't implement this.

Yet.

I have hope that future CUs will enhance this feature to remove restrictions and allow more flexibility with replication in AGs. I think that handling naming and networking issues in HADR situations is incredibly complex and replication was built quite some time ago, when we didn't think so deeply about distributed systems. I slowly see Microsoft adapting parts of SQL Server to the modern world, and I hope that they continue to do so for replication.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

Continuous Delivery for SQL Server Databases

Spend less time managing deployment pain and more time adding value. Find out how with database DevOps

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial

Featured Contents

Self SQL Injection

Alessandro Mortola from SQLServerCentral.com

Based on a real fact, this article demonstrates how a bad use of sp_executesql can lead to unpleasant surprises More »

Execution Plans and Data Protection

Additional Articles from SimpleTalk

One of the most important tools for query tuning is ability to view execution plans. They are even portable; you can send an execution plan to get advice from someone who doesn't have access to the underlying data. Have you even considered that sensitive information might travel along with the plans? Hugo Kornelis explains how execution plans affect data protection. More »

Masking Data in Practice

This article describes the practicalities of data masking, the various methods we can use, and the potential pitfalls. More »

From the SQLServerCentral Blogs - Finding Untrusted Foreign Keys and Constraints

Dharmendra Keshari from SQLServerCentral Blogs

In the previous blog, we discussed the Foreign Keys Constraints and how the CHECK Constraints are useful to verify the... More »

From the SQLServerCentral Blogs - Bring Your Own Key to Azure SQL Database TDE

Bradley Schacht from SQLServerCentral Blogs

I have previously written about using Transparent Data Encryption (TDE) with Azure Key Vaule as a great way to store... More »

Question of the Day

Today's Question (by Steve Jones):

I'm looking to determine some information about a file in one of my filegroups. I decide to the the FILEPROPERTY() function. Which of these are valid properties to query? (choose 3)

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

The Phoenix Project: A Novel about IT, DevOps, and Helping Your Business Win

The company's new IT initiative, code named Phoenix Project, is critical to the future of Parts Unlimited, but the project is massively over budget and very late. The CEO wants Bill to report directly to him and fix the mess in ninety days or else Bill's entire department will be outsourced. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have a set of data like this:

 CREATE TABLE CastTest ( Myval VARCHAR(20) ); GO INSERT CastTest ( Myval ) VALUES ('A') , ('My Test') , ('123') , ('123.5') , ('20180301') , ('Mar 23, 2000'), ('Jan 1, 2000'); GO

I decide to filter my data by a date, but I'm worried about the data conversion. I try this code. How many rows are returned?

 SELECT CAST(Myval AS datetime) FROM dbo.CastTest AS ct WHERE CASE WHEN TRY_CAST(ct.Myval AS datetime) IS NOT NULL THEN CAST(ct.Myval AS datetime) END > '2000-01-01'; GO

Answer: 2

Explanation:

In this case, we can use TRY_CAST to only check those rows that can cast to the datatype. Those that cannot will return a null, so no error is returned. In this structure, we cast the non-NULL values to dates and use those to compare.

There are only two valid dates > Jan 1, 2000.

Ref: TRY_CAST - click here

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

Does SQL server 2016 supports Database mirroring like in SQL server 2008 - I am new to SQL server 2016 and requires some information. I have 2 SQL server instances located Geographically apart...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Strange "The datepart millisecond is not supported by date function dateadd for data type date." error - Hi, I am getting strange error 9810 "The datepart millisecond is not supported by date function dateadd for data type date.",...

SQL Server 2014 : Development - SQL Server 2014

Group by month and year - Hi all, I have a date column which isn't behaving as expected...

SQL Server 2012 : SQL 2012 - General

Deleting AG. Some questions - Good Morning Experts, If i delete the Availability Group(AG) in primary replica, what will be the status of databases in primary...

SQL Server 2012 : SQL Server 2012 - T-SQL

Parsing Data in Column, Returning Unique Values - Hello, I have this sample data below: CREATE TABLE MyTable ( varchar(100), VARCHAR(100), VARCHAR(100)) INSERT INTO MyTable (,, ) VALUES ('https://www.abc123.com/p/1/red-widget','This is...

Data Warehousing : Integration Services

Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". - Hi I am following a book on SSIS 2012, please see link below. Ch1, page 25 - 26. I am getting the...

SQL Server 2005 : Business Intelligence

SSIS variable declaration issue - Dear Experts, i am beginner in MSBI. i am try to create full path dynamically.for archive excel file. Input file folder : \\ABC105.SSN.MERIT.COM\VIPP_HT_Bckup_28-12\Actual load\2018\Mar_2018 File...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.