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

The Voice of the DBA

Improving Log Shipping

I have depended on log shipping for a lot of my career. Often I've found the most common disaster is some user wrecks a lot of data. They drop or delete a table, they change all values to some scalar, or perform some similar problematic update. In those cases, it's often that a restore of some sort is needed, but we don't want to overwrite all the other data in the table.

While there are complexities in how you actually work with log shipping, pause it, recover data, and restart it, the basic idea is very simple. It's an extension of basic backup and restore, something all of us (should) be comfortable with. Perhaps that's why it's been something that homegrown scripts handled for many years.

In recent versions, Microsoft has added better tooling around log shipping, and there's a good piece on the useful ways log shipping helps DBAs, along with a feature request. The request is for the direct seeding of log shipping, similar to how Availability Groups can be created. While I'm not sure I love this idea, in today's world of high bandwidth, perhaps this isn't a bad idea.

For me, I think log shipping is a simple feature, but one that certainly could benefit from some ergonomic tooling that might help us better allow clients to discover our secondary databases, or ease the process of recovering data from them and returning it to the primary. I'd like better statistics to help administrators understand the rate of data transfer, easily learning if there are potential issues with our configuration.

Perhaps there are other features you'd like. Maybe you'd want some better filegroup restore capabilities added, allowing you to update some data, while other data is always available on the secondaries. Put on your thinking cap, brainstorm away and give us your ideas in the discussion below.

Steve Jones from SQLServerCentral.com

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

Learning designed for the data professional

PASS Summit offers five educationally-packed days lead by highly-rated, experienced speakers and sessions focused on the way you work with data. Speaker highlights include Bob Ward, and Sunil Agarwal from the Microsoft Tiger Team. Join us Oct 31 to Nov 3. Go to www.PASSsummit.com

SQL Clone: Now supporting databases up to 64TB

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free

Featured Contents

PowerShell Tool Time: Our First Toolbox

Mike Fal from SQLServerCentral.com

Learn how to begin assembling your functions and code into modules you can easily call from any session. More »

What is the biggest mistake you made in production?

Additional Articles from Brent Ozar Unlimited Blog

When you have sysadmin access, you are bound to make a big mistake in production at some point in your career - in this post Tara Kizer looks back at what hers were. More »

Learning designed for the data professional

- PASS Summit offers five educationally-packed days lead by highly-rated, experienced speakers and sessions focused on the way you work with data. Speaker highlights include Bob Ward, and Sunil Agarwal from the Microsoft Tiger Team. Join us Oct 31 to Nov 3. Go to www.PASSsummit.com. More »

From the SQLServerCentral Blogs - SQL Puzzle 3: Knights and Queens

matthew.mcgiffen 73574 from SQLServerCentral Blogs

I thought I’d do another chess puzzle this month. This one is a variant on the Eight Queens problem: SQL Puzzle... More »

From the SQLServerCentral Blogs - Azure SQL Database – Scaling up

Arun Sirpal from SQLServerCentral Blogs

Scaling up and down your SQL Database is something that is quite common to do. I want to discuss the... More »

Question of the Day

Today's Question (by Steve Jones):

What is a Table Valued Constructor?

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 in this category: Table Value Constructor.

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

Expert Scripting and Automation for SQL Server DBAs

Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

When a query is processed logically, which clause is handled first?

Answer: FROM

Explanation:

The logical order of query processing is:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

Ref: SELECT - 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

copying a database from one server instance to another server instance - I am trying to copy a database from one server instance to another server instance. Both instances are viewable in...

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

difficult (for me) query to count available items - I have a difficult sql calculation to make, and I dont't find the answer. Can someone help me? I have a...

First / last item of a string in a nvarchar field - Hi together, I actually have a little Problem and no idea to fix that. I searched @ Google but with the search...

SQL Server 2014 : Development - SQL Server 2014

date fillers - dates in two rows - Hello All, I need a very efficient way to duplicate rows based on dates in rows. table structure is something as...

Removing Repeating Data From a Fuzzy Match Result - I'm in the process of cleaning up a lookup table we have that is used to match on a name...

DBCC CheckDB WITH TableResults - Does anyone have a reference to the columns (and preferably datatypes) in the output of the different versions - or at...

SQL Server 2012 : SQL 2012 - General

Script to later multiple sql authenticated logins on a SQL instance - Hi Does anyone have any scripts or tips for altering multiple sql authenticated logins on a sql instance. T-SQL or...

Replication (possible) issues - Hi all We have a 3rd-party software vendor who puts data into some SQL tables. We want to replicate the databases across...

SQL Server 2012 : SQL Server 2012 - T-SQL

Determining causes for performance difference in queries - Hi, We have an integration query running at 5am - simple view on 3 tables (2 tables have 4-6m rows) - on some...

Query Help - Here is the basic schema of table CREATE TABLE Test (ID INT, ProductCode VARCHAR(50), Quantity INT) INSERT INTO Test SELECT 1,'ABC', 1 UNION SELECT...

SQL Server 2008 : SQL Server 2008 - General

Update Statistics.. - Hi Experts, Trying to figure out if it might help in solving any performance issues by introducing an additional manual update...

Compare database structure - Hi All I am comparing databases( the struct

PLE - Hi All, Appreciate if anybody can help understand why the physical memory is divided by 4 to determine ideal Page Life...

SQL Server 2008 : T-SQL (SS2K8)

How to categorise my customer last 3 year , 2 year and 1 year not billed? - Hi Friends, desc tab1 locn varchar(10) cust_no varchar(10) address varchar(10) desc tab2 cust_no varchar(10) bill_date datetime my expecting output is custno locn _no The condition is i want to...

SQL Server 2008 : SQL Server 2008 Administration

Can I restore a single file .mdf to multiple files spread across disks - I am trying to create a 2nd copy of my database which is 200GB so that I can do Log...

Cloud Computing : SQL Azure - Administration

Script out permissions - Azure SQL database - Hi Folks, Any one has a script to script out user permissions from Azure SQL database? I was not able to...

Reporting Services : Reporting Services

Dynamically read images from share path and display in report - Hi Will it be possible to display the images in SSRS from FTP path ? Two table invoice & image_info -- table invoice Invoice...

Data Warehousing : Integration Services

Data Flow Task Error: The version of ODBC Source is not compatible with this version of the DataFlow - So, that is the error I am getting when I attempt to execute my extremely simple SSIS package. How simple?...

Using dtexec to execute a file system package with project level connection managers - I'm new to SSIS. I'm trying not to be a help vampire ;-) http://www.skidmore.edu/~pdwyer/e/eoc/help_vampire.htm I've read

SQL Server 7,2000 : Replication

Cleaning up Replication Monitor - I have sucessfully removed an old publication and it subscriptions, but I am still seeing references to it in the...

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.