Wednesday, March 16, 2011

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.
 
 

No comments :

Post a Comment