How to install SQL Server on a different machine and set it to be a live replica of the first

Question

Configuration:

I've a SQL Server 2008 R2 on a windows 2003 server. It handles quite well a 300 GB db.

The Problem:

The problem is we are planning to implement a feature in the customer's software that really will over-stress the sql server and our test convinced us it is probably time to migrate from a single r/w node to a r/w node _plus a read only node to do a minimum of load balancing.

It is a completely new scenario for me, and I've no idea of where to start.

I can install another sql server, I've already done it.

Question:

But how to install a SqlServer on a different server and set it to be a live replica of the first (without turn off the first one) ?

I am not a DB Admin, I inherited this 'problem'.

Answer 1

The two most important parts of your question are SQL Server 2008, and this line:

it is probably time to migrate from a single r/w node to a r/w node _plus a read only node to do a minimum of load balancing.

Unfortunately, in SQL Server 2008, your only two options for a readable secondary server are:

1. Log shipping - which isn't real time. When it's time to update the data, you have to kick everyone out of the database for the next restore to finish. That either means everyone's getting kicked out of the database all the time, or they're querying old data. The business isn't usually okay with either of those. Or...
2. Transactional replication - which is fairly straightforward in theory, but can be challenging in practice. When set up incorrectly, it can add a lot of load on the primary server, and you may also have to change your database structures in order to really support replication.

Since you noted that you're not a DBA, here's what I would do in your shoes: beef up the primary server instead. It's easier to accomplish, and it's probably more in your comfort zone.

For advice on where to start with performance improvements, download sp_Blitz (disclaimer: I'm one of the authors of that open source tool) and run this:

sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1

Copy/paste the output of that into your question text here at Stack, and we can give you advice about where to start for performance improvements. (Feel free to redact lines that your company wouldn't want shown publicly.)