Oct 31, 2009

Replication on SQL Server 2008.

One of the High Availability feature of SQL Server is Replication. Replication shares the selected tables, views or procedures among other servers. In fact Replication is not fully High Availability solution, but it can helpful for reporting servers and Warehouse etc.
There are three models of Replication supported by SQL Server 2008. Transaction Replication, Merge Replication and Snapshot Replication.
Snapshot Replication This is most simplest model of Replication. Snapshot Replication just restores the initial set of data and does not support any incremental restore of logs.
Agent: Snapshot Agent, Distribution Agent
Transactional Replication This is most common replication model. Typically this Model starts with initial snapshot. Using this model, Log Reader service reads the transactions and send them over to Distribution Server. Distribution server then distributes the transactions to all subscribers. This entire process by default runs continuously, however this can be scheduled to run on specific intervals.
Agents: Snapshot Agent, Log Reader Agent, Distribution Agent.
imageFigure Courtesy SQL Server BOL: Transaction Replication
Merge Replication Merge Replication is Similar to Transaction Replication, Merge Replication also typically starts with Snapshot of initial data. Once initial snapshot is applied, it merges the changes occurred at publisher or subscriber.
Merge Replication requires UniqueIdentifier type column in each published table. If this column type is not already added, it adds this column. To track changes, Merge Replication also adds Insert, Delete and Update Triggers for each of the articles (aka Published Objects). These triggers use the UniqueIdentifier type column in each table to identify the changes, which was added above.
Agents: Snapshot Agent, Merge Agent.
imageFigure Courtesy SQL Server BOL: Transaction Replication
There can either be Push Subscription or Pull Subscription. In case of Push Subscription, Distribution Agent and Merge Agent runs at the Distributor. However, in case of Pull Subscription, Distribution Agent and Merge Agent runs at Subscriber.
Each agent of Replication is well described on BOL too. Please click here.

Enabling Distribution on SQL Server 2008.

Oct 5, 2009

Windows NT user or group 'Domain\User' not found. Check the name again. (Microsoft SQL Server, Error: 15401)

When you add new Domain Login to SQL Server, you may see error  Windows NT user or group 'Domain\User' not found. Check the name again. (Microsoft SQL Server, Error: 15401).

This error message is very general and it does not explain any specific problem or reason itself. Microsoft has a very good article to fix this issue at http://support.microsoft.com/kb/324321/en-us.

However, there is another scenario which is not covered in above article (at the time when writing this article) or may be I am the first person who faced this issue.

This scenario can be reproduced on Windows 2008 Server with SQL 2008 Server where the Domain Controller is Windows 2000 server. On Windows 2008 server, 2 new policies have been enabled by default that encrypts the secure channel data when new LOGIN request is sent to Domain Controller by Domain member(Also SQL Server). In this scenario, Domain Controller is Windows 2000, thus it does not understand the encrypted request thus refuses the LOGINrequest. All you need to do is to fix this behavior in Windows 2008 (SQL Server) to not to send encrypted secure channel data to Domain Controller. To do this follow the steps below and it should fix the issue.
  1. From the SQL Server running Windows 2008 R2, Click Start-> Run and type the command GPEDIT.MSC. This will open the Policy Editor.
  2. From Policy Editor Expand “Computer configuration” - > Windows Settings -> Security Setttings -> Local Policies -> Security Options.
  3. You will see all security policies on right hand side window. Make changes into the following two policies.
    • Domain member: Digitally encrypt secure channel data (when possible) – Disable this policy
    • Domain member: Digitally sign secure channel data (when possible) – Disable this policy
After making these changes, close the policy editor and reboot the box. (Not SQL Server, but restart entire system).

In case your local policy does not allow you to make changes, you may have to make changes using Group Policy Management Console. Instructions to install GPMC are located at http://blogs.technet.com/askds/archive/2008/07/07/installing-gpmc-on-windows-server-2008-and-windows-vista-service-pack-1.aspx.

  • Run gpmc.msc (Group Policy Management)
  • Expand your Domain
  • Go to and select and then follow steps 2 and 3 from above.

Optimizing Indexes with Execution Plans

Contact Me

Name

Email *

Message *