5

The SQL Server 2008 R2 Web Edition product marketing site has a benefit listed on its High Availability section:

Benefit from log shipping, which helps automatically back up data on separate servers or keep multiple-read servers online to better handle large amounts of web traffic... through one-way merge and transactional replication.

How is this scenario implemented? Is this referring to:

  • log shipping to multiple target instances and using snapshots and lots of T-SQL to keep them in sync
  • new functionality to manage multiple-read servers?
  • other?
Improve this question

5 Answers 5

Reset to default
2

Log Shipping is a high availability feature of SQL Server where a database copy from a specific point in time is made available to a different server using full backups + scheduled transaction log backups delivered by an internal mechanism of SQL Server. It's composed of some SQL jobs and some executables and is accessible by a wizard - right click a database -> Properties -> Transaction Log Shipping.

It's no new functionality of SQL Server (if I recall correctly it's at least from SQL 2000).

You can read about it here: Log Shipping Overview.

Improve this answer
4
  • Thanks for the response. What's confusing me is the webpage referring to "multiple read servers". Having to kick users off the read server(s) database when applying new transaction log backups or creating new db snapshots is obviously not helpful for websites. I was hoping log shipping had been updated to handle fast synchronous updates to "multiple read servers"?
    – Jon
    Commented Sep 5, 2011 at 11:05
  • You could gather all transaction log backups from a day and update the database only once a day (when you think it's the smallest traffic). It depends on how often you need your data synchronized on the secondary server (it's a tradeoff that you have to check for). If you need more real-time copies of the database then there is replication..
    – Marian
    Commented Sep 5, 2011 at 11:50
  • real-time copies of all tables are what we are looking for, but without adding the additional maintenance and db objects required by replication to our system. I guess the suitable replication options transactional or peer-to-peer?
    – Jon
    Commented Sep 5, 2011 at 12:03
  • 1
    Peer-to-peer is a subset of transactional replication, where all subscribers can also write (every participant is both a publisher and subscriber), so if you don't need that, just stick to the classical transactional replication. You can see some details here: SQL Server Replication Crib Sheet.
    – Marian
    Commented Sep 5, 2011 at 12:41
2

Log shipping will copy the transaction logs from the primary server to the secondary server(s) and apply them to the respective DBs. If you want to read from the secondary server, the appropriate DBs will have to be in the standby mode.

You can choose one of two settings to be able to query the secondary DBs: 1) The users are disconnected when the logs are being applied or 2) The application of the logs are delayed till the users disconnect.

If you want to have better synchronization and not worry about the disconnection issues while being able to read the DBs, you will have to look into something like transactional replication.

Improve this answer
1

What is meant by using the secondary server(s) as a reporting tool is that instead of using norecovery for the t-log restores on the secondary server(s), you can use standby to put the database in a mode where SELECTs can hit the secondary database(s).

Is it better than snapshots? They have their differences. Snapshots can only reside on the instance where the database resides, so there is zero high availability if you are looking for a hint of that.

Improve this answer
2
  • ok thanks. What I was hoping to achieve was "multiple read servers" that can process all inserts/updates from the master db server in "real-time" without using replication.
    – Jon
    Commented Sep 5, 2011 at 11:46
  • Also understand that log shipping isn't real time, so the delay in reporting data will be that of your scheduled log shipping jobs.
    – Thomas Stringer
    Commented Sep 5, 2011 at 12:00
0

I suspect this refers to a couple of technologies - the first being something like Windows network load balancing (NLB) of the read-only database servers. This will allow a web server (for instance) to query a network name (eg myreadonlydatabaseservers), and spread the load. And you get cheaper SQL Server licensing to boot (about half the list price of SQL Server Standard Edition). I'd speculate this is to try to make SQL Server a bit more affordable like mySQL.

The other technology is about how to keep those read only databases in sync - replication is one technology. Or you could pop each read-only database server out of the NLB "cluster", put it into read-write, and update it.

If I remember, this approach is discussed in the Apress 2005 Replication book: http://www.apress.com/9781590596500

Improve this answer
0

I think this is talking about a couple of different technologies.

Benefit from log shipping, which helps automatically back up data on separate servers

That part is talking about using log shipping to get the data to multiple servers for backup purposes.

keep multiple-read servers online to better handle large amounts of web traffic... through one-way merge and transactional replication.

That part is talking about using merge or transactional replication to spread the workload and using either Network Load Balancer (NLB) or a hardware load balancer across multiple SQL Servers which are all in sync.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.