Learning MS SQL Server Interview Questions Day 154
Today #Day154 of #365DaysOfCode, I am Learning MS SQL Server Interview Questions.
3. What are the High-Availability solutions in SQL Server and differentiate them briefly?
There are four High-Availability solutions available in SQL Server:
Failover Clustering,
Failover Clustering is a solution that provides redundancy for the entire instance of the SQL Server. It involves multiple servers, with one server acting as the primary server and the others as secondary servers. If the primary server fails, one of the secondary servers takes over.
Database Mirroring,
Database Mirroring is a solution that provides redundancy for individual databases.
It involves two servers, with one server acting as the principal server and the other as the mirror server.
The principal server sends transaction log records to the mirror server, which applies them to its copy of the database.
If the principal server fails, the mirror server takes over.
Log Shipping, and
Log Shipping is a solution that involves automatically backing up transaction logs from a primary database and restoring them to one or more secondary databases.
It can be used to provide redundancy for individual databases.
Replication
Replication is a solution that involves copying and distributing data and database objects from one database to another.
It can be used to provide redundancy for individual databases or to distribute data across multiple servers for load balancing or reporting purposes.
4. What are the commands used in DCL?
The commands used in DCL are GRANT, DENY, and REVOKE.
GRANT,
GRANT command is used to provide user access privileges to database objects. For example, the following SQL command grants SELECT permission on a table named "employees" to a user named "John":
GRANT SELECT ON employees TO John;
DENY and
DENY command is used to deny user access privileges to database objects.
For example, the following SQL command denies SELECT permission on a table named "customers" to a user named "Jane":
DENY SELECT ON customers TO Jane;
REVOKE
REVOKE command is used to remove previously granted or denied user access privileges to database objects.
For example, the following SQL command revokes SELECT permission on a table named "orders" from a user named "Bob":
REVOKE SELECT ON orders FROM Bob;
Conclusion
In conclusion, the article discusses two topics related to MS SQL Server interview questions.
The first topic covers the four high-availability solutions available in SQL Server, including failover clustering, database mirroring, log shipping, and replication.
The second topic covers the commands used in DCL, which are GRANT, DENY, and REVOKE.