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

Consume Service References Using Windows Strore Apps

Please note that this is not database related post. I'm recently working on windows store app development. In one scenario I have been pushed to consume .asmx web services. At that time there were no other resources to follow for me. I found this methods using some unorthodox way. In last few days several of individuals ask about consuming web services in Store apps specially .asmx.

Here we go,

1. Add service reference to the project. (Service1)

2. Create   Service1SoapClient object for consume service

3.  Assume that your imported Service1 contains method that returns string and method is GetServiceData(). Therefore you need to create object called GetServiceDataResponse nameOfResult  

nameOfResult variable gets response asynchronously  from web service

 4.  Then you can get the response body (actual response we need) fro called nameOfResul like this.

 



5. Then you can use this string as you wish. This string is the actual string that return by GetServiceData()

Enjoy..... :)

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

Document Oriented Database

        Designed for storing, retrieving, and managing document-oriented, or semi structured data, information. Document-oriented databases are one of the main categories of so-called NoSQL databases and the popularity of the term "document-oriented database" (or "document store") has grown with the use of the term NoSQL itself. In contrast to well-known relational databases and their notions of "Relations" (or "Tables"), these systems are designed around an abstract notion of a "Document".           
 At the highest level of organization, it is quite similar to a relational database, but as you get closer to the data itself, you will notice a significant change in the way the data is stored. Instead of databases, tables, columns, and rows you have documents. A document doesn’t have a direct correlation in the relational world. Documents do not have a predefined schema like relational database tables. A document is partly a row, in that it’s where the data is located, but it's also part columns, in that the schema is defined in each document (not table-wide).

          The best way to think of a document is as a multidimensional array. In an array, you have a set of keys that map to values. The values could themselves be another array. Documents map extremely well to objects and other PHP data types like arrays and even multidimensional arrays. Documents encapsulate and encode data (or information) in some standard formats or encoding. Encoding in use include XML, YAML, JSON, and BSON, as well as binary forms like PDF and Microsoft Office documents.

           Currently lot of web based online shopping websites are moving to this kind of databases. Simple and amaizing handling with database solutions like MongoDB, Apache CouchDB , Clusterpoint etc

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

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