Creating SQL instances for messaging related services

We will come across  certain scenarios where we need to integrate our Exchange Servers with other products for additional functionality.

Few examples are installing an automated mail signature software on the Transport Server for customizing and applying the email signatures automatically to all users.

Installing an additional layer of Anti-spam product on the email servers.

Configuring a Meeting room solution for integrating our  room mailboxes,Equipment mailboxes with additional product and integrating with meeting schedule display monitors.

Bringing up an archive solution for all the primary mailboxes and the list goes on.

Most of the products which are coming on the above category will require a centralized database where it needs to store its information related to the  configuration,logs, reports,etc.

In most of the cases these products will be using SQL DB’s for storing these data and values.

So planning in these kind of scenarios is very very important.Since an improper configuration of SQL instance and installation of the related product can interrupt the Exchange functionality very easily.

When we come across these kind of scenarios its always better to interact with the associated product expert .Get recommendations from a SQL expert with regards to the SQL configuration for the product.

I have collected few things which will help an Messaging guy during these scenarios

1) First get in touch with a SQL person and explain him about the requirement of the SQL for the product.Basically what kind of job it writes and stores in the SQL.

2) Reach the associated product expert and get advise on configuring the SQL instance for the product.

If  your environment is or the product that you are going to install is not that complex and does not require SQL enterprise then you can go ahead and install the local SQL instance on the corresponding Exchange Server.

I have collected few points based on my experience which might help during configuring SQL instance if you are going to configure the instance :

Make sure during the installation you select the SQL authentication mode and create a service account for the same. Though the builtin sa account will have the permission but its better to create a dedicated service account for the admin.

SQ

Advantages of selecting SQL server authentication mode

SQL Authentication is the typical authentication used for various database systems, composed of a username and a password. Obviously, an instance of SQL Server can have multiple such user accounts (using SQL authentication) with different usernames and passwords. In shared servers where different users should have access to different databases, SQL authentication should be used for better security since the users only with Windows authentication cannot connect to the database.

Also make sure that you set the file db and the log file value to some greater figure as below example

SQ1

 

 

Make sure the auto-close option is set to false

Functionality of Auto Close :-

Having this option set to true will annoy us from unwanted SCOM alerts.
When we set this option to True the DB will go offline when it doesn’t receive any active connections and will resume back whenever it gets new connections. So when we have a SCOM agent monitoring this SQL we will get unwanted alerts from them.

Its better to set this parameter to false

Launch Management Studio –> Select the Database  –> Right Click Properties–> Options –> “Auto Close” value to “False”

Autoclsoe

 

After the installation is done check the total and target server memory to see if its eating up any additional memory by executing the below query

Select * from sys.dm_os_performance_counters

where counter_name in

(‘Target Server Memory (KB)’,

‘Total Server Memory (KB)’)

or (object_name=’SQLServer:Buffer Manager’ and counter_name in(‘Buffer cache hit ratio’,

‘Page life expectancy ‘))

 

Also you can check the physical memory ,available committed memory , memory utilization percentage by running the below query

select * from sys.dm_os_process_memory

SQLM

If we are not sure about these values its better after the configuration you can show your SQL installation to an SQL expert and check if all the settings are in right place.

Thanks 

Sathish Veerapandian

MVP – Exchange 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: