how to create linked server in sql server 2008 step by step

If you've been navigating around SQL Server Management Studio (SSMS), you may have come across the Linked Servers option.
The Linked Servers option allows you to connect to another instance of SQL Server running on a different machine, perhaps remotely in a different city/country. This can be useful if you need to perform distributed queries (query a remote database). Setting up a linked server is quite straight forward in SSMS, all you need is details of the remote server, and the database that you need to query.

Creating a Linked Server

To create a linked server:

1. Navigate to Server Objects > Linked Servers
2. Right click on Linked Servers and select New Linked Server.... Like this:
   
3. Complete the details for the linked server. In this example, Data source refers to the name of the SQL Server machine ("Barts_database_server"), Cataloguerefers to the name of the database ("Barts_database"). You can also configure options in the other two tabs depending on your requirements.
   

Distributed Queries

Once you have configured your linked server, you will be able to run queries etc against it. When you run a query against a linked server, it is referred to as adistributed query.

When you execute a distributed query against a linked server, you must include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name.

Here's an example:

This example is based on the linked server example above. It assumes that the remote database has a schema called "Person" and a table called "Enemy"

Barts_database_server.Barts_database.Person.Enemy

how to create user in sql server 2008 step by step

SQL Server allows for the creation of user logins. Each individual who needs access to SQL Server can be given their own user account.

When the administrator configures these user logins, he/she can assign them to any number of roles and schemas, depending on the access that the individual is entitled to.

In this lesson, we will walk through the steps in creating a user login.

To Create a New User Login

1. Using SQL Server Management Studio, expand the "Security" option and right click on "Logins"
2. Click on "New Login"
   
3. Complete the login properties in the "General" tab by providing a name for the login, choosing the Authentication method (providing a password if you choose "SQL Server authentication"), and selecting the database to use as a default. If you don't choose a language, it will use the default for the current installation of SQL Server.
   If you get an error that reads "The MUST_CHANGE option is not supported by this version of Microsoft Windows", simply uncheck the "User must change password at next login" option. The error occurs because your operating system doesn't support this option.
   
   
4. Click the "Server Roles" tab if you need to apply any server-wide security privileges.
   
5. Click the "User Mapping" tab to specify which databases this user account is allowed to access. By default, the login will be assigned to the "Public" role, which provides the login with basic access. If the login needs more access in one or more databases, it can be assigned to another role with greater privileges.Note that these roles are "Database Roles" and are different to the server roles in the previous tab. Server roles are for administering the SQL Server. Database roles are created within each database and specify what the login can do within that database.
   

how to create roles in sql server 2008 step by step

When creating a new user login in SQL Server, you get the option of assigning the login one or more server roles.

Server roles (not to be confused with database roles) are available for various database administration tasks. Not everyone should be assigned to a server role. In fact, only advanced users such as database administrators should be assigned a server role.

Accessing the Server Roles

To access the server roles in SQL Server Management Studio, expand the Security folder:

You view the properties of a server role by right clicking on it. You can then add users to the server role by clicking Add. In the screenshot below, Homer has been added to the securityadmin role.

Explanation of Server Roles

Here's an explanation of the server roles defined in SQL Server 2008 during setup:

Server Role	Description
sysadmin	Can perform any task in SQL Server.
serveradmin	Can set server-wide configuration options, can shut down the server.
setupadmin	Can manage linked servers and startup procedures.
securityadmin	Can manage logins and database permissions, read logs, change passwords.
processadmin	Can manage processes running in SQL Server.
dbcreator	Can create, alter, and drop databases.
diskadmin	Can manage disk files.
bulkadmin	Can execute BULK INSERT statements.
public	Every SQL Server user account belongs to this server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on an object when you want the object to be available to all users.

As you can see, some of these roles allow very specific tasks to be performed. If you don't have many technical users, it's likely that you'll only use one or two of these roles (including sysadmin).

how to create schema in sql server 2008 step by step

What is a Database Schema?

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.

You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.

Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Creating a Database Schema

To create a database schema in SQL Server 2008:

1. Navigate to Security > Schemas
2. Right click on Schemas and select New Schema.... Like this:
   
3. Complete the details in the General tab for the new schema. In this example, the schema name is "person" and the schema owner is "Homer".
   
4. Add users to the schema as required and set their permissions:
   
5. Add any extended properties (via the Extended Properties tab)
6. Click OK.

Add a Table to the New Schema

Now that we have a new schema, we can add objects such as tables, views, and stored procedures to it. For example, we could transfer the table that we created in the earlier lesson to the new schema.

When we created that table (called "Individual"), it was created in the default database schema ("dbo"). We know this because it appears in our object browser as "dbo.Individual".

To transfer the "Individual" table to the person "schema":

1. In Object Explorer, right click on the table name and select "Design":
   
2. From Design view, press F4 to display the Properties window.
3. From the Properties window, change the schema to the desired schema:
   
4. Close Design View by right clicking the tab and selecting "Close":
   
5. Click "OK" when prompted to save

Your table has now been transferred to the "person" schema.

Confirm your Change

To confirm the change:

1. Refresh the Object Browser view:
   
2. You will now see that Object Browser displays the new schema for the table (person.Individual):
   

how to schedule a job in sql server 2008 step by step

SQL Server Agent is at the heart of automating maintenance tasks. There are several things available in SQL Server Agent that can assist you with routine automation and preventative maintenance. You can create operators that will receive notification for certain events. You can define alerts that will capture certain events, and SQL Server Agent can then perform predefined actions in response to those events. You can create jobs that SQL Server Agent can run on a predefined schedule to perform routine preventative maintenance tasks. You can even create a master server in SQL Server Agent so you can manage jobs on multiple target servers from a single server.

1. Operators

The first thing you need to do is define an operator so you can receive automatic notifications through SQL Server Agent. An operator consists of two basic pieces of information: a name used to identify the operator and the contact information used to notify the operator. To add an operator using SQL Server Management Studio, expand SQL Server Agent in the Object Explorer, right-click the Operators folder, and select New Operator from the context menu. This will open the New Operator dialog box shown inFigure 1.

Figure 1. New Operator dialog box

Enter an operator name and email address, and click OK. Although you can configure net send and pager information, these options are deprecated and will be removed in a future version of SQL Server, so you should avoid using them. The only information you should enter here is the name of the operator and the email address that you will use to receive event notifications.

You can also add an operator using the sp_add_operator procedure located in the msdb. The following statement adds an operator named DBA Support and supplies an email address as the contact information.

EXEC msdb.dbo.sp_add_operator
     @name='DBA Support',
     @email_address='DBASupport@somecompany.com'

2. Enabling SQL Server Agent Notifications

You have to enable the alert system in SQL Server Agent before you can start receiving notifications. Once you have configured Database Mail and added an operator, the next thing you should do is enable the alert system and designate a fail-safe operator. This is a designated operator that will receive notifications in the event that the primary operator is unreachable. In SQL Server Management Studio, right-click SQL Server Agent and select properties from the context menu. This will bring up the SQL Server Agent Properties dialog box. Select the Alert System page, as shown in Figure 2.

Figure 2. SQL Server Agent Properties dialog box Alert System page

There are only a few settings on this page you need to configure. Under the Mail Session section, select Enable Mail Profile. This will allow you to select the mail system and profile that SQL Server Agent will use to send notifications. You should select Database Mail from the Mail System drop-down list. SQL Mail is the other available mail system option; however, you should avoid SQL Mail because it will be removed in a future release. Now select the profile SQL Server Agent will use to send alert notifications from the Mail Profile drop-down list. 

The next thing you need to do is select Enable Fail-Safe Operator under the Fail-Safe Operator section. Now you can select the operator from the drop-down list that you want to receive notifications in case the designated operator is unreachable. SQL Server stores the fail-safe operator information in the registry in case the operator tables in the msdb are unavailable. We are using an operator called DBA Support, as shown in Figure 13-10. You should then select the E-mail check box to specify that the fail-safe operator will receive notifications using email. Remember, you should avoid the Pager and Net Send options, since they will no longer be supported in a future release.

Select OK to close the SQL Server Agent Properties dialog box. You must restart SQL Server Agent before the new settings will take effect. You are now ready to start receiving automatic notifications from SQL Server Agent alerts and jobs.

3. Alerts

A SQL Server Agent alert is an automatic response to a predefined event. You can configure SQL Server Agent alerts to fire in response to SQL Server events, SQL Server performance counters, and Windows Management Instrumentation (WMI) events. Once an event has caused an alert to fire, you can respond to the alert by notifying operators of the event or even running a job after the event has occurred.

So, what kind of alerts should you have in place that will lead to proactive maintenance? At the very minimum, you should have a separate alert configured for fatal errors, which are indicated by severity codes 19 through 25. Let's walk through an example of creating an alert that will notify you of any fatal errors encountered with the severity of 19 called "Fatal Error in Resource." To add an alert using SQL Server Management Studio, expand SQL Server Agent in the Object Explorer, right-click the Alerts folder, and select New Alert from the context menu. This will open the New Alert dialog box shown in Figure 3.

Figure 3. New Alert dialog box General page

Give the alert a descriptive name and make sure the Enable check box is selected. We will call this alert "Fatal Error in Resource." Select SQL Server Event Alert from the Type dropdown list. The Event Alert Definition section will change depending on the type of alert you have selected. You can limit the alert to a specific database by selecting it from the Database name drop-down list; for this example, you should select <all databases>. We want to monitor errors with a severity of 19, so select the Severity option to enable the drop-down list, and then select 019 - Fatal Error in Resource. Select the Response page, as shown in Figure 4, to define the action that SQL Server Agent will perform when the event occurs.

Figure 4. New Alert dialog box Response page

The Response page allows you to perform two actions in response to an event: execute a job and notify an operator of the event. You can select an existing job to run from the Execute Job drop-down list, or select the New Job button to open the New Job dialog box and create a new job. If you choose a job from the drop-down list and select the View Job button, SQL Server will display the Job Properties dialog box that will allow you to view and edit an existing job.

All of the existing operators are displayed in the Operator List area. Check the E-mail column for each operator you would like to receive a notification email when the alert is triggered. Remember, the Pager and Net Send options are deprecated, so you should avoid these two options. You can select the New Operator button to open the New Operator dialog box, or select the View Operator button to open the Operator Properties dialog box for the selected operator. We have chosen to notify the DBA Supportoperator we created in the previous "Operators" section. Select the Options page, as shown in Figure 5, to make the final configurations to the new alert.

Figure 5. New Alert dialog box Options page

Select the E-mail check box at the top of the Options page so the error text for the event will be included in the email that the operator receives. The Additional Notification Message to Send text box will allow you to send further information or instructions along with the error text to the operator. You can use the Delay Between Responses section to suspend additional responses for the alert for a specified amount of time. Adding a delay between responses is useful for error messages that may occur in rapid succession; nobody wants to receive a hundred emails in five minutes. Click OK to close the New Alert dialog box and create the alert.

You can repeat this process for each alert you want to create on every SQL Server instance, or you can create a T-SQL script you can quickly run on all of your SQL Server instances. The code in Listing 1 will generate the same alert we just created for severity 19 using SQL Server Management Studio.

Example 1. Code to Create an Alert for Severity 19 Messages

USE msdb GO EXEC msdb.dbo.sp_add_alert @name=N'Fatal Error in Resource', @message_id=0, @severity=19, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @notification_message=N'This is a Fatal Alert. Please review immediately.' GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Fatal Error in Resource', @operator_name=N'DBA Support', @notification_method = 1 GO

You can also define an alert for a specific error number, regardless of the severity, by entering the exact error number. We have seen some specific blog entries around the need to create an alert for error 825, which deals with an IO issue. You can read more on error 825 on Paul Randal's blog located athttp://sqlskills.com/BLOGS/PAUL/post/A-little-known-signof-impending-doom-error-825.aspx.

For a complete list of messages and their severity codes, you can query the sys.messages catalog view. There are far too many messages in the sys.messages catalog view to review each one manually. You can narrow down the messages by limiting the results by language and only showing the messages that SQL Server will log. You can also limit the results to messages with a severity of less than 19, as shown in the following query, since you should already be monitoring messages with a severity of 19 and above. You can review the list returned by the following query to determine if you want to add additional alerts for specific errors.

SELECT *
FROM sys.messages
WHERE language_id = 1033 and
            is_event_logged = 1 and
            severity < 19

SQL Server Agent jobs make it possible for you to perform routine scheduled maintenance on your SQL Server instances. A job is made up of a series of steps or actions that work together to perform a task. You can place each job in a category to group tasks that are similar in nature. For example, you can use the Database Maintenance category to group all of your maintenance jobs. You can execute a job using a predefined schedule or by using the sp_start_job stored procedure in the msdb. SQL Server Agent can notify an operator when a job fails, when a job succeeds, or any time a job completes.

Right-click the Jobs folder under SQL Server Agent and select New Job from the context menu to open the New Job dialog box, as shown in Figure 6.

Figure 6. New Job dialog box General page

Give the job a descriptive name, such as Cleanup Mail History. Select Database Maintenance from the Category drop-down list. If you do not have a Database Maintenance category or you want to create a new category, you can right-click the Jobs folder and select Manage Job Categories from the context menu. Provide a brief description, such as Database maintenance job used to purge mail history older than 30 days from the msdb. If Enabled is unchecked, the job will not run during the scheduled time; however, you can still execute the job using the sp_start_job stored procedure. A disabled job will also run if it is executed in response to an alert. Select the Steps page, as shown in Figure 7, to define the steps the job will perform.

Figure 7. New Job dialog box Steps page

You can use the Steps page to add, edit, and delete job steps, move the order of the steps, and set the start step for the job. Select the New button to display the New Job Step dialog box shown in Figure 8.

Figure 8. New Job Step dialog box General page

Give the job step a descriptive name, such as Delete Mail History. You can create job steps to execute many different actions, such as the following:

• Operating system commands
• Transact-SQL scripts
• PowerShell scripts
• ActiveX scripts
• Replication tasks
• Analysis Services tasks
• Integration Services packages

ActiveX scripts will be removed from SQL Server Agent in a future version of SQL Server. Therefore, you should avoid using ActiveX scripts in any new jobs.

Since we are executing a SQL script, select Transact-SQL Script (T-SQL) from the Type drop-down list. The Run As drop-down list in the General page does not apply to T-SQL scripts; it only applies to other step types that interact with the OS, such as PowerShell scripts and CmdExec. The Run As option here is for designating a proxy account to run the job step. (We will discuss proxy accounts more in the next section.) Actually, if you change Type selection to something other than T-SQL, and then change it back to T-SQL, the Run As option will be disabled.

Change the Database option to msdb, since that is where the Database Mail cleanup stored procedures are located. Enter the following script in the Command text box. Select the Advanced page, as shown in Figure 9, to configure the remaining options for the job step.

DECLARE @BeforeDate DateTime =
                      (Select DATEADD(d,-30, GETDATE()))

EXEC sysmail_delete_mailitems_sp
           @sent_before = @BeforeDate

EXEC sysmail_delete_log_sp
           @logged_before = @BeforeDate

Figure 9. New Job Step dialog box Advanced page

You can use the Advanced page to define options for the job step, such as completion actions and logging. You can use the On Success Action and On Failure Action lists to quit the job reporting success, quit the job reporting failure, go to the next step, or to select a specific step that you would like to run next as a result of the action. You can use the Retry Attempts and Retry Interval options to set the number of times SQL Server Agent will try to run the steps and how long it will wait between retry attempts before it considers the step a failure. We have set our job step to retry one time 15 minutes after the initial failure. It is important to specify an output file to log the messages returned by the job step. Output files are a tremendous help when troubleshooting issues related to the job failures. The Run As User option in the Advanced page is only applicable for T-SQL scripts. If you create a step that is anything other than T-SQL, the option will not be displayed. The account specified here is the SQL Server account used by SQL Server Agent to execute the job step. Only users with sysadmin rights can set the Run As User option and create output files. Click OK to add the new step to the job.

You can use the Schedules page of the New Job dialog to pick an existing schedule or create a new schedule for the job. Click the Schedules page and select New to open the New Job Schedule dialog box shown in Figure 10.

Figure 10. New Job Schedule dialog box

Give the new schedule a descriptive name, such as Midnight Every Sunday. The options are pretty self-explanatory. Select the appropriate schedule for the new job. You will see a Summary area at the bottom that you can use to validate the choices you have made. As indicated by the name, we have created a schedule to run every Sunday at 12:00 AM. Select OK to add the job to the new schedule.

You can use the Alerts page to create a new alert that will execute the job in response to an event. Since we do not need to clean up Database Mail for an alert, skip this page and select Notifications, as shown in Figure 11.

Figure 11. New Job dialog box Notifications page

You can use the Notifications page to specify the actions SQL Server Agent will perform when the job completes. All of the drop-down lists on this page have the same three options: When the Job Fails, When the Job Succeeds, and When the Job Completes. You can use these options to notify an operator, write to the Windows Application event log, and automatically delete the job. In our case, we want to email the DBA Support operator when the job fails. Click OK to create the new Job.

You only need to worry about the Targets page if you are using master and target servers within SQL Server Agent. SQL Server Agent allows you to designate a SQL Server instance as a master (MSX) server and push jobs to target (TSX) servers. The Targets page allows you to define whether the job will be defined locally or if you will be targeting multiple servers. The jobs on the target servers are read-only and cannot be deleted. In order to set up a MSX server and enlist TSX servers, you can right click on SQL Server Agent, select Multi-Server Administration, and then select Make This a Master. This will start the Master Sever Wizard that will guide you through the process. For more information on master and target servers, search for "Creating a Multiserver Environment" in SQL Server Books Online.

5. Proxies

You can create a proxy account that allows SQL Server Agent to execute a job step that runs under the credentials of a specified Windows user account. You can use a proxy account to access subsystems external to SQL Server using Windows credentials; therefore, you cannot use a proxy account for a T-SQL job step, since T-SQL is an internal operation. In order to create a proxy account, you must first create a credential that maps to a Windows user account. To open the New Credential dialog box shown in Figure 12, expand the Security node in SQL Server Management Studio, right-click the Credentials folder, and select New Credential from the context menu.

Figure 12. New Credential dialog box

Enter a descriptive name for the credential. We named ours Ken_Credential, since that is the Windows account we are using. Enter a valid Windows user account in the Identity field. You can select the ellipsis next to the Identity field to display the Select User or Group dialog box, which will allow you to search for and verify Windows user accounts. Enter the password for the Windows user account and select OK to create the new credential. You may also choose to verify the Windows user account by an Extensible Key Management provider if you have one installed on the server.

Now that you have created a credential, you are ready to create a new proxy. Expand SQL Server Agent, right-click the Proxies folder, and select New Proxy from the context menu. This will display the New Proxy Account dialog box shown in Figure 13.

Figure 13. New Proxy Account dialog box General page

Give the new proxy account a descriptive name; we called ours Ken_Proxy. Now, enter the name of the credential created earlier in this section. You can also select the ellipsis next to the Credential Name text box to search for the credential. Enter a description for the new proxy account, such asProxy used to execute Operating System and PowerShell scripts. Select the subsystems the new proxy account will use. We have selected Operating System (CmdExec) and PowerShell. Select the Principals page, as shown in Figure 14, to manage the principals that can use the new proxy account in job steps.

Figure 14. New Proxy Account dialog box Principals page

Select the Add button to assign SQL Server logins or roles to the proxy account. Members of the sysadmin role have access to all proxy accounts, so you do not need to add them on the Principals page. Click OK to create the new proxy account.

The proxy account is now an available option in the Run As drop-down list on the General page of the New Job Step dialog box. The proxy account is only available in the Run As list if you select Operating System (CmdExec) or PowerShell as the step type, since they are the only two subsystems we allowed the proxy account to use.

database mirroring in sql server 2008 step by step

What is Database Mirroring?

Database mirroring is the feature in SQL Server 2005 and SQL Server 2008 that provides a high availability solution for Databases. This feature can be enabled and used only on a database with Full recovery models. The database can be mirrored from one SQL Server instance to another SQL Server instance. The source instance is called Principal server; the target instance is called Mirrored server. We could have one more server called Witness server--we will talk about that in later part of this article series.

How does database mirroring work?

The principle server sends the active transaction log record to the mirrored server. The mirrored server applies the transaction log record one by one in sequence.

Modes of Database Mirroring

Database mirroring can be configured in two different modes, High-Safety mode also known as synchronous mode and High-Performance mode also known as asynchronously. The term synchronous and asynchronous says it all.

In the synchronous mode, the principal server sends the transaction and waits until the transaction is committed on the mirrored server. Then the transaction is committed on the principal server.

In Asynchronous mode, the principal server sends the transaction to the mirrored server and does not wait for the transaction on the mirrored server to commit.

We will discuss transaction safety in detail in a future installment of this series.

Now let's setup database mirroring between the SQL Server instance PowerPC\SQL2008 [our principal server] and PowerPC\SQL2k8 [our mirrored server].

What are the Pre-Requisites of database mirroring?

The following are the pre-requisites for database mirroring.

• Edition of SQL Server should be Standard, Enterprise or Developer edition
• Principal Database involved in database mirroring should be in full recovery mode
• Before configuring database mirroring, take a full backup, transactional log backup on the principal server and restored it on the mirrored server with NORECOVERY option.

Now let's create a database DB1 on the principal server, PowerPC\SQL2008, using the following transact SQL statement. In this part of article series, we are going to discuss database mirroring with synchronous mode and with no witness server.

USE [master]
GO

/****** Object:  Database [DB1]    Script Date: 06/20/2009 21:10:33 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')
DROP DATABASE [DB1]
GO


USE [master]
GO

/****** Object:  Database [DB1]    Script Date: 06/20/2009 21:10:13 ******/
CREATE DATABASE [DB1] ON  PRIMARY 
( NAME = N'DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DB1.mdf' , \
 SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DB1_log.LDF' , 
 SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Now let's create a database DB1 on the mirrored server, PowerPC\SQL2K8, using the following transact SQL statement.

USE [master]
GO

/****** Object:  Database [DB1]    Script Date: 06/20/2009 21:10:33 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')
DROP DATABASE [DB1]
GO


USE [master]
GO

/****** Object:  Database [DB1]    Script Date: 06/20/2009 21:10:13 ******/
CREATE DATABASE [DB1] ON  PRIMARY 
( NAME = N'DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf' , 
 SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.LDF' , 
 SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

If the target server does not have the database with same name, you will get the following error when configuring database mirroring. [Refer Fig 1.0]

Fig 1.0

Note: Instead of creating the DB1 database on the mirrored server, you could restore the database backup and tranlog backup using the with replace option to create and restore at the same time.

Now let's backup the database and transaction on the principal server using the following transact SQL statement.

use master
go
Backup database DB1 to disk ='C:\Backups\DB1.Bak' with init
go
Backup log DB1 to disk ='C:\Backups\DB1.trn' with init
go

Restore the database on the target server using the following transact SQL statement.

use master
go
restore database DB1 from disk ='C:\Backups\DB1.Bak' with norecovery, 
replace,
move 'DB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf',
move 'DB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.ldf'
go
restore log DB1 from disk ='C:\Backups\DB1.trn' with norecovery, replace,
move 'DB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf',
move 'DB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.ldf'
go

On the target server, if the database is not in restore mode you will get the following error. [Refer Fig 1.2]

Fig 1.2

Configure the database DB1 on the principal server for database mirroring. Using SQL Server management studio, expand the databases and click on the Database DB1. Right click on the database DB1 and select properties. In the properties window select the "Mirroring" option as shown below. [Refer Fig 1.3]

Fig 1.3

Now click on the "Configure Security" button and you will see the following screen. Since we are not going to setup the witness server, select the option "No" and click next. [Refer Fig 1.4]

Fig 1.4

Select the default port and the endpoint name chosen by the SQL server management studio and click Next. [Refer Fig 1.5] If you are choosing some other port, then make sure that port is open and available.

Fig 1.5

Now select the mirrored server name, click on the "Connect" button and make sure you can connect to the mirrored server. Select the default port and the endpoint name chosen by the SQL server management studio and click Next. [Refer Fig 1.6] If you are choosing some other port, then make sure that port is open and available.

Fig 1.6

Type the appropriate service account you want to use for the database mirroring. [Refer Fig 1.7]

Fig 1.7

Double check the summary details and click finish. This will configure database mirroring. [Refer Fig 1.8, 1.9, 1.10]

Fig 1.8

\
Fig 1.9

\
Fig 1.10

On the next screen, click on the button "Start Mirroring". [Refer Fig 1.11]

Fig 1.11

On the next screen, click on the "Yes" button. [Refer Fig 1.12]

\
Fig 1.12

The following screen shows that database mirroring is configured and running. [Refer Figure 1.13]

Fig 1.13

Click OK and refresh the databases. You can see the caption of the DB1 database has changed in both principal and mirrored server. [Refer Fig 1.14]

\
Fig 1.14

Conclusion:

Part I of this series explained the basics of the Database mirroring feature in SQL Server 2008. It also illustrated a step-by-step process on how to create database mirroring. Part 2 of this series we will discuss how to add data on the principal server and how data is mirrored to the mirrored server.

SQL Server 2008 - Merge Replication Step by Step Procedure

Introduction

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. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Replication is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users so that they can make changes to their local copy of the database and later synchronize the changes to the source database.

Terminologies before getting started

Microsoft SQL Server 2000 supports the following types of replication:

• Publisher is a server that makes the data available for subscription to other servers. In addition to that, publisher also identifies what data has changed at the subscriber during the synchronizing process. The publisher contains publication(s).
• Subscriber is a server that receives and maintains the published data. Modifications to the data at the subscriber can be propagated back to the publisher.
• Distributor is the server that manages the flow of data through the replication system. Two types of distributors are present, one is the remote distributor and the other the local distributor. The remote distributor is separate from the publisher and is configured as the distributor for replication. The local distributor is aserver that is configured as the publisher and distributor.
• Agents are the processes that are responsible for copying and distributing data between the publisher and subscriber. There are different types of agents supporting different types of replication.
• Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database.
• An article can be any database object, like Tables (Column filtered or Row filtered), Views, Indexed Views, Stored Procedures, and User Defined Functions.
• Publication is a collection of articles.
• Subscription is a request for a copy of data or database objects to be replicated.

Replication types

Microsoft SQL Server 2005 supports the following types of replication:

• Snapshot replication
• Transactional replication
• Merge replication

Snapshot replication

• Snapshot replication is also known as static replication. Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time.
• Subscribers are updated with the complete modified data and not by individual transactions, and are not continuous in nature.
• This type is mostly used when the amount of data to be replicated is small and data/DB objects are static or does not change frequently.

Transactional replication

• Transactional replication is also known as dynamic replication. In transactional replication, modifications to the publication at the publisher are propagated to the subscriber incrementally.
• Publisher and the subscriber are always in synchronization and should always be connected.
• This type is mostly used when subscribers always need the latest data for processing.

Merge replication

It allows making autonomous changes to replicated data on the Publisher and on the Subscriber. With mergereplication, SQL Server captures all incremental data changes in the source and in the target databases, and reconciles conflicts according to rules you configure or using a custom resolver you create. Merge replication is best used when you want to support autonomous changes on the replicated data on the Publisher and on the Subscriber.

Replication agents involved in merge replication are snapshot agent and merge agent.

Implement merge replication if changes are made constantly at the publisher and subscribing servers, and must bemerged in the end.

By default, the publisher wins all conflicts that it has with subscribers because it has the highest priority. The conflict resolver can be customized.

Before starting the replication process

Assume that we have two servers:

• EGYPT-AEID: is the publisher server (contains HRatPublisher)
• SPS: is the subscriber server (contains HRatSubscriber) use SQL Server Authentication mode for login

On the publisher database, I created a table Employees with the fields ID, Name, Salary, to replicate its data to the subscriber server. I will use the publisher as the subscriber also.

Note: Check that SQL Server Agent is running on the publisher and the subscriber.

Steps

1. Open SQL Server Management Studio and login with SQL Server Authentication to configure Publishing, Subscribers, and Distribution.


1. Configure the appropriate server as the publisher or distributor.


2. Enable the appropriate database for merge replication.

2. Create a new local publication from DB-Server --> Replication --> Local Publications --> Right click --> New Pub.

Then choose the database that contains the data or objects you want to replicate.

Choose the replication type and then specify the SQL Server versions that will be used by subscribers to that publication, like SQL Server 2005, SQL Mobile Edition, SQL for WinCE, etc.

After that, manage the replication articles, data, and database objects by choosing the objects to be replicated.
Note: you can manage the replication properties for the selected objects.

Then add filters to the published tables to optimize performance and then configure the snapshot agent.


and configure the security for the snapshot agent.

Finally, rename the publication and click Finish.


3. Create a new subscription for the created "MyPublication01" publication by right clicking on MyPublication01 --> New Subscription.
Configure the "Merge Agent" for the replication on the subscriber database.


Choose one or more subscriber databases. You can add new SQL Server subscribers.

Then specify the Merge Agent security as mentioned above on "Agent Snapshot". And specify the synchronization schedule for each agent.

Schedules:

• Run continuously: add schedule times to be auto run continuously
• Run on demand only: manually run the synchronization

and then next up to the final step, and click Finish.

You can check errors from the "Replication Monitor" by right clicking on Local Replication --> Launch ReplicationMonitor.

Advantages of Replication

Users can avail the following advantages by using a replication process:

• Users working in different geographic locations can work with their local copy of data, thus allowing greater autonomy.
• Database replication can also supplement your disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, your applications can switch to the replicated copy of the data and continue operations.
• You can automatically back up a database by keeping a replica on a different computer. Unlike traditional backup methods that prevent users from getting access to a database during backup, replication allows you to continue making changes online.
• You can replicate a database on additional network servers and reassign users to balance the loads across those servers. You can also give users who need constant access to a database their own replica, thereby reducing the total network traffic.
• Database-replication logs the selected database transactions to a set of internal replication-management tables, which can then be synchronized to the source database. Database replication is different from filereplication, which essentially copies files.

Replication performance tuning tips

• By distributing partitions of data to different subscribers.
• When running SQL Server replication on a dedicated server, consider setting the minimum memory amount for SQL Server to use from the default value of 0 to a value closer to what SQL Server normally uses.
• Don’t publish more data than you need. Try to use row filter and column filter options wherever possible as explained above.
• Avoid creating triggers on tables that contain subscribed data.
• Applications that are updated frequently are not good candidates for database replication.
• For best performance, avoid replicating columns in your publications that include TEXT, NTEXT, or IMAGE data types.