Making Availability of Data and Preventing Data Loss in SQL Server

To keep database secure and reliable then here is some expect that make sql server to most

power database management system in microsoft technologies:

• Keeping your hardware secure
• Taking advantage of database mirroring
• Creating checkpoints
• Keeping your database running with clustering
• Producing database snapshots
• Backing up and restoring data

In a connected world, your colleagues and customers need almost continuous

access to data. This means that you need to avoid users temporarily

losing access to data. Or, if you can't completely avoid such temporary problems,

make sure that you can recover from them quickly. More important,

you must take careful steps to ensure that the chance of users permanently

losing access to data is as close to zero as possible.

SQL Server 2005 supports many features that improve the chances of keeping

your SQL Server databases available to users. For example, database mirroring

is a new feature that allows almost instant switching to a backup SQL

Server if a primary server goes down.

As well as achieving high availability of data, it is crucially important that you

avoid the permanent loss of any business data that is needed for the running

of your business. Losing important business data can be fatal for your continued

employment and can, in some cases, also be fatal for the business.

Taking appropriate steps to back up data and ensure that you can restore it is

of enormous importance.

Reducing Downtime with Database Mirroring

Database mirroring is an option to improve the availability of a SQL Server

database or databases. Database mirroring is new in SQL Server 2005. You

choose to mirror the databases on a SQL Server instance on a database-bydatabase

basis.

Note:

Database mirroring was intended to be available in the November 2005

release of SQL Server 2005. Microsoft has delayed support of the database

mirroring feature in a production environment, although you can enable it in

the November 2005 release for evaluation purposes by using trace flag 1400.

Microsoft recommends that you do not use database mirroring in the original

release in a production environment.

Database mirroring overview

You have three server machines in a common setup for database mirroring.

One machine (the principal) has the copy of a database that applications read

and write to. Another machine (the mirror) has a copy of the principal database.

The mirror database is kept almost instantaneously in synch with the

principal database via a network connection. All transactions that are applied

to the principal database are also applied to the mirror database.

You might wonder how, with two copies of the data, applications know which

copy of the database to read and write to. The third machine is a witness and

has the "casting vote" as to which of the other two machines is running the

principal database.

Database mirroring gives very fast switching if the principal database

becomes unavailable. Typically, it takes less than three seconds to be up and

running again, using the mirror database. Many users don't notice an interruption

of response; at the most, perhaps just a slightly slower response than

normal.

Microsoft claims zero data loss for database mirroring. Transactions are sent

to the mirror database's log at the same time as they are written to the principal

database's log. The chances of any transaction being lost on the mirror

are extremely low.

Note: You cannot mirror the master, msdb, tempdb, or model databases.

You can switch control to the mirror database either manually or automatically.

Given that one of the advantages of database mirroring is the really

rapid switching that can occur automatically, I envisage automatic switching

being the typical scenario.

Another useful feature of database mirroring is that any changes that are

made on the new principal database (the former mirror database) are automatically

synchronized with the former principal database when the former

principal server is available again.

You can use database mirroring together with replication. For example, if

you're replicating the data from a headquarters SQL Server instance to

branch offices, all or any of the headquarters or branch office instances to

which replication takes place can be a database mirroring configuration.

While replication and database mirroring are separate processes, you can, in

appropriate circumstances, usefully combine them.

 

Transparent client redirect

Database mirroring depends on a companion new technology on the client

side that is called transparent client redirect. Essentially the client knows

about both the principal database and the mirror database. While the principal

database is working correctly, the client only connects to it. When the

principal database fails and the former mirror database becomes the new

principal database, the client automatically connects to the new principal

database.

Database views

You can use another new feature, called database views, with database mirroring.

Database views allow you to make read-only use of the mirror database.

The mirror database is only minimally out of synchronization with the

principal database, because the transaction log of the principal database is

immediately sent to and applied to the mirror database. For any data

retrieval that doesn't require absolutely up-to-date, real-time information, the

mirror database is satisfactory. Any database access that involves writing to

the database must use the principal database.

One important potential use of database views is as the data source for

Reporting Services. Because reporting requires only read access to the database,

you can retrieve any data you need while taking some load off the principal

database.

 

Differences from failover clustering

I list here some key differences between database mirroring and failover clustering:

 

 Database mirroring allows failover at the database level. Failover clustering

   allows failover at the server level or SQL Server instance level.

 Database mirroring works with standard computers, standard storage,

    and standard networks. Failover clustering requires specific, certified

    hardware.

 Database mirroring has no shared storage components. Failover clustering

    uses shared hard drives.

 Database mirroring allows Reporting Services to run against the

    mirror database. Reporting Services cannot be run against a currently

    inactive node in a failover cluster.

 Database mirroring has two (or more) copies of a database. Failover

    clustering works with a single copy of databases, which are stored on

    shared hard drives.

 Database mirroring is much faster than failover clustering. Typical figures

    might be 3 seconds versus 60 seconds, although exact figures

    depend on various factors specific to your setup.

 

Similarities to failover clustering

The following points apply to both database mirroring and failover clustering:

 Both support automatic detection and failover.

 Each has a manual failover option.

 Each supports transparent client connection to the backup database or

    server.

 Each achieves "zero" work loss.

 Database views minimize the effects of DBA or application errors.