Data Replication with SQL Server 2012

Replication is the process of copying and maintaining database objects in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Replication provides user with fast, local access to shared data, and protects availability of applications because alternate data access options exist. Even if one site becomes unavailable, users can continue to query or even update the remaining locations.

In SQL Server 2012 Management Studio you can create replication very simply. Replication makes your database more secure and effective

Here the steps ,
1.  Log on to the database server and R-click on the Replication folder and select new publication
 

2. In new publication click next.
3. Select distributor  that can be responsible for storing replication info. I don't have any other servers I select default server in my desktop

4. In SQL Server Agent  select whether SQL Server agent should start automatically or not  then click next

5. Then it will displays snapshot folder.

6. Then select database that you want to replicate. After that it prompt select Publication type to replicate your database.  For each publication type preview window in bottom will show discription about each of them. Select it as you want and click next.

7. Then in Articles we can choose objects to publish in replication. then click next

8. Add data filters if you need. then click next in the prompt
9. Schedule snapshot agent as you want. It will give you time bounded option as well.
10. In Agent security add snapshot agent. I choose SQL Server agent account and finally click finish. and add publication name in prompt.

Its done..

Read More

READTEXT in T-SQL (SS2012)

This statement use to read text, ntext, or image values from a text, ntext, or image column, starting from a specified offset and reading the specified number of bytes.

Parameters of statement

• table.column

Is the name of a table and column from which to read. Table and column names must

comply with the rules for identifiers. Specifying the table and column names is required;

however, specifying the database name and owner names is optional.

• text_ptr

Is a valid text pointer. text_ptr must be binary(16).

• Offset

Is the number of bytes (when the text or image data types are used) or characters

(when the ntext data type is used) to skip before it starts to read the text, image, or

ntext data.

• Size

Is the number of bytes (when the text or image data types are used) or characters

(when the ntext data type is used) of data to read. If size is 0, 4 KB bytes of data is

read.

• HOLDLOCK

Causes the text value to be locked for reads until the end of the transaction. Other

Users can read the value, but they cannot modify it.

Read More

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

Classification

      Classifications are the internal rules that classify the incoming requests and route them into a workload groups. This classification is based on set of user defined function called classifier function. This classifier function will be created in master database. Once DBA enables the resource governor on SQL Server Then each and every single session to the server will be evaluated by the classifier function. It can evaluate various login properties.

• USER_NAME
• HOST_NAME
• WORKSTATION_NAME
• DB_NAME
• SUSER_SNAME

Read More

Workload Groups

             Workload groups are logical entities that represent one or more client workloads. Workload groups act as a container which accepts sessions from SQL Server users based on classification criteria applied to each requests. 

                As in resource pools there are also two predefined workload groups named Internal and Default. Internal workload group cannot be changed but can monitor it to see how SQL Server is utilizing memory and CPU.

Incoming requests to the server are classified in to Default workload group when there is no classification criteria applied for it or there was an attempt made to classify the request in to nonexistent workload group or there is failure in classification. This default workload group is user configurable.

• CREATE WORKLOAD GROUP group_name
  USING pool_name
  GO
   

Option	Description	Default
IMPORTANCE	Importance between workload groups using the same resource pool.	MEDIUM
REQUEST_MAX_MEMORY_GRANT_PERCENT	Max memory grant from the resource pool for query execution.	25%
REQUEST_MEMORY_GRANT_TIMEOUT_SEC	How long to wait for a query execution memory grant.	0
REQUEST_MAX_CPU_TIME_SEC	Max amount of CPU  time a request can use	0
MAX_DOP	Max degree of parallelism.	0
GROUP_MAX_REQUESTS	Maximum concurrent requests per group.	0

Read More