Create Database Snapshot

CREATE DATABASE statement can also be used to create database snapshots of existing databases.

Database snapshot is a read only static view of a SQL Server database.Snapshot is transitionally consist with the source database as of the snapshot creation. A database snapshot always resides on the same server instances as it source database. As the source database updates the snapshot is updated.
In database snapshot creation in CREATE DATABASE statement we have to add AS SNAPSHOT OF clause in CREATE DATABASE statement others  are same as general CREATE DATABASE statement.   ( CREATE DATABASE )

Sample code :- 

Read More

Create Database in SQL Server 2012

  This example query create table named as db_name  in this case I omitted PRIMARY option and the first file is assumed as a primary file.The logical name of this file is DB_data as I mentioned in query. File name parameter is for specify physical location for the database file. db.mdf in Local disk D:  in my hard drive.

The original size of this file is 10MB ,Additional 20MB from disk may allocated by the system if it needed(FILEGROWTH).
If MAXSIZE option is not specified or it set to unlimited the file will dynamically use all space in disk as it grows.

If you need to use single transaction log file with specific logical name and destination you can use it as follows. parameters are specified are uses as same in ON category.


Full Query for create Database statement

 

Read More

Policies Management In SQL Server

New SQL Server versions upgrade with new Declarative Management Framework (DMF) which policy based system managing one or more server instances, Databases or other Database objects called managed targets. A policy is a DMF condition together with the corresponding behavior and it can contain only one condition.

First before create policy we must have to create condition for policy.
Open SQL Server Management studio  and expand Policy Management 

• R-Click on the condition and select New Condition
• In wizard type condition name and choose facet that you need.In my case I want to apply this condition to all server instances therefore I choose Server Configuration from drop down menu.
• In field select that field that you want and operator  and value as you wish.Or else you can use variable as a value. Click Ok  and create condition.

Now go to the Policies folder in Policy Management

1. R-click Policies and select New Policy
2. Type name and select condition from drop down Menu.
3. In evolution field select on demand (in My Case) and make other fields as it is on default. and clock OK

To test the policy
Expand the Policy Folder and select policy that we created and Evaluate policy(In SQL Server 2008 and previous select Run now) The dialog box notifies you whether the policy fails for existing DB's.

Read More

Introdiuction to Query Optimizer

     In Practical case most of the DBE's can not find the query that optimized execution process of the query.
          The question that arises when database Engine executes a query is how the data that is necessary for the query can be accessed and process most efficient manner. The component of the Database Engine that responsible for such  thing called query optimizer.
           The task of the query optimizer is to consider variety of possible execution strategies for querying the data relation to given query and select most efficient strategy for executing and it is called execution plan. The optimizer make its decisions using considerations such as How big the tables are that involved with the query and what indices exist, What Boolean operations are use in WHERE closes like statistics. There are different tools that can edit query optimize in SQL Server. We will discuss that in later posts.

Read More

Requirements for Database Security

Ever since, men had learned and concentrated on ways on how they could protect their files and documents even before computers ever existed. Today, now that most documents are files are saved in computers, while some other important data are usually located in the company's database, men wanted to make sure that they'll practice security and ensure that all of their business' files and data are kept secured - this is the reason why database security and network security services are usually being sought, along with some security tools that could further help them keep their files and documents safe and secured at all times. As a database administrator we have to think about several requirements of database for security implementations.

Physical Database Integrity :- The data in database are directly relate with the physical (external) factors of the Database Server such as Power failures, Some one can drop the database from database server or any hardware damages in database server.

Logical Database Integrity :- The structure of database, If some of elements are changed in one field and it should not be affected to the other fields in database.

Element Integrity :- Each element which data is contained must be accurate.

Auditability :- Must possible to track who accessed or modified data in the Database.

Access Control :- Authorized users only have to access the certain set of data.( restrict read and write ability of data to different users as application behave)

User Authentication :- Every user must correctly identified for audit and access control.

Availability :- If user have permission to access certain data it must be available of user.

Read More