How Database Snapshots Work

A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).

Database snapshots are dependent on the source database. The snapshots of a database must be on the same server instance as the database. Furthermore, if that database becomes unavailable for any reason, all of its database snapshots also become unavailable.

Snapshots can be used for reporting purposes. Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation. Also, creating a database snapshot can be useful immediately before making a major change to a database, such as changing the schema or the structure of a table. For more information on the uses of snapshots, see Typical Uses of Database Snapshots.

Understanding how snapshots work is helpful though not essential to using them. Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file. For more information about sparse files, see Understanding Sparse File Sizes in Database Snapshots.

Source: http://msdn.microsoft.com/en-us/library/ms187054.aspx

Service Principal Names

service principal name (SPN)

A service principal name (SPN) is the name by which a client uniquely identifies an instance of a service. If you install multiple instances of a service on computers throughout a forest, each instance must have its own SPN. A given service instance can have multiple SPNs if there are multiple names that clients might use for authentication. For example, an SPN always includes the name of the host computer on which the service instance is running, so a service instance might register an SPN for each name or alias of its host. For more information about SPN format and composing a unique SPN, see Name Formats for Unique SPNs.

Before the Kerberos authentication service can use an SPN to authenticate a service, the SPN must be registered on the account object that the service instance uses to log on. A given SPN can be registered on only one account. For Win32 services, a service installer specifies the logon account when an instance of the service is installed. The installer then composes the SPNs and writes them as a property of the account object in Active Directory Domain Services. If the logon account of a service instance changes, the SPNs must be re-registered under the new account. For more information, see How a Service Registers its SPNs.

 Transparent Data Encryption (TDE)

You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

Important
TDE does not provide encryption across communication channels. For more information about how to encrypt data across communication channels, see Encrypting Connections to SQL Server.

After it is secured, the database can be restored by using the correct certificate. For more information about certificates, see SQL Server Certificates and Asymmetric Keys.

Source:

http://msdn.microsoft.com/en-us/library/bb934049.aspx

Database Mirroring Overview

Database Mirroring Overview

In database mirroring, an originating SQL Server 2005 instance continuously sends a database's transaction log records to a copy of the database on another standby SQL Server instance. The originating database and server have the role of principal, and the receiving database and server have the role of mirror. The principal and mirror servers must be separate instances of SQL Server 2005.

In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database's log buffer in memory, and then flushed to disk (or 'hardened') as quickly as possible. In database mirroring, as the principal server writes the principal database's log buffer to disk, it simultaneously sends that block of log records to the mirror instance.

When the mirror server receives a block of log records, it places the log records first into the mirror database's log buffer and then hardens them to disk as quickly as possible. Those transaction log records are later replayed on the mirror. Because the mirror database replays the principal's transaction log records, it duplicates the database changes on the principal database.

The principal and mirror servers are each considered a partner in the database mirroring session. A database mirroring session consists of a relationship between the partner servers when they mirror a database from one partner to another. A given partner server may have the principal role for one database and a mirror role for a different database.

In addition to the two partner servers (principal and mirror) a database mirroring session may have an optional third server, called the witness. The witness server's role is to enable automatic failover. When database mirroring is used for high availability, if a principal server suddenly fails, if the mirror server has confirmation from the witness, it can automatically take on the role of principal and make its database available within a few seconds.

Some important items to note about database mirroring:

• The principal database must be in the FULL recovery model. Log records that result from bulk-logged operations cannot be sent to the mirror database.
• The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.
• The mirror database must have the same name as the principal database.
• Because the mirror database is in a recovering state, it cannot be accessed directly. You can create database snapshots on the mirror to indirectly read the mirror database at a point in time. (See 'Database Mirroring and Database Snapshots' later in this paper.)
  
  
  Source: http://technet.microsoft.com/en-us/library/cc917680.aspx
  
  
  
  

Delete by Using INNER JOIN SQL Server

Delete by Using INNER JOIN SQL Server


select * from CDR_0(nolock) a inner join CDR_Aug11(nolock) b on a.MSISDN = b.MSISDN
where (a.file_id=1526046) and a.b_number = b.b_number and a.charging_timestamp = b.charging_timestamp




Delete a from CDR_0(nolock) a inner join CDR_Aug11(nolock) b on a.MSISDN = b.MSISDN
where (a.file_id=1526046) and a.b_number = b.b_number and a.charging_timestamp = b.charging_timestamp

Use of Group by with Update Query

Update a set a.CDRCOunts = ISNULL(a.CDRCOunts,0) + b.CDRCount
from dbmonitor.dbo.T_SCFFIles(nolock) a Inner join (Select file_id,COUNT(*) as CDRCount from CDR_Aug11(nolock) group by file_id) b

on a.fileid = b.file_id