Merge replication in sql server 2008

Suppose you are working as Database administrator for your company and requirement is to replicate table’s data to another sql server instance installed on reporting server. In this way, they want to reduce network traffic, those users who use to generate reports based on one day old data. Now after replicating data, they will connect ...

Split partition in sql server

Sql server 2005 introduced partitioning to segregate large table data into separate filegroup. Split and merge partitoin used to manage table data as per requirement. Split partition helps you to move data in different filegroup, Sql server enterprise edition supports partitioning of tables that improves query performance and makes easy for DBAs to handle large ...

SQL Server – Msg 229, level 14, State 5, Line 1 The SELECT permission was denied on database object

To maintain data integrity of the database, DBA has to restrict unauthorized access to the table or database objects.  Company has their own security policies to protect customer data that sometime make hard for end user to access any information. Application developers use to write procedure to generate business report for client. While executing procedure, ...

SQL server skips identity value after error

Strange behavior of sql server which skips identity value after generating error. We created a table with identity key and one unique key column, added some records in it while inserting duplicate values in the table as expected sql server throws error saying violation of unique key.  After removing duplicate value when we are trying ...

How to rollback truncated data in sql server

Truncated data can be rollback if you are using transaction in sql server. If transaction is committed then can not be recovered as its not logged operation. Whenever you truncate any data, sql server deallocate data pages related to the table. Truncate command comes under DDL operation in sql server so only deallocation of data pages ...

Example of Change Data Capture in sql server 2008 1

Lets understand this feature with help of some examples, here we will create test database with dummy table and data to perform DML operation like INSERT, UPDATE and DELETE. create database TestDb use testdb create table st_trackingdata (st_name varchar(10), Grade char(1) ) Lets add one dummy data in table with out enabling CDC (Change data capture) feature for database. insert into st_trackingdata values(‘sandeep’,’B’) If you are ...

How to enable Change Data Capture on one or more Tables

As you know Change data Capture (CDC) feature is applied on table level to any database which having CDC enabled. First you have to enable CDC for database then only for table in the database which needs to be tracked by executing sp_cdc_enable_table with some parameter like schema name and table name that you want to track. If you ...

Change data capture in sql server 2008 1

Another great feature has been added in sql server 2008 that is Change data capture(CDC). As name suggests, it keeps track on data change in the real production environment. It records all DML operations like INSERTs, UPDATEs and DELETEs to sql server tables. One can easily find out what modification has been done on the ...

Restore sql server 2008 backup to sql server 2005

If you ask me, can you able to restore sql server 2008 backup to sql server 2005 environment ? my answer is No. It is not possible to restore backup of higher to lower versions of sql server due to backward compatibility issue. if you will try to restore, it will throw error like “restore headeronly is terminating ...

How to validate Cluster configuration on window 2008 server

It’s good to perform cluster validation tests on window server 2008 environment whether using existing cluster setup or new one. Cluster validation wizards will help to validate your cluster environment. It used to perform all cluster configuration, system configuration, network and storage related tests to validate your cluster. Using cluster validation wizard, you can troubleshooting ...