Book Image

Microsoft SQL Server 2008 High Availability

By : Hemantgiri S. Goswami
Book Image

Microsoft SQL Server 2008 High Availability

By: Hemantgiri S. Goswami

Overview of this book

<p>Every business has it's mission critical applications and data. Therefore, it is very important to keep database servers up and running all the time – 24 X 7. There is a need to store and process terabytes of data to cater for business needs, and it is vital to make data highly available.<br /><br />High availability is all about the site being accessible all the time. High availability solutions minimize the downtime for these mission critical applications.</p> <p>Microsoft SQL Server is a powerful relational database engine, widely used to store and maintain data in Enterprises of various levels be they small, medium or large.</p> <p>This book will teach you how best to use these readily-available options and save you time in making your websites highly available.<br /><br />This Microsoft SQL Server 2008 High Availability book will take you through pre and post installation concepts and common issues you come across while working with SQL Server HA. It will teach you how these various HA solutions can be installed using GUI and the command line. It will also show you how to troubleshoot common issues you may encounter whilst installing or managing the HA option in your environment. It provides references to external links for more advanced learning on the topic.<br /><br />This book starts with an introductory chapter into the windows domain, domain users and various handshake methods available with Windows server. It also offers information the different authentication methods available with SQL Server - giving you an insight into the importance of security. After you are through with the security aspects, your journey to installing SQL Server HA will start. It will cover the concepts of database mirroring, log shipping, clustering, and replication.<br /><br />By the end of this book you will be confident enough to take up the challenge to install any of the SQL Server HA options.</p>
Table of Contents (18 chapters)
Microsoft SQL Server 2008 High Availability
About the Author
About the Reviewers

Understanding SQL Server Security

Security!! Now-a-days we store various kinds of information into databases and we just want to be sure that they are secured. Security is the most important word to the IT administrator and vital for everybody who has stored their information in a database as he/she needs to make sure that not a single piece of data should be made available to someone who shouldn't have access. Because all the information stored in the databases is vital, everyone wants to prevent unauthorized access to highly confidential data and here is how security implementation in SQL Server comes into the picture.

With the release of SQL Server 2000, Microsoft (MS) has introduced some great security features such as authentication roles (fixed server roles and fixed database roles), application roles, various permissions levels, forcing protocol encryption, and so on, which are widely used by administrators to tighten SQL Server security.

Basically, SQL Server security has two paradigms: one is SQL Server's own set of security measures and other is to integrate them with the domain. SQL Server has two methods of authentication.

Windows authentication

In Windows authentication mode, we can integrate domain accounts to authenticate users, and based on the group they are members of and level of access they have, DBAs can provide them access on the particular SQL server box.

Whenever a user tries to access the SQL Server, his/her account is validated by the domain controller first, and then based on the permission it has, the domain controller allows or rejects the request; here it won't require separate login ID and password to authenticate. Once the user is authenticated, SQL server will allow access to the user based on the permission it has. These permissions are in form of Roles including Server, fixed DB Roles, and Application roles.

  • Fixed Server Roles: These are security principals that have server-wide scope. Basically, fixed server roles are expected to manage the permissions at server level. We can add SQL logins, domain accounts, and domain groups to these roles. There are different roles that we can assign to a login, domain account, or group — the following table lists them.

    Role name

    Permission user can have


    Can perform any activity in the server


    Can change server-wide configuration and shut down server


    Can manage logins and their properties


    Can end the process that are running in SQL Server


    Can add or remove linked servers


    Can run the bulk insert statement


    Can manage the disk files


    Can create, alter, drop, or restore any database


    Default role assigned to each login

  • Fixed DB Roles: These are the roles that are assigned to a particular login for the particular database; its scope is limited to the database it has permission to. There are various fixed database roles, including the ones shown in the following table:

    Role name

    Permission user has


    Alter any users, create schema, connect


    Back up database, log, and create checkpoint


    Can execute select statement


    Can execute delete, insert, and update statements


    alter — assembly, asymmetric key, certificate, database DDL trigger, database event, notification, dataspace, fulltext catalog, message type, remote server binding, route, schema, service, symmetric key, checkpoint; create — aggregate, default, function, procedure, queue, rule, synonym, table, table, view, XML schema collection and references


    Cannot execute select


    The role is to revoke the right/permission for select statement


    Can perform any action in the database


    Can alter — application role, any role, create schema, view definition


    Can view most recent status in database mirroring monitor

  • Application Role: The Application role is a database principal that is widely used to assign user permissions for an application. For example, in a home-grown ERP, some users require only to view the data; we can create a role and add a db_datareader permission to it and then can add all those users who require read-only permission.

  • Mixed authentication: In the Mixed authentication mode, logins can be authenticated by the Windows domain controller or by SQL Server itself. DBAs can create logins with passwords in SQL Server. With the release of SQL Server 2005, MS has introduced password policies for SQL Server logins. Mixed mode authentication is used when one has to run a legacy application and it is not on the domain network.

In my opinion, Windows authentication is good because we can use various handshake methods such as PKI, Kerberos, EAP, SSL NAP, LDAP, or IPSEC to tighten the security.

SQL Server 2005 has enhancements in its security mechanisms. The most important features amongst them are password policy, native encryption, separation of users and schema, and no need to provide system administrator (SA) rights to run profiler.

These are good things because SA is the super user, and with the power this account has, a user can do anything on the SQL box, including:

  • The user can grant ALTER TRACE permission to users who require to run profiler

  • The user can create login and users

  • The user can grant or revoke permission to other users

A schema is an object container that is owned by a user and is transferable to any other users. In earlier versions, objects are owned by users, so if the user leaves the company we cannot delete his/her account from the SQL box just because there is some object he/she has created. We first have to change the owner of that object and then we can delete that account. On the other hand, in the case of a schema, we could have dropped the user account because the object is owned by the schema.

Now, SQL Server 2008 will give you more control over the configuration of security mechanisms. It allows you to configure metadata access, execution context, and auditing events using DDL triggers — the most powerful feature to audit any DDL event.

If one wishes to know more about what we have seen till now, he/she can go through the following links:




In this section, we understood the basics of domains, domain users, and SQL Server security. We also learned why security is given so much emphasize these days.

In the next section, we will understand the basics of clustering and its components.