Database Mirroring Setup Document.

Hi:

I am looking for a Step-by-Step document for setting up and configuring of database mirroring if one is available. I have gone through BOL and found some related documentation but want to see if a step-by-step document is available. If so can any one please direct me to the resource.

Thank you

AK

Please look at the How-to section in the BOL.

http://msdn2.microsoft.com/en-us/library/ms175059.aspx

Database Mirror and Failover

Hello,
I'm just configuring a Database Mirror with SQL 2005 SP2. I have two SQL-Servers SQL1 and SQL2 and one Whitness. Everything is working fine, when I plug out the ethernet cable from SQL1 the whitness is detecting the failure an is switching over to SQL2 and so on. The running application is connecting to SQL2 after the failure. In case of a restart of the application the client tries to connect to SQL1, which is not reachable. As I'm expecting there should be a connect to SQL2 after the sqlclient can't reach SQL1 but with netstat -na I only see connection attempts to SQL1. From a former posting I got the Information about some existing connections to SQL1, but I made a restart of the client so be sure that there is no existing connection to SQL1. When both SQL-Servers are reachable and I do a manual failover everything is working fine, so I think database mirroring only works, when both servers are online. Is this true?
My connection string looks like this:
Data Source=DBSRV1;Failover Partner=DBSRV2;Initial Catalog=TEST;User ID=test;Password=test;Network Library=DBMSSOCN;Load Balance Timeout=5"
Thanks for your help
Greetings from Germany
Christian
Christian Grbner
MVP ISA Server
Hilfe & Infos rund um den ISA Server: http://www.msisafaq.de !!!!
NEU !!! Das Handbuch zum ISA 2006 - http://www.msisafaq.de/buch/
Database mirroring works either way depending on which mode you're in. It's
the automatic failover in high safety mode that may or may not meet your
needs. Specifically, not the failover part but rather the new client
connections part post failover (as per your scenario).
Automatic failover only works if your client's data access layer (SNAC or
ADO/ODBC.NET) has cached both server names. This happens in the first
connection attempt on the server. This is usually the primary server unless
you have specific code in your app (just a few simple lines) to test for
both. Many DB Mirroring implementations are "as-is" using exising apps
without code tweaks so they connect to their "original" primary as per
normal ops but benefit from automatic reconnect in a failover.
However, if the primary server was down before the client connects for the
first time, it would not know there is a mirror server to connect to which
is what you're seeing. Whether it's a new client connection or a re-started
app/web server, it's the same effect; you don't have the mirror server name
cached so the app/web server or client doesn't know it exists.
joe.
"Christian Grbner [MVP]" <newsgroups@.groeby.net> wrote in message
news:uunWT7wuHHA.2360@.TK2MSFTNGP06.phx.gbl...
Hello,
I'm just configuring a Database Mirror with SQL 2005 SP2. I have two
SQL-Servers SQL1 and SQL2 and one Whitness. Everything is working fine, when
I plug out the ethernet cable from SQL1 the whitness is detecting the
failure an is switching over to SQL2 and so on. The running application is
connecting to SQL2 after the failure. In case of a restart of the
application the client tries to connect to SQL1, which is not reachable. As
I'm expecting there should be a connect to SQL2 after the sqlclient can't
reach SQL1 but with netstat -na I only see connection attempts to SQL1. From
a former posting I got the Information about some existing connections to
SQL1, but I made a restart of the client so be sure that there is no
existing connection to SQL1. When both SQL-Servers are reachable and I do a
manual failover everything is working fine, so I think database mirroring
only works, when both servers are online. Is this true?
My connection string looks like this:
Data Source=DBSRV1;Failover Partner=DBSRV2;Initial Catalog=TEST;User
ID=test;Password=test;Network Library=DBMSSOCN;Load Balance Timeout=5"
Thanks for your help
Greetings from Germany
Christian
Christian Grbner
MVP ISA Server
Hilfe & Infos rund um den ISA Server: http://www.msisafaq.de !!!!
NEU !!! Das Handbuch zum ISA 2006 - http://www.msisafaq.de/buch/

Database Mail and Activation Error

Hi All,

I'm having some issues getting Database Mail to work with SQL Agent Job's which is doing my fruit !

I'm configuring the server using the following script:

DECLARE

@.DataFile varchar(1000),

@.LogFile varchar(1000),

@.BackupFile varchar(1000),

@.cmd Varchar(8000),

@.ServerName varchar(1000),

@.AccountName Varchar(1000),

@.EmailAddress Varchar(1000),

@.MailSubject VARCHAR(500),

@.FullPath varchar(400),

@.Drop varchar(100)

--Configure SQL Server DBMail

SET

@.AccountName = (SELECT REPLACE(@.@.ServerName, '\','-') + ' Email Profile')

SET

@.EmailAddress = (SELECT REPLACE(@.@.ServerName, '\','-') + '@.OurDomain.com')

SET

@.ServerName = (SELECT REPLACE(@.@.ServerName, '\','-'))

IF EXISTS

(SELECT Name FROM msdb.dbo.sysmail_profile WHERE NAME = @.AccountName)

BEGIN

PRINT 'Database Mail Profile Already Exists - Skiping Step'

END

ELSE

BEGIN

EXECUTE msdb.dbo.sysmail_add_account_sp

@.account_name = @.AccountName,

@.description = 'Mail account for administrative e-mail.',

@.email_address = @.EmailAddress,

@.replyto_address = 'SQL_Notify@.OurDomain.com',

@.display_name = @.ServerName,

@.mailserver_name = 'MailServer.net' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@.profile_name = @.AccountName,

@.description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@.profile_name = @.AccountName,

@.account_name = @.AccountName,

@.sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@.profile_name = @.AccountName,

@.principal_name = 'Public',

@.is_default = 1 ;

EXEC

msdb.dbo.sp_set_sqlagent_properties @.email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @.AccountName

SET

@.MailSubject = 'Database Mail Test Message from ' + @.@.SERVERNAME

PRINT

'Created DBMail Profile'

END

GO

DECLARE @.MailSubject VARCHAR(500)

DECLARE @.AccountName Varchar(1000)

SET

@.AccountName = (SELECT REPLACE(@.@.ServerName, '\','-') + ' Email Profile')

EXEC

msdb.dbo.sp_set_sqlagent_properties @.email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'Default Email Profile'

SET

@.MailSubject = 'Database Mail Test Message from ' + @.@.SERVERNAME

--Check if DB Operator alredy exists; Create if not.

IF EXISTS (Select Name FROM MSDB.dbo.SysOperators WHERE NAME = 'SQL Notify')

BEGIN

PRINT 'Database Operator Already Exists - Skipping Step'

END

ELSE

BEGIN

EXEC msdb.dbo.sp_add_operator @.name=N'SQL Notify',@.enabled=1, @.pager_days=0, @.email_address=N'SQL_Notify@.OurDomain.com'

END

EXEC

msdb.dbo.sp_send_dbmail

@.profile_name = @.AccountName,

@.recipients = 'SQL_Notify@.OurDomain.com',

@.subject = @.MailSubject,

@.body = 'This is a Database Mail Test Message'

GO

OK - I get the test message, however when I setup a SQL Agent job later in the same script to alert me on failure; I dont get any mails through.

When I check the SysMail Event Log; I see the followign error:

error 2007-07-20 15:58:07.623 Activation failure. NULL NULL NULL 2007-07-20 15:58:07.623 sa

The SQLAgent is running under the same account as SQLServer; neither are local admin's on the machine. I've restatred SQLAgent using SSCM a number of times yet nothing comes through.

Mail Profile is started; Service Broker is enabled in MSDB

SQL Agent is configured with the correct profile; an operator is set as a failsafe operator.

Does anyone have any suggestions?

Try stopping and re-starting the SQL Agent.

I had the same issue.

Database Mail and Activation Error

Hi All,

I'm having some issues getting Database Mail to work with SQL Agent Job's which is doing my fruit !

I'm configuring the server using the following script:

DECLARE

@.DataFile varchar(1000),

@.LogFile varchar(1000),

@.BackupFile varchar(1000),

@.cmd Varchar(8000),

@.ServerName varchar(1000),

@.AccountName Varchar(1000),

@.EmailAddress Varchar(1000),

@.MailSubject VARCHAR(500),

@.FullPath varchar(400),

@.Drop varchar(100)

--Configure SQL Server DBMail

SET

@.AccountName = (SELECT REPLACE(@.@.ServerName, '\','-') + ' Email Profile')

SET

@.EmailAddress = (SELECT REPLACE(@.@.ServerName, '\','-') + '@.OurDomain.com')

SET

@.ServerName = (SELECT REPLACE(@.@.ServerName, '\','-'))

IF EXISTS

(SELECT Name FROM msdb.dbo.sysmail_profile WHERE NAME = @.AccountName)

BEGIN

PRINT 'Database Mail Profile Already Exists - Skiping Step'

END

ELSE

BEGIN

EXECUTE msdb.dbo.sysmail_add_account_sp

@.account_name = @.AccountName,

@.description = 'Mail account for administrative e-mail.',

@.email_address = @.EmailAddress,

@.replyto_address = 'SQL_Notify@.OurDomain.com',

@.display_name = @.ServerName,

@.mailserver_name = 'MailServer.net' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@.profile_name = @.AccountName,

@.description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@.profile_name = @.AccountName,

@.account_name = @.AccountName,

@.sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@.profile_name = @.AccountName,

@.principal_name = 'Public',

@.is_default = 1 ;

EXEC

msdb.dbo.sp_set_sqlagent_properties @.email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @.AccountName

SET

@.MailSubject = 'Database Mail Test Message from ' + @.@.SERVERNAME

PRINT

'Created DBMail Profile'

END

GO

DECLARE @.MailSubject VARCHAR(500)

DECLARE @.AccountName Varchar(1000)

SET

@.AccountName = (SELECT REPLACE(@.@.ServerName, '\','-') + ' Email Profile')

EXEC

msdb.dbo.sp_set_sqlagent_properties @.email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'Default Email Profile'

SET

@.MailSubject = 'Database Mail Test Message from ' + @.@.SERVERNAME

--Check if DB Operator alredy exists; Create if not.

IF EXISTS (Select Name FROM MSDB.dbo.SysOperators WHERE NAME = 'SQL Notify')

BEGIN

PRINT 'Database Operator Already Exists - Skipping Step'

END

ELSE

BEGIN

EXEC msdb.dbo.sp_add_operator @.name=N'SQL Notify',@.enabled=1, @.pager_days=0, @.email_address=N'SQL_Notify@.OurDomain.com'

END

EXEC

msdb.dbo.sp_send_dbmail

@.profile_name = @.AccountName,

@.recipients = 'SQL_Notify@.OurDomain.com',

@.subject = @.MailSubject,

@.body = 'This is a Database Mail Test Message'

GO

OK - I get the test message, however when I setup a SQL Agent job later in the same script to alert me on failure; I dont get any mails through.

When I check the SysMail Event Log; I see the followign error:

error 2007-07-20 15:58:07.623 Activation failure. NULL NULL NULL 2007-07-20 15:58:07.623 sa

The SQLAgent is running under the same account as SQLServer; neither are local admin's on the machine. I've restatred SQLAgent using SSCM a number of times yet nothing comes through.

Mail Profile is started; Service Broker is enabled in MSDB

SQL Agent is configured with the correct profile; an operator is set as a failsafe operator.

Does anyone have any suggestions?

Try stopping and re-starting the SQL Agent.

I had the same issue.

database mail

HI,

when configuring database mail in sql server 2005 i am getting the below error.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2006-09-13T13:50:18). Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine). )

when i ping the smtp server, i could successfully connect to the smtp server. but when i send a test email i am getting the above error message saying not able to connect to the server.

please help

There are so many mail components in SQL Server Microsoft did not like what we were doing with SQL Server so there are some restrictions in 2005 so try the links below. If the mail include SQL Server Agent you need to give the Agent correct permssions. Try the links below post again if you still have questions. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177580.aspx

http://msdn2.microsoft.com/en-us/library/ms191207.aspx

|||Make sure the startup account of SQL Server database engine service is the same as the domain account associated with the mail profile which used to send the databse mail.