Task 16: Replication

                                                   Replication

Replication is the mechanism for creating and maintaining multiple copies of the same data. Replication allows:
• Multiple copies of data can be kept in sync. Load balancing. Replication allows you to disseminate your data to a number of servers and then distribute the query load among those servers.
• Allows data be closer to users, improving performance. Redundancy. Replication allows you to build a fail-over database server that’s ready to pick up the processing load at a moment’s notice.
• Allows a branch office to work when not connected to the main office. Offline processing. You may wish to manipulate data from your database on a machine that is not always connected to the network.
• Separate process and offload work from production servers

What are the Components of Replication

Following are the important components in replication setup
• A publication contains articles.
• The publisher either distributes the magazine directly or uses a distributor.
• Subscribers receive publications to which they have subscribed.

What are the Server Roles Involved in Replication ?

SQL Servers can be assigned three different roles in a replication topology:
• Publishers create and modify data. Publishers have data to offer to other servers. Any given replication scheme may have one or more publishers.
• Distributors store replication-specific data associated with one or more publishers.
• A subscriber holds a replica copy of the data. Subscribers are database servers that wish to receive updates from the Publisher when data is modified.

What are the type of replication available in SQL Server ?

SQL Server supports

Snapshot replication sends the entire data set to the subscriber. The publisher simply takes a snapshot of the entire replicated database and shares it with the subscribers. You don't use this replication type for databases that change frequently. 
Transactional replication only sends modifications to the data. The replication agent monitors the publisher for changes to the database and transmits those changes to the subscribers.
Merge replication items are modified at both the publisher and subscribers. Allows the publisher and subscriber to independently make changes to the database. Both entities can work without an active network connection. When they are reconnected, the merge replication agent checks for changes on both sets of data and modifies each database accordingly.

Here you can find how to configure the data replication in SQL Server 2008

You may wish to implement replication in your organization for a number of reasons, such as:

• Load balancing. Replication allows you to disseminate your data to a number of servers and then distribute the query load among those servers.
• Offline processing. You may wish to manipulate data from your database on a machine that is not always connected to the network.
• Redundancy. Replication allows you to build a fail-over database server that’s ready to pick up the processing load at a moment’s notice.

Task 15: Maintenance Plan

To create a MaintenancePlan is pretty easy because there is a wizard there and the tasks are already defined.

Example of MaintenancePlan created on the project I work for: 

Maintenance plans can be created to perform the following tasks:

• Reorganize the data on the data and index pages by rebuilding indexes with a new fill factor. Rebuilding indexes with a new fill factor makes sure that database pages contain an equally distributed amount of data and free space. It also enables faster growth in the future. For more information, see Specify Fill Factor for an Index.
• Compress data files by removing empty database pages.
• Update index statistics to make sure the query optimizer has current information about the distribution of data values in the tables. This enables the query optimizer to make better judgments about the best way to access data, because it has more information about the data stored in the database. Although index statistics are automatically updated by SQL Server periodically, this option can force the statistics to update immediately.
• Perform internal consistency checks of the data and data pages within the database to make sure that a system or software problem has not damaged data.
• Back up the database and transaction log files. Database and log backups can be retained for a specified period. This lets you create a history of backups to be used if you have to restore the database to a time earlier than the last database backup. You can also perform differential backups.
• Run SQL Server Agent jobs. This can be used to create jobs that perform a variety of actions and the maintenance plans to run those jobs.

Task 14: Indexes

   Here you can find a link that provide you with detailed information about indexes:

Index Overview, Index Architecture, Clustered Indexes, Nonclustered Indexes, Covering Indexes, How Indexes Improve Performance, Index Uniqueness, Data Pages & Extents, How Indexes Are Stored, The Relationship Between Index Size and Page Size, Other Things Stored in the Index, Retrieving Database Page Metadata, Index Statistics, Best Practices on How to Design Database Indexes, Maintaining Indexes for Top Performance

Index fragmentation is a subject that I would like to cover next. 

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

Detecting Fragmentation

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The result set returned by the sys.dm_db_index_physical_stats function includes the following columns.

avg_fragmentation_in_percent value > 5% and < = 30%  ALTER INDEX REORGANIZE

avg_fragmentation_in_percent value  > 30% ALTER INDEX REBUILD WITH (ONLINE = ON) 

USE AdventureWorks2012;
GO
-- Find the average fragmentation percentage of all indexes
-- in the HumanResources.Employee table.
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
-- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table.
ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee
REORGANIZE ;
GO
-- Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE ;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B-tree structure of a SQL Server index

Task 12: Report in Power View

I found a cool Sharepoint tutorial which learn you how to Create a Sample Report in Power View
http://technet.microsoft.com/en-us/library/hh759325%28v=sql.110%29.aspx

First of all I got many access problems with Sharepoint site:
1. Trying to create a new folder in Shared docuemnts I got the following error:

2. In New Document  I could not found the Report Data Source option. (If there is no Report Data Source option on the New Document tab, then someone with adequate permissions on the

SharePoint site needs to add that content type to the site. For more information, see Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode).) 

The issues were resolved by our Configuration Engineer.

 You can use this tutorial anytime when you need to create a simple power view report using data source 

saving the excel file as SharePoint document library of PowerPivot Gallery. 

Task 11: Administering Microsoft SQL Server 2012 Databases certification

I passed the Querying Microsoft SQL Server 2012 certification two days ago and I still dream about all 13 questions I didn't answered correctly or completely. I'll continue with the next 2 certifications:

For 70-462 there is a Training Kit available to free download.

Task 10: Halloween Problem

Did you heard about Halloween Problem?

In computing, the Halloween Problem refers to a phenomenon in databases in which an update operation causes a change in the physical location of a row, potentially allowing the row to be visited more than once during the operation. This could even cause an infinite loop in some cases where updates continually place the updated record ahead of the scan performing the update operation.

Please have a look  to understand what is the meaning of this problem through an example:

-------------------------------------------------------------------------------
--- Halloween problem script ---
-------------------------------------------------------------------------------
CREATE TABLE halloweenProblem 
(
     Pk int NOT NULL PRIMARY KEY, 
     Name varchar(20), 
     Salary money
)
insert into halloweenProblem(pk, Name, Salary) values(1, 'Joe Blow', 20000.00)
insert into halloweenProblem(pk, Name, Salary) values(2, 'Joe Smith', 30000.00)
insert into halloweenProblem(pk, Name, Salary) values(3, 'Jane Doe', 40000.00)
insert into halloweenProblem(pk, Name, Salary) values(4, 'Boss Man', 50000.00)

DECLARE RaiseSalary CURSOR
FOR 
            SELECT salary, pk 
            FROM halloweenProblem 
            WHERE salary < 50000 
            ORDER BY pk
FOR UPDATE  OF pk

OPEN RaiseSalary 
FETCH RaiseSalary
             WHILE ( @@fetch_status =0)
             BEGIN

             UPDATE halloweenProblem 
             SET pk=pk*10  
             WHERE CURRENT OF RaiseSalary

             SELECT * FROM halloweenProblem 
             FETCH RaiseSalary
             END
CLOSE RaiseSalary 
DEALLOCATE RaiseSalary 


DECLARE abc CURSOR  FOR
             SELECT salary
             FROM halloweenProblem 
             WHERE salary < 50000 
             ORDER BY salary
FOR UPDATE OF salary

OPEN abc
GO

FETCH NEXT FROM abc
GO

UPDATE halloweenProblem  
SET salary= salary*2
WHERE CURRENT OF abc
GO
CLOSE abc
DEALLOCATE abc
------------------------------------------------------------------------------

Task 9: Simple Q&A

• I have a direct relationship with my clients and we're frequently confronted with interesting Q&A. For this task I'll update the questions and answers in progress. 

1Q. In order to move the DataWarehouse from production to development server, can we just move the DataMart.mdf, DataVault.mdf , StagingArea.mdf and the log files *.ldf from one server to another?

1A. It’s not so easy to move the data from one server to another.

We need to back-up all databases from production server and restore them to the development one. Or use attach-detach commands for the same scope. We also need to pay attention on disk space for back-up and also to the VPN connection between user and servers.

2Q. The back-up should be placed on development server Local Disk(U:). This is a disk that’s not “snapshotted”.On production server you can map to this disk. Before starting the restore on development server we have to stop “snapshot”. Can we do in this way the back up procedure?

2A. I’m afraid this option could not be possible because the database back-up can be done only on local disks, not shared or mapped one. We need a disk with sufficient space for back-up directly on production server because the mapped disk does not appear in backup location list.

Restore database backup over the network

SQL Server backup across the network

3Q. How to resolve the error received  while processing a cube using the BIDS (Business Intelligence Development Studio) : "The following system error occurred: The trust relationship between the primary domain and the trusted domain failed." 

3A. This error occurred during cube processing might occur because of inapprotiate role definitions between the development and the target deployment environments of SSAS applications.  Drill down to the Roles folder in the Solution Explorer of the SSAS database solution. A list of roles will be listed under the Roles folder as seen in the following screenshot. 

If you double-click the role definition and navigate to the Membership tab in the role definition screen, you will see a list of users and groups assigned for this role. These assigned users and members of these assigned groups have the privileges and required permissions in all of the SQL Server Analysis Services objects within the SSAS database.  If you remove the user or the user group that is defined for the SSAS role object using the Remove button, you can handle this error and finish the deployment and so the process of the cube successfully.