Is it possible to real-time synchronize 2 SQL Server databases

Question

I have an application that runs on server A and the database is on the same server
there is a backup server B which I use in case the server A is down

the application will remain unchanged but the data in the DB is changing constantly

Is there a way to synchronize those 2 databases real-time automatically?

currently I wait till all the users are gone so I can manually backup and restore in the backup server.

Edit: When I said real-time I didn't mean it literally, I can handle up to one hour delay but the faster sync the better.

• My databases are located on 2 servers on the same local network.

• 2 of them are SQL Server 2008, the main DB is on windows server 2008

• the backup is on windows server 2003

• A web application (intranet) is using the DB

• I can use sql agent (if that can help)

I don't know what kind of details could be useful to solve this, kindly tell me what can help. Thanks.

Edit: I need to sync all the tables and table only.
the second database is writable not read-only

Answer 1

I think what you want is Peer to Peer Transactional Replication.

From the link:

Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.