Downgrade SQL Server 2012 from Standard Edition to Developer Edition

Problem

We have several dev/test servers with SQL Server 2012 Standard edition installed. Since Developer edition is free for development/test environment, so it make sense to downgrade to Developer edition. Unfortunately, Microsoft does not provided a direct method to do it. According to Microsoft online book: https://msdn.microsoft.com/en-us/library/ms143393(v=sql.110).aspx, it is easy to upgrade from cheaper edition to expensive edition, not the other way around.

Solution

We will have to uninstall SQL Server 2012 Standard edition, then install Developer edition. But before doing that, we can backup the system databases, then migrate them later into the new installation.

Steps

1. Preparation: check the instance properties and system database properties, take a screenshot of the root directory and database default locations.
   
2. Run  SQL Server 2012 Configuration Manager, take a screenshot of accounts used by SQL Server services, then stop the services.
3. Copy system databases (master, model, msdb) and ReportServer database to a new folder.
4. Uninstall SQL Server 2012 Standard edition.
5. Install SQL Server 2012 Developer edition, set the root directory and database default locations the same as the previous installation. Make sure the patch level is the same as before.
6. Run SQL Server 2012 Configuration Manager, stop SQL Server services.
7. Copy the system databases (master, model, msdb) and ReportServer database from the backup folder to the current installation folder.
8. Restart SQL Server services.

Send SMS Notification When You Receive an Important Office 365 Email

Problem

In order to monitor our databases servers, many email alerts have been setup in SQL Server to help us know what's happening on the server. It is really helpful for the monitoring and trouble shooting for the database servers. However, it causes the flood of email to my index. Some email alerts need my immediate attention, for example a production is in critical condition, need me to fix it right away. I may miss it just because I cannot read so many email and tell this one is much more important than the others.

Solution

There are several methods to setup special notifications for important office 365 emails:

Method 1. iPhone VIP email alerts.

The iOS email client allow users to add some contacts (for example your boss) as VIP senders, therefore receive VIP email notifications when an email from VIP sender enters the inbox. My cell phone is an Android phone, so this method does not work for me.

Method 2. Office 365 Email Text Message feature
Office 365 email support text message, which can be easily setup:

Limitations: it only support Bell and Telus in Canada. Since I am with Rogers, neither does this method work for me.

Method 3. Email to Text Feature (Cell Phone Carriers) and Office 365 Email Index Rules

Most cell phone carriers provide email to text feature, which can send you a text message once an email was sent to your phone's email inbox. I am with Rogers, my phone's email address is 10digitphonenumber@pcs.rogers.com. This is how I setup my SMS notifications:

1. Add 10digitphonenumber@pcs.rogers.com as a contact in Office 365
2. Create an inbox rule in Office 365 to forward important emails to 10digitphonenumber@pcs.rogers.com

You will get a SMS every time you have received an important email.

Troubleshooting for SQL Server 2014 RTM TempDB Error "Could not allocate space for object 'dbo.SORT temporary run storage"

Problem

Error Message:

Subject: SQL Server Alert System: 'ServerName - Sev:017 - INNSUFIFIENT RESOURCES' occurred on \\ServerName

DESCRIPTION:    Could not allocate space for object 'dbo.SORT temporary run storage:  150684339732480' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Troubleshooting

1. Check tempdb properties

The version of SQL Server on this server is 2014 RTM CU14. Since the application is not certified for SQL Server 2014 SP1 and above, so we are unable to install more patches. Autogrowth of tempdb is disabled. The error message tells me there is no space in tempdb any more.

The first step is to check the properties of tempdb from SSMS: 

It shows that a lot of space available, strange. If this is true, then we should not have gotten the error message.

2. Check DM view sys.dm_db_files_space_usage

DM view sys.dm_db_files_space_usage returns space usage information for each file in the database. Microsoft online book provides more details here. Run this query will get the space usage information from tempdb:

SELECT *

  FROM [tempdb].[sys].[dm_db_file_space_usage]

The results show that the tempdb has been filled up by internal objects. According to Microsoft online book, the following objects are included in the internal object page counters:

• Work tables for cursor or spool operations and temporary large object (LOB) storage
• Work files for operations such as a hash join
• Sort runs

The internal objects should be deallocated after being used. However, in this case, they are not deallocated, and keep growing until reach the space limit.

3. Reboot server during planned application outage

A reboot will recreate the tempdb, therefore cleanup the internal objects. 

Future Work

The next step will be monitoring the growth of internal objects, and to setup alert for tempdb space usage.