sql server 2008 Active/standby mode

[SAPSupport]

We want to build SQL Server DIGITS database active/standby mode to achieve real-time synchronization of the whole database, without the need for HA, what technology can be achieved here?

------------------------------------------------------------------------------------------------------------------------------------------------
Learn more about the SAP Support user and program here.

[SAPSupport · Accepted solution]

In SQL Server 2008, to achieve real-time synchronization of the whole database in an active/standby configuration without using High Availability (HA) solutions like Always On Availability Groups (which are not available in SQL Server 2008), you can use SQL Server's Database Mirroring feature.

Database Mirroring is a solution for increasing database availability. It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. One server instance serves the database to clients (the principal server), while the other instance acts as a hot or warm standby server (the mirror server).

Here's how you can set up Database Mirroring in SQL Server 2008:

1. Prepare the Principal and Mirror Servers: Ensure that both servers are properly configured, and SQL Server is installed on both. They should be able to communicate with each other over the network.

2. Backup and Restore the Database: Take a full backup of the principal database and a subsequent log backup. Then, restore these backups on the mirror server WITH NORECOVERY. This leaves the database on the mirror server in a restoring state, ready to synchronize with the principal database.

3. Configure Security: Database Mirroring can operate in two different modes: with a witness server or without. If you want automatic failover, you'll need a witness server. You'll also need to configure the appropriate endpoints and security, such as certificates or Windows Authentication, for the servers to communicate.

4. Establish the Mirror: Use SQL Server Management Studio or T-SQL commands to establish the mirroring session. You'll need to specify the principal and mirror server instances and the database to mirror.

5. Monitor the Mirror: Once established, monitor the mirroring status to ensure that the databases remain synchronized. SQL Server Management Studio provides a Database Mirroring Monitor that you can use to track the status.

6. Failover: In the event of a failure on the principal server, you can manually failover to the mirror server. If you've configured a witness server and set up automatic failover, this can happen without manual intervention.

Please note that Database Mirroring is deprecated in later versions of SQL Server, and Microsoft recommends using Always On Availability Groups, which were introduced in SQL Server 2012, for new applications. However, for SQL Server 2008, Database Mirroring is a suitable technology for your requirements.

Keep in mind that SQL Server 2008 has reached the end of its extended support lifecycle as of July 9, 2019. It's highly recommended to upgrade to a newer version of SQL Server to benefit from the latest features, improvements, and security updates.