Send alert out when SQL Server Restarts

An easy to set up procedure which runs every time SQL Server starts. This can be particular useful in clusters if the instances can move around nodes.

CREATE PROCEDURE [dbo].[spEmailSQLServerRestart]
AS
  BEGIN
-- Declare Variables
DECLARE @strServer VARCHAR(128) = CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
DECLARE @strMailSubject VARCHAR(128) = 'SQL Server '
+ UPPER(@@SERVERNAME) + ' restarted!'
DECLARE @strMailBody VARCHAR(1000) = 'SQL Server '
+ UPPER(@@SERVERNAME) + ' restarted at '
+ CONVERT(VARCHAR(12), GETDATE(), 108) + ' on '
+ CONVERT(VARCHAR(12), GETDATE(), 103)
+ ' Now running on server: ' + @strServer
-- Wait for the database mail engine to start
WAITFOR DELAY '00:00:15'

DECLARE @sender varchar (50)
SELECT @sender = name from msdb..sysmail_profile where profile_id = 1

-- Send Email
EXEC msdb.dbo.sp_send_dbmail @profile_name = @sender,
@recipients = 'DBAOnCallAlerts@autotrader.co.uk',
@subject = @strMailSubject, @body = @strMailBody,
@body_format = 'HTML';
END

Just deploying the procedure in this scenario is not enough to trigger the alert. We now need to tell SQL Server that it should scan for this procedure every time in starts up. This is known as auto execution in SQL Server and is set by sp_procoption (from SQL Server 2008)

To Set a procedure to run on instance startup, you can run:

-- Sets stored procedure for automatic execution.
sp_procoption    @ProcName = 'spEmailSQLServerRestart',
@OptionName = 'startup',
@OptionValue = 'on'

To see which procedures have the startup option set:

SELECT name, type_desc, create_date, modify_date
FROM sys.procedures
WHERE is_auto_executed = 1

From Microsoft:

Startup procedures must be in the master database and cannot contain INPUT or OUTPUT parameters. Execution of the stored procedures starts when all databases are recovered and the “Recovery is completed” message is logged at startup.

 

Installing MongoDB on a Mac with Homebrew

Homebrew is a great little utility to aid application installations and does a great job in packaging up and simplifying the install process.

To learn more about Homebrew visit https://brew.sh/ or the Wiki page here

Installing MongoDB using Homebrew is pretty straightforward but some familiarity with the terminal is beneficial.

Open the Terminal app and type the following:

brew install mongodb

This should trigger a set of actions, of which the first will be a brew update

Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/core).

After updating itself, brew will download the latest mongodb package ‘bottle’ and install it using the default settings.

==> Downloading https://homebrew.bintray.com/bottles/mongodb-3.4.4.sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring mongodb-3.4.4.sierra.bottle.tar.gz
==> Caveats
To have launchd start mongodb now and restart at login:
  brew services start mongodb
Or, if you don't want/need a background service you can just run:
  mongod --config /usr/local/etc/mongod.conf
==> Summary
🍺  /usr/local/Cellar/mongodb/3.4.4: 17 files, 266.3MB

As this point, there is enough to get started with a mongodb instance, or to use the mongo shell to connect to a remote instance.

To start the instance locally without a service:

mongod --config /usr/local/etc/mongod.conf —fork

Then connect into the instance:

mongo —port 27017

Alterntively, to start as a brew service, run:

brew services start mongodb

Setting up SQL Server vNext CTP 2.0 on Docker

Recently I moved into the brave new world of having a Macbook pro as my main laptop, and one of my first frustrations was the inability to be able to run SQL Server Management Studio locally. Unfortunately Microsoft haven’t come up with a solution for that one, so I’m having to do with a mix of SQLPro Studio and running SSMS inside a VDI machine on the mac.

One thing the Mac has made easier for me is the ability to start testing SQL Server vNext locally and without too much messing around. So when Microsoft released vNext 2.0 on the 19th April 2017, it seemed like a good opportunity to give it a try. (Whats new in SQL Server Linux)

This post should cover what you need to do to get a docker image of SQL Server running on macOS Sierra. The Microsoft documentation is pretty good on this subject, and not convoluted but I always find it good to blog how exactly I’ve done things, as there are always little quirks.

Install Docker for Mac

I downloaded the stable version just to be a bit safer and so that I wouldn’t introduce any unnecessary pinch points.

https://docs.docker.com/docker-for-mac/install/

Once installed, Start the Docker application – it may take a few minutes to initialise, but eventually you will end up with the docker ship icon in the top bar.

There are a few minimum requirements for running docker for the SQL Server image:

  • Docker Engine 1.8+ on any supported Linux distribution or Docker for Mac/Windows.
  • Minimum of 4 GB of disk space
  • Minimum of 4 GB of RAM

If your downloading a recent release of docker then the engine shouldn’t be an issue.

At the time of writing, my version is:

$ docker version
Client:Version	17.03.1-ce
API Version: 1.27
Go Version: go1.7.5

Server:
Version: 17.03.1-ce
API Version: 1.27 (minimum version 1.12)
OS/Arch: linux/amd64

Docker default starts with 2GB of RAM, but this can be easily changed to 4GB from the docker preferences in the advanced section.

Pull down and run the latest SQL Server Docker Image – Step by Step details here – https://docs.microsoft.com/en-gb/sql/linux/sql-server-linux-setup-docker

Quick Steps here:

sudo docker pull microsoft/mssql-server-linux

Note that I have added a –name switch to the docker command below. This is to simplify things once we have everything up and running as the container name is quite critical for more or less every command in docker.

sudo docker run --name SQL2017 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=' -p 1433:1433 -d microsoft/mssql-server-linux

To make sure we have successfully created our container, we can run:

docker ps -a

To familiarise yourself with other docker commands, you can run:

docker --help

Connect to the SQL Server

sudo docker exec -it SQL2017 /bin/bash

The flags are for the following:

-i, –interactive Keep STDIN open even if not attached–privileged Give extended privileges to the command-t, –tty Allocate a pseudo-TTY

This then takes you into the interactive command line for the docker image.

The folder structure by default is /var/opt/mssql for the database files & error logs. Sqlcmd is in /opt/mssql-tools/bin/

Reading the error log

While in exec mode for the SQL2017 container, run the following cat command:

cat /var/opt/mssql/errorlog

There is a very good guide by microsoft on how to troubleshoot SQL Server Linux which can be found here – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide

Using sqlcmd

Starting with SQL Server vNext CTP 2.0, the SQL Server command-line tools are included in the Docker image. If you attach to the image with an interactive command-prompt (as I have done above), you can run the tools locally.

First step is to add the sqlcmd path to the $PATH environment variable. This step isn’t critical but makes sqlcmd accessible from any location on the docker image

PATH=$PATH:/opt/mssql-tools/binecho 'export PATH="$PATH:/opt/mssql-tools/bin"'
~/.bash_profile 
root@5214e1df3c86:/opt/mssql-tools/bin# echo $PATH
/opt/mssql-tools/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
sqlcmd -S . -U sa -P
1> select name from sys.databases
2> go
name
-----------------------------------------------------------
master
tempdb
model
msdb

Installing SQLCMD on macOS

/usr/bin/ruby -e "$(curl -fsSL <a href="https://raw.githubusercontent.com/Homebrew/install/master/install" data-mce-href="https://raw.githubusercontent.com/Homebrew/install/master/install">https://raw.githubusercontent.com/Homebrew/install/master/install</a>)"
brew tap microsoft/mssql-preview <a href="https://github.com/Microsoft/homebrew-mssql-preview" data-mce-href="https://github.com/Microsoft/homebrew-mssql-preview">https://github.com/Microsoft/homebrew-mssql-preview</a> 
brew update 
brew install mssql-tools #for silent install ACCEPT_EULA=y 
brew install mssql-tools

 

Can be quickly tested locally by opening a terminal window and running:

sqlcmd -S my-remote-server -U test -P test
1&gt; select @@SERVERNAME
2&gt; go
-----------------------------------------------------------------
SQLSERVER/MYNAMEDINSTANCE
(1 rows affected)

Connecting into the docker image from host machine

Get the IP of the container

MCR-AL33450:/mohsin.alipatel$ docker inspect --format "{{ .NetworkSettings.Ports}}" SQL2017
map[1433/tcp:[{0.0.0.0 1433}]]

Then make a sqlcmd connection using the IP and port above

MCR-AL33450:/mohsin.alipatel$ sqlcmd -S 0.0.0.0,1433 -U sa -P
1&gt;Select @@servername
2&gt;go-----------------------------------------------------------------------------------------------
5212e1df3c86
(1 rows affected)

This should provide a basic introduction to both docker and to SQL server on linux.

ADRCI error – DIA-48448: This command does not support multiple ADR homes

Problem: When running adrci on an oracle server with multiple homes, you can get the following error when trying to complete some actions:

DIA-48448: This command does not support multiple ADR homes

Reason is existence of multiple oracle database instances/ASM/Listener homes etc. Solution is to set the ADRCI home for the instance you want to operate.

As the error above stipulates, this is because the adrci home is not set. To check the homes available on the server:

ADRCI: Release 12.1.0.2.0 – Production on Tue Feb 21 22:03:57 2017

ADR base = “/u01/app/oracle”
adrci> show homes
ADR Homes:
diag/crs/myhost/crs
diag/clients/user_oracle/host_2668819005_11
diag/clients/user_oracle/host_2668819005_82
diag/rdbms/mydb/MYINSTANCE
diag/rdbms/_mgmtdb/-MGMTDB
diag/asm/+asm/+ASM1
diag/tnslsnr/myhost/listener
diag/tnslsnr/myhost/listener_scan1
diag/tnslsnr/myhost/listener_scan2
diag/tnslsnr/myhost/listener_scan3

to set the home to listener3, run the following command:

adrci> SET HOME diag/tnslsnr/myhost/listener_scan3

You should now be able to complete actions against this home, such as purging alert logs over X number of days.

Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##"

Following error continuously in the agent log after restoring the msdb database

The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following: 'Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##" does not exist, this type of principal cannot be impersonated, or you do not have permission.'

This is normally down to an orphaned login for the ‘##MS_PolicyEventProcessingLogin##’ login

To fix, you can first make sure if an orphaned login is in fact the cause:

USE master
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go
USE msdb
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go

This should show that the SID for the login in the master database is different from the SID in the msdb database

image

Then run the following to correct the orphaned login:

use [master]
go
exec sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##';
go
use [msdb]
go
exec sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##';
go

By rerunning the check, it should show that the SID is now the same in both database_principals tables

USE master
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go
USE msdb
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go

image

To clean up, cycle the agent error log

use msdb
go
sp_cycle_agent_errorlog