How to test whether your SQL server is available or not without installing anything?

Technorati Tags: Data Connection

Scenario: Recently, I was asked what if I want to test connectivity to my SQL server from a web server or from my local desktop machine ?

Approach: Well, this seems a much obvious question that can be answered on the spur as use PING <server IP> from the command prompt.

But PING just does the basic test and tells that whether your server is available or not. What if you want to see that your databases that your SQL server is hosting are available or not ?

There is a more smatter and easy way of doing this. The idea is to use UDL (Universal Data Link) files (i.e  .udl files).

Overview of UDL files:

UDL files are an easy, efficient and fast way to test connection to a server from your work station or from web server eliminating the need to install any application (e.g dtcping) or SSMS (SQL Server Management Studio). Also, its a good way to quickly test that the credentials you specified will connect to a server and to build a connection string.

How do I do this ?

1. Create an empty .txt file. Name it for example MyConnection.txt .
2. Now rename the extension and save it as MyConnection.udl .
3. Below is the image of the MyConnection.udl file.

4. Now double click the MyConnection.udl file to see the below screen.

5. Click the Provider tab and select SQL Native Client and then click Next.

6. Enter the data source: localhost or IP address of the server. Here I have used Windows authentication, but choosing the second option allows you to use SQL server authentication wherein you have to provide username and password. (Note: Here there is an option to save the password as blank or Allow saving password option and choosing this option will save the password as clear text on the disk which is a security risk !). Next you can choose to connect to the specific database by clicking the drop box and selecting the database name. Click test connection button to verify that the connection is successful. Below is what you will see:         

7. Press OK when you are done.

8. Now we can examine the contents of our MyConnection.udl file by renaming it back to MyConnection.txt

9. Below are the contents of the file:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=localhost

10. You can use the above connection string in your application code directly !

Hope this helps !

Thanks,

\\K

How to rename Distribution Job in an automated fashion ?

Technorati Tags: Replication

Situation: Recently I came across a situation wherein I have to rename the distribution jobs that are created when replication is recreated. The need to recreate replication is due to the fact that we do every 3 or 4th week staging refresh in our environment.

Our environment is on Windows server 2008 (64bit) with SQL server 2005 enterprise edition SP3 with Transactional replication involved.

Approach: We have a automated process called a Master Job (I will blog this in my future posts) that does the staging refresh job. This job is named as per what it does, meaning it drops replication, does restores and recreates replication with just one click ! Isn’t that cool !

But this job has some manual steps to rename the distribution jobs that are created when the replication is recreated. So to fully automate the job, I thought of automating some how to rename the distribution jobs.

First I would try to explain how the jobs are created when replication is created or recreated.

When transactional replication is created either using GUI (wizard) or recreated using scripts, the respective agent (snapshot, distribution and log reader) jobs are created with their default naming conventions. For example:

Snapshot Agent:
[PublisherServerName]-PublisherDB-PublisherDB- [# of time replication script/wizard run on this server]

Log reader Agent:
[PublisherServerName]-PublisherDB- [# of time replication script/wizard run on this server]

Distribution Agent:
[publisherserverName]-publisherDB-SubsriberServerName [# of times subscribers created run on this server]

[Ref: http://rdbmsexperts.com/Blogs/?p=40 . Thanks to Vinay for his help !]

This shows that there is no control of creating our own job names (for better management and maintenance).

There are two ways of renaming the distribution jobs:

1. On the distribution server, double click the respective job names and then rename them. Uh! that’s a manual way of doing and it can be tricky or prone to mistakes and being a DBA, its not smart way to do things !
2. The second way is creating a custom script as below:

In the distribution database, there is a system table called dbo.MSdistribution_agents that gives the info about the distribution agent with respect to sysjobs.

Below is the script that I used to rename the distribution jobs ( we have multiple publisher – subscribers.)

declare @jobID1 uniqueidentifier 
select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db='[pub_db_name]'  and
a.subscriber_db='[subscriber_db_name]' and
a.subscriber_id=s.server_id and
s.name='subscriber_server_name'

print @jobID1  -- this will print the jobID of the distribution job 

select job_id, name from msdb.dbo.sysjobs where job_id = @jobID1   -- this will give the jobID as well as job name of  the distribution job that we will be renaming

–-rename the distribution agent job that is created to new job name

exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’

To sum up above method:

-- Get the job ID for the distribution job created
declare @jobID1 uniqueidentifier

select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db='[pub_db_name]'  and
a.subscriber_db='[subscriber_db_name]' and
a.subscriber_id=s.server_id and
s.name='subscriber_server_name'

–-rename the distribution agent job that is created to new job name

exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’

In the same way we can rename the job for snapshot and logreader agent as well with MSlogreader_agents & MSsnapshot_agents tables respectively.

Hope this helps !

Thanks,

\\K

Strange issue with database mail in clustered environment - not working tried everything

Scenario: We have a 4 node clustered environment having windows server 2008 64bit with SQL server 2005 SP3 enterprise edition.

Problem: The issue was that out of 4 servers in a clustered environment, on only one server the database mail was not working.

Approach:

Step1: Check that the Database mail is enabled using Surface Area Configuration. Then I rebuilt the Profiles and Accounts within the SQL Database Mail. I sent test messages after the rebuild and didn't see anything logged. I used the troubleshoot option on the test message send page and found some stored procedures which allow you to check to see if the queue is active and to start it. I both confirmed it was active, and sent it another start command, but still don't see email getting sent. I did see that the DB Mail start command was logged in the event logs, but nothing other than that.

Below is what was done:

--The following query will show database mail is working correctly
SELECT * FROM msdb.dbo.sysmail_sentitems order by sent_date desc

No results are seen after running the above command.


SELECT * FROM msdb.dbo.sysmail_allitems order by sent_date desc

WHEN I RUN above COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "failed" send_status

--The following query will show Database mail is started and running
SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc

WHEN I RUN THIS COMMAND I CAN SEE DB MAIL IS STARTED, BUT I DON'T SEE ANY SUCCESSFULLY SENT MESSAGES LIKE I DO WHEN I RUN IT ON ANY OF THE OTHER DB SERVERS

--If Database mail was having problems, we would see failed items in this view.
SELECT * FROM msdb.dbo.sysmail_unsentitems

WHEN I RUN THIS COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "unsent" send_status

Then the FIREWALL and SMTP relay were working fine or not were checked and they were all working fine !!

Then I GOOGLED and found "http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/fd210efa-9c01-439e-8573-4f25a4177b08", but this did not work !!

Then the decision was taken to open a ticket with MS... spent 2.5 hours on the phone with Microsoft trying to figure out what's going on with DB Mail.

At this point they took some traces, other diagnostic data ... and yes they were able to figure the REAL problem ....

When you select MSDB and run the following to show DB Mail config:

------------------------------------------------------------------

USE msdb

select * from sysmail_configuration

------------------------------------------------------------------

...We can see that the following is set (which MS says would have been done manually as the default is to NOT use a config file):

------------------------------------------------------------------
ReadFromConfigurationFile 1 Send mail from mail server in configuration file
------------------------------------------------------------------

With this option enabled there MUST be a configuration file that tells the DB Mail exe what Server\Instance to send the mail to. This file existed (I don't know if it has existed from the beginning, who created it, etc...), but it was not in the correct location.

The file was in:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

...and needed to be in (MSSQL.3 since registry shows [server_name] being the 3rd instance):

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn

Once this file was moved into the proper location, we were able to define the correct server name:

------------------------------------------------------------------
Contents of config file: "DatabaseMail90.exe.config"
------------------------------------------------------------------

configuration
appSettings
add key="DatabaseServerName" value ="[server_name]" /
add key="DatabaseName" value ="msdb" /
/appSettings
/configuration

------------------------------------------------------------------
Once this had been properly configured and the file put into the correct location we were able to successfully send mail

Since this is a 4-node SQL Cluster we also had to copy the "DatabaseMail90.exe.config" file over to the other 3 nodes to:

\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn
\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn
\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn

...so that if this instance fails over to any other node it will continue to function.


This is how the Strange database mail issue was fixed ... Ah.. it was with the config file .... and lastly got resolved !!!