SQL Server Security Checklist

Problem

I have to install a new Microsoft SQL Server box and configure it as a production server. I know that there are many security related settings and I do not want anyone to hack the system on the first day on the network. Which are the basic steps to harden the security of my SQL Server? Do you have a checklist to start with? Check out this tip to learn more.

Solution

There are many security related settings in the Microsoft SQL Server and you should also consider setting up processes to ensure that the security is maintained in the future. The security related tasks can be divided into four main categories: physical security, operating system level security, SQL Server configuration and user management. You should protect your server physically, have a secure OS and then you can start thinking about your SQL Server.

Physical Security

The first line of your security is the physical security of your on premise hardware. You have to protect your server from being tampered with. Here are the basic items I would recommend:

• Limit the number of employees who have access to the physical hardware. You can limit access with access codes, entry cards or even with armed security guards. The most important point is to have a minimal number of people who have access and you should have written procedures to access the server, preferably with managerial approval for physical access rights.
• Store the backup media at a secure offsite location. To increase security, it is recommended to have one location for the production server and a separate location for the backup files.
• Configure alerts for hardware warnings. To be proactive, you can configure e-mail alerts for example with HP iLO or Dell DRAC, but you can also use VMware vSphere.

Operating System Security

We live in the era of the internet, so physical security is not enough. You should build your protection plan to include Windows. The operating system should be secured to reduce the vulnerability of your system. Here are the basic items to consider:

• Install all service packs and critical fixes for Windows (and for VMware if applicable). It is necessary to implement all critical fixes to ensure that there are no known holes on your security. Consider creating a process to apply the latest Windows security patches on a regular basis.
• Configure a firewall. You need a protective firewall on your server to defend your system. If there is no other firewall installed on your server, then configure Windows Firewall to work with the Database Engine, Integration Services and Analysis Services components of SQL Server.
• Limit the number of employees who have Windows Administrator access on the SQL Server. As a best practice, you should limit the administrative access only to those who really require it. This should include the VMware console as well as Remote Desktop Services. Maybe you can consider limiting the OS level admin access to the SQL Server administrators and Network administrators.

SQL Server Installation

Now we reached the point when you can finally work with your beloved SQL Server. There are a number of security related tasks which should be completed before you can start to use a production SQL Server. Here is what I recommend as a starting point:

• Install only the required components. The less installed components, the less security problems can occur.
• Install all service packs and critical fixes for SQL Server. It is necessary to implement all critical fixes to ensure that there are no known holes on your security.
• Disable unnecessary features and services. You can use the SQL Server Configuration Manager to disable unused SQL Server services.
• Disable the unused SQL Server protocols. SQL Server supports four type of protocols: Shared Memory, Named Pipes, TCP/IP and VIA. You should use the bare minimum of these protocols and disable the others using the SQL Server Configuration Manager.
• Change the default SQL Server ports. The SQL Server gets installed with the default TCP port 1433 which is well known by admins and attackers too. You can avoid some targeted SQL attacks if you do not use the default ports. You can change the port in SQL Server Configuration Manager.
• Hide the SQL Server instance and/or turn off the SQL Server Browser Service. You can configure the SQL Server instance as hidden using the SQL Server Configuration Manager. This will prevent advertisement of your server by the SQL Server Browser service. If you use fully qualified connection strings then you can disable the SQL Server Browser.
• Restrict the access to the SQL Server configuration and database file. Apart from the database level access, you should also protect the file system to prevent unauthorized file deletion, copying or alteration of data.
• Restrict the access to the SQL Server backup folders. Read Below tip to learn how to protect your SQL Server Backup folder.
• Use Transparent Data Encryption whenever it is an option. You can secure your data, logs and backup with TDE in Evaluation, Developer, Enterprise and Datacenter versions of SQL Server 2008, 2008 R2 and 2012.
• Create only the required databases. Do not create demo or test databases on production servers, keep it clean and safe.
• Run the SQL Server Best Practice Analyzer to verify your installation. The Microsoft SQL Server 2012 Best Practice Analyzer can quickly identify if your server is configured according to industry best practices or not.
• Revoke execute rights to 'PUBLIC' on extended stored procedures. Extended stored procedures will be removed in a future version of SQL Server and it is not recommended to use them. You can use CLR Integration instead. The following extended stored procedures should not be executed by your applications:xp_availablemedia, xp_dirtree, xp_enumgroups, xp_fixeddrives, xp_regaddmultistring, xp_regdeletekey, xp_regdeletevalue, xp_regenumvalues, xp_regremovemultistring, xp_regwrite, xp_regread, xp_servicecontrol, xp_subdirs.
• Disable the xp_cmdshell option. It is highly recommended to disable the xp_cmdshell stored procedure even if other administrators can enable it again.

User Accounts

After your basic SQL Server security is configured, you can start to address the traditional user access and security topics. The administrators and the service accounts require extra attention. Here is how to get started:

• Rename and disable the SA account if your applications allow it. You can use thesp_SetAutoSAPasswordAndDisable stored procedure to disable the SA account as described in below tip. This will prevent the attacker from trying to login with the default admin account.
• Remove the BUILTIN\Administrators group from the SQL Server Logins. You can read more about the security issues with the SQL Server BUILTIN\Administrators group in Below tip.
• Use Windows Authentication mode. You can check and change the authentication mode in three different ways: using SQL Server Management Studio, with T-SQL or in the Windows registry.
• Every administrator should have a named login, shared logins should not be allowed. This is required in order to be able to identify the people behind each and every database change. It is also critical to have an up-to-date list of all the accounts.
• All accounts for named user access should be controlled by Active Directory. Use Active Directory and do not create SQL Server logins. It makes the administration easier if you grant access rights through Active Directory groups or Group Policy.
• Use service accounts for applications. It is a best practice to create a different service account with a descriptive name for every service. You can use SQL Server logins, but a complex password is a must. Restrict the access only to data required: if an application updates only 1-2 tables then it does not require full control of every object in the database.
• Configure service accounts with the least privileges. You can read Below tip if you would like to know how to determine service related privileges for SQL Server service account. Do not grant more rights than required.
• The user privileges should be minimized. Try to assign the minimum sufficient rights to every user. It is a best practice to document any elevated user permission and request managerial approval.
• All administrator accounts should have a complex password and password change should be enforced. You should identify blank and weak passwords and configure password enforcement options.
• Configure SQL Server login auditing to log both failed and successful logins. Details of the login audit configuration can be found in Below tip and there is also a tip about SYSADMIN login auditing below.

The above checklist can be used to ensure that the minimum requirements are fulfilled. Every company should have an information security policy and you should apply those requirements to your SQL Server as well. In case that policy does not exist then you can use this checklist as the basic checklist.

Tips:  

1.Protecting the SQL Server Backup folder

If you look at the permission for the BACKUPS folder that the Microsoft SQL Server install creates, you've probably noted that it has permissions where members of the Administrators group has full control, meaning they can do anything they want. Actually, this is true of most every location, including a folder I just created off the root of the C:\ drive.

In a lot of cases the Windows Local Administrators group has inherited the permissions from a higher level folder. The solution is to break inheritance and set our own permissions. Without breaking inheritance we can only add permissions to a file or folder. We can't remove permissions, so this is a necessary step. However, to make sure you don't lock yourself out, choose to copy the existing inheritable permissions.

Before you remove any permissions, the first step I'd take is to ensure you have permission to get back and manage the folder. For instance, if all DBAs should be able to get to the appropriate folder, add the appropriate Windows group for the DBAs and give it Full Control. This allows you to modify the folder and any files contained in it as well as manage the permissions. It's this last aspect of Full Control you'll find essential. I typically grant this permission, then click OK to confirm and only then do I start removing or reducing permissions.

When I go to remove permissions, I tend to automatically eliminate the following accounts or groups:

• SYSTEM
• Authenticated Users
• Users

Note I didn't strike the Administrators group. Instead, I tend to reduce permissions.  I still grant the local Windows Administrators the ability to see what's in the folder. Unless the local security policy has been changed, they can backup the files and folders regardless of permissions, but this doesn't typically give them the ability to view what's in the folder without the use of a 3rd party product. When it comes to disk space issues and the like, it can often be helpful for an administrator to be able to see the files. We can accomplish this by granting the following permission:List folder contents. This gives them the ability to see the files, but not read from them.

Most importantly, don't forget to give Microsoft SQL Server access. It will need Modify access for the folder. This gives SQL Server the ability to read files, write to them, modify them, and delete them. In SQL Server 2005-2008R2, there will be a SQL Server User group corresponding to the instance name. In SQL Server 2012 the access will be configured based on the service's virtual account name. Check the appropriate version of Books Online for this. Here's the pattern for SQL Server 2008 and 2008R2:

Type of Instance	Pattern for Group Name
Default Instance	SQLServerMSSQLUser$ComputerName$MSSQLSERVER
Named Instance	SQLServerMSSQLUser$ComputerName$InstanceName

If you don't have the appropriate access, you'll get the following error trying to run a backup through SQL Server Management Studio:

If you get this error, you're likely missing the Modify permission for SQL Server.

And that's all there is to it. While a member of the Windows Local Administrators group could still get in, he or she would have to intentionally seek to bypass security. You can't completely prevent an administrator from gaining access, however, by making it difficult you keep out the merely curious. That's the intent of altering file and folder permissions like we did here.

2.Secure and disable the SQL Server SA Account

SQL Server has an undocumented system stored procedure named sp_SetAutoSAPasswordAndDisable. This procedure will do exactly as the name suggests: it will reset the password and then disable the 'sa' login.

---

The procedure takes no parameters, so the syntax for usage is as follows:

EXEC sp_SetAutoSAPasswordAndDisable
GO

After completion you should see the standard message:

Command(s) completed successfully. 

The actual code is as follows:

ALTER procedure [sys].[sp_SetAutoSAPasswordAndDisable]
as
    -- can execute only as SysAdmin
 if (not (is_srvrolemember('sysadmin') = 1))  -- Make sure that it is the SA executing this.
 begin
  raiserror(15247,-1,-1)
  return(1)
 end
    -- Begin a transaction
    BEGIN TRANSACTION
    
 -- Disable Password Policy on the SA Login
 ALTER LOGIN sa WITH CHECK_POLICY = OFF
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END
 -- Create a New Guid as the random password
 declare @randompwd UNIQUEIDENTIFIER
 declare @stmt nvarchar(4000)
 SET @randompwd = newid()
 SELECT @stmt = 'ALTER LOGIN sa WITH PASSWORD = ' + quotename(@randompwd, '''')
 EXEC(@stmt)
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END
 -- Now set the policy back
 ALTER LOGIN sa WITH CHECK_POLICY = ON
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END 
 -- Now set the policy back
 ALTER LOGIN sa DISABLE
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END 
 -- Commit the transaction
 COMMIT TRANSACTION

When you execute this stored procedure the password for the ‘sa' login will be reset to a random GUID, and then be disabled. Auditors love this aspect because not only is the password secure, but so is the account itself.

If you need to roll your own solution to rotate the password for the ‘sa' login, then the sp_SetAutoSAPasswordAndDisable stored procedure may be exactly what you are looking for.

3.How to determine service related privileges for Sql Server service account

I had a customer inquire as to how to verify service-related permissions for the Sql Server service account...i.e., Books Online indicates the Sql Server service account requires permission to start the following related services (amoung many other requirements): Sql Server Active Directory Helper and Sql Writer services.  Obviously, the service really wouldn't NEED permissions to do so if you weren't going to make use of said services, but let's assume for the moment that we are going to do so, and that you want to determine first of all if the Sql Server service account has these permissions and/or what permissions the account has to services on the machine in question.

There's a tool called "AccessChk" which will allow you to see this type of information, along with lots of other access-related information for given accounts. You can find information on the tool at the following link:

 http://www.microsoft.com/technet/sysinternals/utilities/AccessChk.mspx

As an example, the following command line will give you effective permissions to all services on the local machine for an account named "LTCBOYDMS\sqlService":

 accesschk "LTCBOYDMS\sqlService" -vc *

To determine service-related permissions, the -c option must be used.  Without it, you get by default ACL information for files/folders/shares and other information (which is handy also). If you specify the wildcard character (the *), you'll get permission related information for the given account against ALL services on the machine...if you're looking for only a specific service information, you can replace the wildcard with the appropriate service name.  For example, to get effective permissions to only the SQLWriter service (the service name for the Sql Writer service), you could use the following command:

 accesschk "LTCBOYDMS\sqlService" -vc "SQLWriter"

and, for the Active Directory Helper service, something like the following:

 accesschk "LTCBOYDMS\sqlService" -vc "MSSQLServerADHelper"

Output from the command should look similar to the following:

 C:\Projects\Toolbox\accessCheck>accesschk.exe "DUMMYDOMAIN\svcSqlService" -vc *

 AccessChk v2.0 - Check account access of files, registry keys or services

 RW Alerter
  SERVICE_ALL_ACCESS

 RW ALG
  SERVICE_ALL_ACCESS

 RW MSSQL$SQL2000
  SERVICE_ALL_ACCESS

 RW MSSQL$SQL2005
  SERVICE_ALL_ACCESS

 RW MSSQL$SQL2005B
  SERVICE_ALL_ACCESS

 RW MSSQL$SQLEXPRESS
  SERVICE_ALL_ACCESS

 RW MSSQLServerADHelper
  SERVICE_ALL_ACCESS

 ...

 R  Pml Driver HPZ12
  SERVICE_QUERY_STATUS
  SERVICE_QUERY_CONFIG
  SERVICE_INTERROGATE
  SERVICE_ENUMERATE_DEPENDENTS
  SERVICE_PAUSE_CONTINUE
  SERVICE_START
  SERVICE_STOP
  SERVICE_USER_DEFINED_CONTROL
  READ_CONTROL

 ...

 RW SQLBrowser
  SERVICE_ALL_ACCESS

 RW SQLWriter
  SERVICE_ALL_ACCESS

 ...

 C:\Projects\Toolbox\accessCheck>

In the output, the "RW" designators at the beginning of each line indicate Read/Write privs (if present), then the service name, then using the -v option will provide you the additional output shown below each service above.  The SERVICE_ALL_ACCESS implies all access, if you don't have that, you should see individual service related privs like in the line above for the "Pml Driver HPZ12" service.

4.Auditing Failed Logins in SQL Server

SQL Server permits the auditing of both login successes and failures, depending on your need. The way to turn this auditing on is by using SQL Server Management Studio. Connect to the SQL Server in Object Explorer and then right-click on the SQL Server and choose the Properties option from the pop-up menu. You should see the server properties like so:

You'll want to click on the Security page (highlighted in the previous image) which will bring you to where you can set the login auditing:

There are four options available:

• None - Neither successful nor failed logins will be audited.
• Failed logins only - Failed logins will be audited, but successful logins will be ignored.
• Successful logins only - Successful logins will be audited, but failed logins will be ignored.
• Both failed and successful logins - Login will be audited regardless of success and failure.

When you've made your choice, click the OK button. The setting for login auditing is actually a registry entry which differs based on SQL Server version and SQL Server instance name. SQL Server only reads this setting when the SQL Server service starts up. Therefore, for this setting to take effect, you'll need to restart the SQL Server service. Because it is a registry setting, the only easy way to set how to audit is through the GUI. There isn't a way using T-SQL without using one of the extended stored procedures that hits the registry directly.

A word about the various settings. At the very least, you should be auditing failed logins on production systems. There may be cases where it is necessary to audit successful logins, but realize that auditing successful logins will likely generate a lot of events to sift through. On extremely sensitive systems it is important to have this level of control. However, on less critical systems, you may be better served to only audit failed logins so you don't get overwhelmed with the number of events. If you have a log management solution, by all means audit both.

---

Once you've turned on auditing, the next step is to be able to find the results of said auditing. The first place where the audit information is recorded is the SQL Server log. For instance, an audit success by the "sa" account and an audit failure are shown in the following excerpt:

If you want to view the contents of the SQL Server log by using T-SQL, there's an undocumented (but well known) extended stored procedure called xp_readerrorlog. You can use it to dump the results of the error log to a recordset by:

EXEC xp_readerrorlog;

To read further back, you can give this extended stored procedure an integer parameter which corresponds to the order of the SQL Server log. 0 represents the current log, with each number after that referring to the next one back. So to see the contents of the 3rd log (including the current one) you would pass a parameter of 2 (counting up from 0 - 0, 1, 2 would be the third), you would execute:

EXEC xp_readerrorlog 2;

To search the current error log and only return failed logins you can use the following command.  The first parameter specifies the error log (0=current), the second parameter specifies the type of log (1=SQL Error Log) and the third parameter specifies the message to search for.

 EXEC sp_readerrorlog 0, 1, 'Login failed' 

---

If you have log management software, another good place to look is the Application event log for the operating system. You can view this by using the Computer Management tool. If you expand System Tools and then Event Viewer, you'll see the Application event log like so:

If you look in this event log, you'll be looking for events with a source of MSSQLSERVER or MSSQL$<Instance Name>. For instance, the following shows the audit success and failure events we looked at before in the SQL Server log:

Successful logins for SQL Server 2005 and 2008 will have an event ID of 18454 and failed logins will have an event ID of 18456. SQL Server 2000 uses the same event ID for both, making it impossible to determine of the event signifies a success or failure without looking at the event details. As a result, I would recommend only auditing failures to eliminate the confusion. Once you have determined the correct events, you can look at the event log details to see what login was attempted. For instance, here is the failed login attempt:

 

and here is the successful login attempt:

The details of what account was attempted is stored within the description of the event log. Therefore, if you need to automate the retrieval of audit successes or failures, make sure the application can retrieve the details and parse the text accordingly. 

5. SQL Server SysAdmin Login Auditing

In SQL Server 2005 Service Pack 2, Microsoft introduced logon triggers into the core functionality. Like DDL and DML triggers, these triggers fire on particular events, in this case, whenever a logon to the SQL Server instance occurs. We can use a logon trigger to audit when members of a particular role, such as the syadmin fixed server role, logs on.

In order to audit for members of the sysadmin fixed server role, we will need to use two system views:sys.server_role_members and sys.server_principals. We will join these views when a logon event occurs to determine if the logon is a member of the sysadmin role or not. We can get the members by joining these two views together as shown below:

SELECT sp.principal_idFROM sys.server_role_members srmINNER JOIN sys.server_principals sp     ON srm.member_principal_id = sp.principal_idWHERE srm.role_principal_id = (     SELECT principal_id     FROM sys.server_principals     WHERE [Name] = 'sysadmin')

This query will be the basis for our logon trigger. By adding an AND clause in our final set of code, we will be able to test whether or not the incoming logon is a member of the sysadmin fixed server role.

We will also need to some place to record the event when it occurs. One of the easiest ways to do this is to use a table created for this purpose in a work database. For the purposes of this example, I am going to assume the table can be stored in a DBA database by the name of DBAWork.  Here is the associated code:

CREATE TABLE dbo.AuditSysAdminLogin(AuditEventId INT IDENTITY(1,1) NOT NULL,EventTime DATETIME NOT NULL,ServerLogin NVARCHAR(100) NOT NULL,CONSTRAINT PK_AuditSysAdminLogin PRIMARY KEY CLUSTERED (AuditEventID));GO

Once we have the audit table, we can create our logon trigger. The basic syntax for a logon trigger is similar to a DDL trigger as shown below:

CREATE TRIGGER <trigger name> ON ALL SERVER FOR LOGON AS <SQL Statements>

Following this format and using the query identified above to help identify who is a member of the SysAdmin role, the only aspect we are missing is a way to identify the logon.  There is a system function, ORIGINAL_LOGIN(), which provides that information. Putting it all together, here is our logon trigger:

USE master; GO CREATE TRIGGER trigLogon_CheckForSysAdmin             ON ALL SERVER             FOR LOGON             AS     BEGIN     IF EXISTS (         SELECT sp.principal_id             FROM sys.server_role_members srm                     JOIN sys.server_principals sp                     ON srm.member_principal_id = sp.principal_id             WHERE role_principal_id = (             SELECT principal_id                  FROM sys.server_principals                  WHERE NAME = 'sysadmin')                 AND ORIGINAL_LOGIN() = sp.NAME)         BEGIN         INSERT INTO DBAWork.dbo.AuditSysAdminLogin             (EventTime, ServerLogin)                 VALUES             (GETDATE(), ORIGINAL_LOGIN())         END;     END; GO

Next Steps

• Be sure to have SQL Server 2005 SP2 or later installed in order to have this functionality work properly.  
• This tip provides a simple approach to capture the logins of SQL Server SysAdmins, but can be easily modified for other groups, roles or logins.
• The logic can also be modified to prevent logins from accessing the SQL Server instance during particular hours, to limit the number of sessions, etc.  So think about some of the needs you have in your environment and consider this tip as a stepping stone to address those needs.
• As you experiment with this logic, please provide your problems and solutions in the forum below.  We would love to hear how creative you were with the opportunities available with this favor of SQL Server triggers.

Production Order steps in AX 2009

Production Process

For new production order you need to follow some points.

1. Create Item

2. Create RM for Item

2. Create Route

3. Create BOM

4. Create Production Order

How to create “Item”

Goto Inventory Management module.

Click on Item details button.

Then, click on “New” button to create a new Item, after that enter all required details.

Note: For FG only change Item Type to BOM

Then, go to Setup button and select configuration and sizes buttons. If all required configuration and sizes available in AX then skip this step else you need to create new configuration and sizes.

How to Create BOM

Go to Inventory Management module and click on Bill Of Material button

Then you need to create BOM version and Lines 

How to create Route

Go to Production Control Module and click on Routes Details button.

Then create Route versions and operations.

Then click on Route button for describing operations.

How to create Production Order

Go to Production Control module and click on Production Order Details button

Now, click on New button to create a new Production Order. In this form you need to select FG item with its specification like: Site, WH, Location etc. then it will pick automatically Route and BOM number then enter quantity.

Click on OK button.

It will show Production order with created status.

Now click on update button and execute all operations:

1.    Estimation

2.    Scheduling

3.    Release

4.    Start

6.    Report As Finished

7.    End

Task has been done.

Missing Label File Problems - AX 2012

Problem : 

In some situation could happen that after installing cumulative updates there are missing some SYP labels.

Solution : 

1. Stop AOS
2. Delete ALD and AUC files from Microsoft Dynamics AX\60\Server\<instance>\bin\Application\Appl\Standard
// Note : In search type *.ald, *.alc and delete files

3. Start AOS again.

That's It..

Issue : 
Some times we face the same labels issue after upgrading sql server
Example: upgrading from Sql 2012 to Sql 2014

Solution : 
http://technet.microsoft.com/EN-US/library/hh389762.aspx

SQL Server Replication Step by Step

Introduction

Earlier, most of the applications were using standalone environment where a single centralized server was responding to multiple users, working in different locations.

Centralized Approach and Problems

• Performance problems
• Availability problems
• Maintenance problems

To overcome all the above problems, we can use replication solution.

Replication allows to maintain same database multiple copies at different locations. Log shipping and mirroring allows to maintain complete database redundancy whereas replication allows to maintain some part of the database (a set of required objects) at users location. Changes made at different user locations are synchronized to the main server. It is object level high availability feature. According to Books Online:

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Unlike other methods of high availability, it doesn’t distribute entire database, but only distributes some part of database like tables or views.

Advantages

• Improved performance
• To reduce locking conflicts when multiple users are working
• Improved availability
• Easy maintenance
• To allow sites work independently. So that each location can set up its own rules and procedures for working with its copy of the data.
• To move data closer to the user

SQL Server 2005 Features

• Restartable Snapshots
• Oracle Publishing
• Replicating all DDLs
• Merge Replication allows to introduce custom business logic into the synchronization process
• Merge Replication provides the ability to replicate data over HTTP with web synchronization option
• Updatable Transactional Subscriptions can now handle updates to large data types at Subscribers

SQL Server 2008 Features

• In SQL Server 2005, replication had to be stopped in order to perform some actions like adding nodes, making schema changes, etc. But in 2008, these can be done online.
• Conflict detection capacity in peer-to-peer replication.
• All types of conflicts are detected and reported through agent error log or conflicts table.
• In SQL Server 2005, switch partition is unsupported, but in 2008 it supports.
  • @allow_partition_switch
  • @replicate_partition_switch
• Performance improvements, under Windows 2008
• Snapshot delivery of more than 500MB/minute
• Time to deliver 100000 varbinary(max) records in less than 2minutes where in 2005 223 minutes.

SQL Server 2012 Features

• Updatable subscriptions with transactional publications are discontinued.
• Four new stored procedures provide replication support for AlwaysOn.
  • sp_get_redirected_publisher
  • sp_redirect_publisher
  • sp_validate_replica_hosts_as_publishers
  • sp_validate_redirected_publisher
• Replication supports the following features on Availability groups:
  • A publication database can be part of an availability group. The publisher instances must share a common distributor.
  • In an AlwaysOn Availability Group, an AlwaysOn secondary cannot be a publisher. Republishing is not supported when replication is combined with AlwaysOn.
  • Heterogeneous replication to non-SQL Server subscribers is deprecated. To move data, create solutions using change data capture and SSIS.
  • Oracle Publishing is deprecated.

Replication Architecture

REPLICATION ENTITIES

SQL Server replication is based on the “Publish and Subscribe” metaphor. Let us look at each of the individual components in detail.

1. Publisher
• It is a source database where replication starts. It makes data available for replication.
• Publishers define what they publish through a publication.
2. Article
• Articles are the actual database objects included in replication like tables, views, indexes, etc.
• An article can be filtered when sent to the subscriber.
3. Publication
• A group of articles is called publication.
• An article can’t be distributed individually. Hence publication is required.
4. Distributor
• It is intermediary between publisher and subscriber.
• It receives published transactions or snapshots and then stores and forwards these publications to the subscriber.
• It has 6 system databases including distribution.
5. Subscriber
• It is the destination database where replication ends.
• It can subscribe to multiple publications from multiple publishers.
• It can send data back to publisher or publish data to other subscribers.
6. Subscription
• It is a request by a subscriber to receive a publication.
• We have two types of subscriptions - push and pull.
7. Push Subscriptions
• With this subscription, the publisher is responsible for updating all the changes to the subscriber without the subscriber asking those changes.
• Push subscriptions are created at the Publisher server
8. Pull Subscriptions -
• With this subscription the subscriber initiates the replication instead of the publisher.
• The subscriptions are created at the Subscriber server.

REPLICATION AGENTS

• We have discussed that replication process works in the background with the help of jobs.
• These jobs are also called as agents. These jobs internally uses respective .exe files present in …………….. \110\COM folder.
• All the agents information is present in Distribution db in the following tables.
  • dbo.MSxxx_agents
  • dbo.MSxxx_history

Snapshot Agent

• It is an executable file that prepares snapshot files containing schema and data of published tables and db objects.
• It stores the files in the snapshot folder, and records synchronization jobs in the distribution database.

Distribution Agent

• It is used with snapshot and transactional replication.
• It applies the initial snapshot to the Subscriber and moves transactions held in the Distribution db to Subscribers.
• It runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

Log Reader Agent

• It is used with transactional replication, which moves transactions marked for replication from the transaction log on the publisher to the distribution db.
• Each db has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

Merge Agent

• It is used with merge replication.
• It applies the initial snapshot to the Subscriber and moves incremental data changes that occur.
• Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.
• It captures changes using triggers.

Queue Reader Agent

• It is used with transactional replication with the queued updating option.
• It runs at the Distributor and moves changes made at the Subscriber back to the Publisher.
• Unlike Distribution Agent and Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution db.

REPLICATION TYPES

• Snapshot Replication
• Transactional Replication
• Merge Replication

1. Snapshot Replication

• The snapshot process is commonly used to provide the initial set of data and database objects for transactional and merge publications.
• It copies and distributes data and database objects exactly as they appear at the current moment of time.
• Snapshot replication is used to provide the initial data set for transactional and merge replication.
• It can also be used when complete refreshes of data are appropriate (BOL).
• Scenarios
• When the data is not changing frequently.
• If we want to replicate small amount of data.
• To replicate Look-up tables which are not changing frequently.
• It is acceptable to have copies of data that are out of date with respect to the publisher for a period of time

For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended.

Snapshot Replication Architecture

Source: BOL

How it Works?

• Snapshot Agent establish a connection from distributor to publisher and generates fresh snapshot into snapshot folder by placing locks.
• Snapshot agent writes copy of the table schema for each article to .sch file.
• Copies data from published table at the Publisher and writes data to the snapshot folder in the form of.bcp file.
• Appends rows to the Msrepl_commands and Msrepl_transactions.
• Releases any locks on published tables.

Configuring Replication

1. Configuring distributor
2. Configuring publisher
3. Creating publication of required type
4. Creating subscription(s)

Step 1: Configuring distributor and publisher

1. Take three instances
2. Go to second instance -> Right click on Replication -> Configure Distribution…
3. Next -> Select ‘SERVER2’ will act as its own distributor;
4. Next
5. Next
6. Next
7. Uncheck the check box present at Server2 -> Add
8. Select instance Server1
9. Next
10. Enter strong password. (Automatically one login is created in distributor with the name Distributor_Admin)
11. Next
12. Next
13. Finish

Observations

1. Go to distributor -> Databases -> Find the new database “Distribution”
2. Go to Security -> Logins -> Find a new login “Distributor_admin”
3. Go to Server Objects -> Linked servers -> Find new linked server “repl_distributor”
4. Right Click on Replication -> Select distributor Properties
   
   

• Transactions stored in distribution database are removed after 72 hrs and agents history is removed after 48 hrs.
• To view snapshot folder path -> Click on publishers -> click on browse button (…) present to right side of publisher name.
• Go to SQL Server Agent -> Jobs -> Find 6 new jobs are created automatically.

Step 2: Creating Snapshot Publication

1. Go to publisher (Server1) -> Replication -> Right Click on Local Publications -> New publication.
2. Next
3. Select second option -> Click on Add -> Select Distributor instance (Server2)
4. Connect ? Next
5. Enter password of Distributor_admin login which we have mentioned while configuring publisher.
6. Next
7. Select required database. For example SSISDb
8. Next
9. Select “Snapshot Publication” -> Next
10. Select required tables -> Next
11. Next -> Next
12. Select the check box to create snapshot as follows
13. Next
14. Click on security settings
15. Select as follows
16. OK
17. Next
18. Next -> Next
19. Enter publication name as follows
20. Finish

Observations

1. Go to publisher -> Replication -> Local publications -> Find new publication is created
2. To check snapshot was created or not -> Right click on the publication (SSISDBSP) -> View Snapshot Agent Status
3. Go to repldata folder as follows:
4. Go to sub folders find the snapshot files (.bcp, .sch, idx, .trg)
5. Go to distributor -> SQL Server Agent -> Jobs -> Find snapshot agent job was created

FAQ: How to display database names which consists of publications?

Ans: Go to publisher -> take new query ->

Hide   Copy Code

select name from sys.databases where is_published=1 or is_subscribed=1

Creating Subscription

1. Go to publisher -> Replication -> Local Publications -> Right Click on SSISDBSP -> New Subscription
2. Next
3. Select the publication name: SSISDBSP
4. Next
5. Select Push subscriptions
6. Next
7. Add Subscriber -> Select third instance (Server1\test) -> Connect
8. Next
9. Under Subscription Database if there is no database exists with same name -> Select New database -> Enter Database Name -> OK -> Next
10. Click on browse button (…) under distribution agent security page.
11. Select “Run under Agent Service Account” and “By impersonating the process account” options as both distributor and subscriber’s service accounts are same. If the service account of subscriber is different, then create a login in subscriber with sysadmin privileges then mention that login details.
12. Next
13. Under Agent Schedule -> Select “Run Continuously”
14. Under Initialize when select -> Immediately
15. Next -> Next -> Finish

Observations

1. Go to subscriber -> SSISDB -> Tables -> Find two tables are created
2. Go to distributor -> SQL Server Agent -> Find new job is created, related to Distribution Agent
   

Verifying Replication

1. Go to publisher perform some changes in any table present in publication
2. Go to distributor run Snapshot Agent Job
3. Go to subscriber observe the changes in the respective table

Setup of Report Server Rights

Report Server Rights:

For setup of report server rights you need to find URL, for which you need to go at below location:

“CEU>system Administration>Setup> Business Intelligence>Report services>Report Server>”

When you click on this option, one form will open. In this form at the right side in the content pane you will see a section named “Reporting Server Information”. In this section you need to copy “ReportManager URL from Reporting Server URL’s group”.

Then you need to open Internet Explorer and paste the copied URL in the address bar. After that you will see a window in which you need to enter BCPROXY/Administrator password after that you need to do setup in given location:

1. Click on site setting>Security>NewRoleAsign (With the help of this option we can add users to whom we need to give permission for report viewing).

2. Then you need to click on :

Home button (Available on the Top- right Corner)>Folder Setting>Security >RoleAsign>AddUsers (With the help of this option we can add users to whom we need to give permission for report viewing).

3. Then, Click again on home button>DynamicsAX>Folder setting>RoleAsign>AddUsers.

Task Done.