SQLWebpedia

Author: Chuck Heinzelman Reviewers: Kevin Cox, Dan Jones, Lara Rubbelke During a customer engagement, we noticed a query that was taking a long time to execute. We saw that the query had a scalar-valued User Defined Function (UDF) encapsulating functionality...(read more)

Recently, a problem was presented to me by a DBA using SQL Server 2008 R2. He could not attach a database whose files had been copied from another server. This blog talks about the situation and the solution. Here are the steps he went through to arrive...(read more)

Author: Chuck Heinzelman Reviewers: Kevin Cox, Kun Cheng, Michael Thomassy In a recent customer engagement, I was presented with a problem that I have seen in the past and am surprised that I don’t see more often. Take the following table as an...(read more)

Author: Alexei Khalyako

Technical Reviewers: Murshed Zaman; Prem Mehra

Editor: Beth Inghram

 

Background

Recently a customer approached me with a request for help. They had set up log shipping between two servers that were installed in two different geographical locations. The problem was the following: Over time their database grew, and they saw that they were approaching the limit of available disk space on the secondary. The customer identified the following goals:

1)      They needed to avoid moving data between the primary server and the secondary over the network, simply because moving their data over a very slow network could take over a week. 

2)      Because they had a bigger server to replace the old one, ideally they wanted the new server become the new secondary.

Possible solution

After some investigation, we found a very simple solution for this problem. These are the steps to follow if you have similar constraints:

1.       Pause log shipping by stopping jobs on the primary (let’s call it Server 1) and the secondary (Server 2) responsible for alerting, database backup, and restore.

2.       On the secondary (Server 2), restore the STANDBY copy of your primary database. You can do this by right-clicking on the database name in SQL Server Management Studio, selecting Tasks, clicking Restore, and then clicking Transaction Log. The following picture illustrates the menus.

In the new dialog box, Restore Transaction Log, make sure that the option Point in time shows Most recent possible.

After this action is complete, the database is no longer in STANDBY mode.

3.       Create a full backup of the secondary database.

4.       Restore the backup on the new server (Server 3) with the RESTORE WITH STANDBY command.

5.       On the primary server (Server 1) remove the previous secondary server reference (that is, the reference to Server 2) in the Transaction Log Shipping properties.

6.       In the same window, add the new secondary:

a.       Click Add.

b.      In the Secondary Server Instance dialog box, click Connect and then click the new server (Server 3) instance name.

c.       Select the database that you restored with the STANDBY option.

d.      Make sure the option No, secondary database is Initialized is selected.

7.       Your log shipping works again and now the secondary database is on the new server. To test it, you can ship the log by right-clicking the database name, pointing to Tasks, and then clicking Ship Transaction Logs.

 

If your server database has data residing on the internal disks and the database tends to grow, sooner or later you may run out of disk space and find the approach described above useful.

We are proud to announce that SQLCAT will continue with the SSAS Maestros course in June and July in Redmond and Madrid. SSAS Maestros 1.2 Courses Join us for a five-day deep-dive course on Analysis Services 2008 R2 UDM and join the SSAS Maestro Program...(read more)

Exciting news as earlier this week the SQL Server Product Group announced the release of SQL Server Migration Assistant (SSMA) v5.0! A product with the goal of further simplifying the user experience in automating the migration of Oracle, Sybase, MySQL...(read more)

Author : Shaun Tinline-Jones Reviewers: Lubor Kollar; Conor Cunningham; Steve Howard; Kun Cheng; James Podgorski; Jimmy May; Joseph Sack; Welly Lee; Neeraj Joshi; Keith Bauer Introduction We recently assisted a global ISV to address a performance issue...(read more)

Author: Chuck Heinzelman Reviewers: Steve Howard, Carl Rabeler, Shaun Tinline-Jones, Mike Weiner, Murshed Zaman I was recently involved in a customer lab testing the Microsoft Business Intelligence stack. I noticed an interesting behavior when working...(read more)