The database engine component of SQL Server is the core service for storing, processing data and ensuring data security. The database engine provides controlled access and fast transaction processing to meet the requirements of highly demanding and data-intensive applications in the enterprise.
SQL Server supports up to 50 database engine instances on the same computer. For local installation, the installer must be run as an administrator. If you install SQL Server from remote sharing, you must use a domain account with read and execute permissions for remote sharing.
Overview of High Availability Solutions
High availability solutions can reduce the impact of hardware or software failures, maintain application availability, and minimize downtime perceived by users.
L Fault Transfer Cluster
A failover cluster consists of one or more nodes or servers with two or more shared disks. The instance of SQL Server failover cluster will be displayed in the network like a single computer, but it has a function that failover can occur between nodes when the current node is not available. For example, when non-disk hardware failures, operating system failures, or planned operating system upgrades occur, an instance of SQL Server can be configured on one node of the failover cluster to fail over to any other node in the disk group.
L Database Mirror
Database mirroring is actually a software solution that provides almost instantaneous failover to improve database availability. Database mirroring can be used to maintain a single standby database (or “mirror database”) for the corresponding production database (known as the “principal database”).
L Log Transport
Like database mirroring, log transfer is a database-level operation. Log transfers can be used to maintain one or more warm standby databases of the corresponding production database (known as the “master database”). Standby databases are also called “auxiliary databases”. Create each secondary database by restoring or using database backups that do not restore the primary database. Using backup restore, the generated auxiliary database can be used for limited reports.
Replication uses publish-subscribe mode. In this way, the primary server (known as the publisher) can distribute data to one or more secondary servers (that is, subscribers). Replication provides real-time availability and scalability between these servers. It supports filtering to provide a subset of data for subscribers, as well as partition updates. Subscribers are online and can be used for reporting or other functions without requiring query recovery. SQL Server provides three replication types: snapshot replication, transaction replication and merge replication. Transaction replication has the shortest latency and is often used in situations requiring high availability.
Scalable shared database
Using scalable shared database capabilities, you can extend the read-only database specifically generated for reports. The report database must reside on a set of dedicated read-only volumes, which are mainly used to host the database. With commercial server hardware and commercial volume hardware, you can extend the report database to provide the same view of report data on multiple report servers. This feature also allows the report database to use a smooth update path.