September 10, 2013 at 2:47 am
Hi Fnds,
i am looking to find kind of investigation DB MAil notifications from sqlservers. so i want to identify those servies which server is using mail services. also want migrate one environment to another environment.
Can anyone have idea?#
Replays are welcome.
cheers,
September 10, 2013 at 3:11 am
you could collect an inventory scanning all your sqlserver instances or active dbmail configurations and for dbmail profiles.
ref: http://technet.microsoft.com/en-us/library/ms177580.aspx
/* is dbmail config activated ? */
Select *
from sys.configurations
where [name] = 'Database Mail XPs'
go
/* dbmail helper procs*/
EXEC msdb.dbo.sysmail_help_status_sp ;
exec msdb.dbo.sysmail_help_account_sp;
exec msdb.dbo.sysmail_help_profile_sp
exec msdb.dbo.sysmail_help_principalprofile_sp;
--show sqlagent mail profile ( column email_profile )
exec msdb.[dbo].[sp_get_sqlagent_properties]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2013 at 3:21 am
Thanks mate,
I'll have look now and do you have any idea on migrate those services from local exchange to company mail.
if you can help will be great.
cheers,
September 10, 2013 at 3:49 am
We used smtp with exchange, so we had to get all ip-addresses granted to be able to use the smtp relay for dbmail.
I'm not even sure if we also need to have our service/proxy accounts granted but we still have the AD group for that.
It's all in place for years now and we no longer use exchange, but it still all has to get granted at ip(range) level by our mail admins.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2013 at 4:29 am
Thanks Johan,
Do you know the process how we will migrate. can you give some idea on methods or wizards.
cheers
September 10, 2013 at 4:46 am
for us - the db crowd - there has been no need to alter stuff as the mail team just re-routed the relay definition for smtp.
They prepared their action well and had us test it with some dev servers that were given test-aliasses to check for operational issues with the new-to-be settings.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2013 at 4:48 am
Thanks Johan
I'll work out and let you know when i get into action.
Cheers
September 10, 2013 at 5:37 am
Hi Johan,
I have another another question for you. Actually i am trying to finding SSIS, SSRS,SSAS services installed or running or not on servers, i got bunch of servers from 2000 to 2012. below script is working fine on 2005 & above servers but i got issue with 2000 servers. do you have any other method or script that runs on any server to find sql services ssis,ssrs,ssas. if you any powershell script please update me.
SET NOCOUNT ON
CREATE TABLE #RegResult
(
ResultValue NVARCHAR(4)
)
CREATE TABLE #ServicesServiceStatus
(
RowID INT IDENTITY(1,1)
,ServerName NVARCHAR(128)
,ServiceName NVARCHAR(128)
,ServiceStatus VARCHAR(128)
,StatusDateTime DATETIME DEFAULT (GETDATE())
,PhysicalSrverName NVARCHAR(128)
)
DECLARE
@ChkInstanceName NVARCHAR(128) /*Stores SQL Instance Name*/
,@ChkSrvName NVARCHAR(128) /*Stores Server Name*/
,@TrueSrvName NVARCHAR(128) /*Stores where code name needed */
,@SQLSrv NVARCHAR(128) /*Stores server name*/
,@PhysicalSrvName NVARCHAR(128) /*Stores physical name*/
,@DTS NVARCHAR(128) /*Store SSIS Service Name */
,@FTS NVARCHAR(128) /*Stores Full Text Search Service name*/
,@RS NVARCHAR(128) /*Stores Reporting Service name*/
,@SQLAgent NVARCHAR(128) /*Stores SQL Agent Service name*/
,@OLAP NVARCHAR(128) /*Stores Analysis Service name*/
,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/
SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128))
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
SET @ChkInstanceName = @@serverName
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @TrueSrvName = 'MSSQLSERVER'
SELECT @OLAP = 'MSSQLServerOLAPService' /*Setting up proper service name*/
SELECT @FTS = 'MSFTESQL'
SELECT @rs = 'ReportServer'
SELECT @SQLAgent = 'SQLSERVERAGENT'
SELECT @SQLSrv = 'MSSQLSERVER'
END
ELSE
BEGIN
SET @TrueSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
SET @SQLSrv = '$'+@ChkSrvName
SELECT @OLAP = 'MSOLAP' + @SQLSrv /*Setting up proper service name*/
SELECT @FTS = 'MSFTESQL' + @SQLSrv
SELECT @rs = 'ReportServer' + @SQLSrv
SELECT @SQLAgent = 'SQLAgent' + @SQLSrv
SELECT @SQLSrv = 'MSSQL' + @SQLSrv
END
/* ---------------------------------- Integration Service Section ----------------------------------------------*/
IF CHARINDEX('2008',@@Version) > 0 SET @DTS='MsDtsServer100'
IF CHARINDEX('2005',@@Version) > 0 SET @DTS= 'MsDtsServer'
SET @REGKEY = 'System\CurrentControlSet\Services\'+@DTS
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Intergration Service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@DTS
UPDATE #ServicesServiceStatus SET ServiceName = 'Integration Service ' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'Integration Service ' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Reporting Service Section ------------------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@RS
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Reporting service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@RS
UPDATE #ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ---------------------------------- Analysis Service Section -------------------------------------------------*/
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @OLAP = 'MSSQLServerOLAPService'
END
ELSE
BEGIN
SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName
SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAP
END
INSERT #RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Analysis service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP
UPDATE #ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE #ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE #RegResult
END
SELECT ServerName,PhysicalSrverName, ServiceName, ServiceStatus
FROM #ServicesServiceStatus
DROP TABLE #RegResult
DROP TABLE #ServicesServiceStatus
Thanks,
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply