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.

 

Advertisements

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.