Linked Servers

                                  

Introduction

Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server.

Advantages

·         The ability to access data from outside of SQL Server.

·         The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

·         The ability to address diverse data sources similarly.

Configuring Linked Server

You can configure a linked server by using SQL Server Management Studio or by using the sp_addlinkedserver (Transact-SQL) statement. 

Linked Server Components

·         An OLE DB provider

·         An OLE DB data source

An OLE DB provider is a DLL that manages and interacts with a specific data source. An OLE DB data source identifies the specific database that can be accessed through OLE DB. 

The Microsoft SQL Server Native Client OLE DB Provider (PROGID: SQLNCLI11) is the official OLE DB provider for SQL Server.

Linked Server Details

How to Add a Linked Server:

To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.

1.   In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)

Add New Linked Server

2.The “New Linked Server” Dialog appears.  (see below).

Linked Server Settings

1.   For “Server Type” make sure “Other Data Source” is selected.  (The SQL Server option will force you to specify the literal SQL Server Name)

2.   Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.

3.   Provider – Select “Microsoft OLE DB Provider for SQL Server”

4.   Product Name – type: SQLSERVER (with no spaces)

5.   Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME

6.   ProviderString – Blank

7.   Catalog – Optional (If entered use the default database you will be using)

8.   Prior to exiting, continue to the next section (defining security)

Define the Linked Server Security

1. Within the same Dialog on the left menu under “Select a Page”, select Security

2. Enter the security option of your choice.

3.      Click OK, and the new linked server is created

Database Mail

 Introduction

The database mail can be used to notify users or administrators regarding events raised in SQL Server. For example, if an automation process like replication, database mirroring fails or there are latency related problems then SQL Server can use this feature to notify the administrators or operators. We can send a text message and HTML, query result, file as attachment

Points to Remember

·          Like SQL Mail, database mail doesn’t require a MAPI – a compliant mail server like Outlook         Express or extended programming interface.

·         Better performance. Impact of sending mails to SMTP servers by SQL Server is reduced as this     task is implemented by an external process initiated by the DatabaseMail.exe file.

·         Works fine in a cluster based environment.

·         64-bit support.

·         Database mail configuration information is maintained in an MSDB database.

·         Only members of SysAdmin and DatabaseMailUserRole database role of MSDB can send          mails by default.

·         Allows sending messages in different formats like text and HTML.

  Supports logging and auditing features through different system tables of MSDB

The main components of database mail are: 

·         Sp_send_dbmail

This is a system defined stored procedure which is used by SQL Server to send email using the database mail feature. This stored procedure is present in the MSDB database.

·         MSDB Database 

Consists of all stored procedures, system tables, and database roles related to database mail.

·         Service Broker

To establish communication between the SQL Server engine and the database mail engine we need a service broker. It submits the messages to the mail engine.

·         DatabaseMail.exe

This file is present in the Binn folder of the respective instance. It is the

database mail engine.

                                 Figure – 1 (Source: BOL) Database Mail Architecture

How it works? 

When a run time error occurs due to any automated task like backups, replication etc database engine raise the error and same information is submitted to Database Mail engine, then database mail engine will submit the mail to SMTP Server using EmailID and Password mentioned in profile. At the last SMTP Server sends mail to recipients.

Error  --> DB Engine  --> DB Mail Engine --> SMTP Server --> Recipients

How to enable a Service Broker in MSDB?

USE [master]

GO

ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT

GO

Configuring Database Mail

1.       Go to Object Explorer

2.       Management

3.       Right click on Database Mail and select “Configure Database Mail” as follows

4.      Next

5.      Select “Setup Database Mail by performing the following tasks” as follows

6.       Next

7.       Enter profile name = “SQL Profile” and description as follows

8.      Click on “Add” button and enter the following details. Always use your own email ID. Generally we have to use here the company email id. We have to raise a ticket to the mail server admin team to get the following details.

o    Separate email id for SQL Server (This is From Email ID)

o    SMTP server name

o    Port number

o    SSL feature should be enable or disable.

Here I am using my personal email id. In Basic Authentication option enter the same email ID along with the valid password of the email ID.

9.       OK

10.       Next

11.       Under Manage Profile Security option make the profile as public by selecting checkbox and default as follows

12.       Next

13.       Accept the default settings for System Parameters as follows

14.Next

15.Finish

. 16.     Close.

17.       We have configured database mail feature successfully. Let's test the mail feature as follows.

18.       Go to Object Explorer -> Management -> right click on Database Mail -> Send Test Email

   19.    Enter the following details. You can use required values.

o           Select Profile name:  SQLProfile

o          To:  ask.sqlserver@gmail.com  (Any Email ID- generally it should be administrators group email     ID) 

o         Subject: Hi, Backup Alert 

o         Body: Backup of master database was generated successfully.

.20.     Send Test Email -> OK 

21.       Verifying whether the mail was send successfully or not. Take new query and run the following command and check sent_status column value for your mail as follows

.     22.  Check your mail box you can find new Email from SQL Server.

SQL Server Profiler Step by Step

Introduction

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring T-SQL Statements of Database Engine. We can save and reuse the state at a later point of time.

• We can do the following using SQL Server Profiler
  • Create a trace
  • Watch the trace results as the trace runs
  • Store the trace results in a table
  • Start, stop, pause, and modify the trace results as necessary
  • Replay the trace results

• Use SQL Server Profiler to monitor only the events in which you are interested.

Menu Path: Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.

The following screen will come:

Figure 1.0

Click on <Connect> Button. New Properties Screen will come:

Figure 1.1

It has two selection tabs:

• General: It is used for general setting for Trace Database Engine.
• Event: It is used to add or remove some selected event for monitor.

In General Section (as given in Figure 1.1), it is divided into four sections.

Section 1: In this section, you have to just specify the name of your trace, Trace provider name and server name are predefined and based upon your SQL Server.

And it is not editable.

Section 2: It is the template section. You can choose different type of Templates based upon your requirements. It is the configuration for trace. By default, it is "Standard (Default)" templates. Others templates are T-SQL, T-SQL Duration, T-SQL Reply, T-SQL SPs, etc. You can create your own custom Templates by selecting different Events and Event Class. It is saved as ".tdf" Extension.

Section 3: This section is related to save your trace. Either as File (.trc) or in a database. as table. While clicking on Save to file check box, File save dialog box should open and you can save that file (with .trc extension).

If you check the "Save to Table", it will connect with your server and ask you to which database you want to save that trace table information.

Figure 1.2

Section 4: You can stop your trace on a particular time. Check the "Enable trace stop time" checkbox and give the time at which you want to stop track, SQL Server will automatically stop trace on that time.

Now Move To "Event Section" Tab.

Now we need to know some definition with respect to SQL Server Profiler.

What is an Event?

An Event is an action or operation that is performed in your SQL Server 2005 Database Engine.

Some examples of Events are:

• Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
• User login and logout
• Execution of Stored procedures
• Operation with cursor

SQL Server profiler is able to trace all of these events and all these events are categories on a particular Eventclass.

What is an Event Class?

Event class is a type of event that can be traced.

Some examples are:

• SQL: BatchCompleted
• SQL: Batch Starting
• Audit Login
• Audit Logout
• Lock: Acquired
• Lock: Released

Now you can select events from this screen:

Figure 1.3

In section 1, we can select the proper check box based upon our requirement, section 2 will display the details of Selected events and Events class. If you check in the check box of section 3, you will get all the list of Events and Columns in Section 1.

Section 4 is something like customization. Just click on the "Column Filter Button". In this section, you can specify some condition (like or Not like).

Figure 1.4

By clicking on "Organize Column" button, you can change the sequence of order of selected events.

Now Click on the "Run" Button, then Trace window will come:

Figure 1.5

Using these windows, you will get the detailed time duration of a query and all other events information that you have selected.

You can save this result and use it in future. Or you can extract a particular query from the trace, just right click and click on "Extract Event Data". And save this as a SQL Script.

Reply in SQL Server Profiler

SQL Server profiler has a Reply facility which has the ability to save a trace and replay it later.

Replay is useful to troubleshoot an application. Trace replay supports debugging by using Toggle Breakpoint and the Run to Cursor options on the SQL Server Profiler Replay menu.

Anything changed in SQL Server Management Studio will be traced by the SQL Profiler. So it can basically be used for database performance check. We also have "SQL Server Performance Monitor" to monitor the System and Server performance too.