de-DEen-GB
 
rss

Just can't get enough of IT

This blog is about mostly anything in IT. But the primary focuses are Microsoft Technologies like Exchange, Office 365, Azure and Cloud Security.
On April 1, 2016
0 Comment
6184 Views

The JET Blue Story

The Exchange Server product used the Extensible Storage Engine (ESE, aka JET Blue) to store data for decades. The story of the JET Blue (in contrast to JET Red which is used for Access database) can be read here (https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine). In the acient days of data storage the ESE database was the best choice for storing mostly unstructured data with many dynamic properties.

The Messaging API (MAPI) had been developed in the 1990s to provide programmers with a set of unified interface for easier message exchange. The MAPI documentation at TechNet has been replaced by the current Outlook 2013 MAPI Reference. In todays world it is not easy to find reliable ressource about the original MAPI implementation. The only printed resource is Inside Mapi (Microsoft Programming Series) , ISBN 978-1572313125 , which has been published in 1996.

At Ignite 2015 Ross Smith VI joked about moving the Exchange storage engine to SQL. Back in the day with Exchange 2013 in production and Exchange 2016 coming, this was true. But Ross laid the tracks for the evolution of Exchange.

Exchange Storage Engine Joke at Ignite

But it seems that the Exchange Product Team realized that in today's world with heavily standardized communication and less dynamic requirements than in the 1990s the days of JET blue are over. At the same time SQL Server evolved to mature database solution, capable of handling big data. The question was, if it can store SharePoint data, why not Exchange data. After twenty years of Exchange Server using the good ole ESE engine it was time to move on.

The SQL scripts that are used by Exchange to configure SQL are loacted in $exbin\SQL

Example

CREATE TABLE [dbo].[MAPI_PROPERTIES](
	[MAPI_PROPERTTY_ID] [int] IDENTITY(1,1) NOT NULL,
	[MAPI_PROPERTY_NAME] [nchar](127) NOT NULL,
	[IsWellKnownProperty] [bit] NOT NULL,
	[MAPI_TYPE_ID] [int] NOT NULL,
 CONSTRAINT [PK_MAPI_PROPERTIES] PRIMARY KEY CLUSTERED 
(
	[MAPI_PROPERTTY_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MAPI_PROPERTIES]  WITH CHECK ADD  CONSTRAINT [FK_MAPI_PROPERTIES_MAPI_TYPES] FOREIGN KEY([MAPI_TYPE_ID])
REFERENCES [dbo].[MAPI_TYPES] ([MAPI_TYPE_ID])
GO

ALTER TABLE [dbo].[MAPI_PROPERTIES] CHECK CONSTRAINT [FK_MAPI_PROPERTIES_MAPI_TYPES]
GO

 

Exchange Server goes SQL

The current Exchange 2016 CU2 Preview supports an undocumented registry key to activate SQL Server support for Exchange. Personally I do not know, if this was supposed to be officially included in a public realease. So maybe the SQL support was made available by error and is already removed from the most current build again.

The famous SqueakyLobster registry key in has been used in Exchange 5.5 to troubleshoot performance issues. The new "Lobster" key is used to activate hidden code in Exchange Server product. The name of the key is LobsterMapiDB.

This key activates support for Exchange modern storage. Without this key you won't be able to move mailboxes from ESE legacy storage to SQL modern storage.

It is assumed that a SQL Server 2014 instance is available. A SQL Server 2014 Express edition is sufficient for testing purposes.

Note:
Any changes to configurations or the registry should be validated in a test environment first. Never try this in production right away.

The high level steps required to activate SQL support for Exchange 2016 are:

  • Create a configuration file to provide the SQL connection string
  • Create a SQL server login for the Exchange Trusted Subsystem security group
  • Add a registry key to the local Exchange Server registry
  • Restart Information Store Service (MSExchangeIS)
  • Execute PowerShell script to migrate mailboxes to SQL

The detailed steps are:

  • Create a new config file named Microsoft.Exchange.Data.SQL.exe.config in $exinstall/bin
		<?xml version="1.0" encoding="utf-8" ?>
		<!-- Exchange SQL Configuration - preliminary support -->
		<!-- %MAILBOXDATABASENAME% will be replaced by Exchange -->
		<!-- More information https://goo.gl/QiTtDo -->
		<configuration>
			<sectionGroup name="SqlMapiProviderGroup" type="Microsoft.Exchange.Data.SQL.SqlMapiProviderGroup, Microsoft.Exchange.Data.Common, Version=15.2.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
				<section name="SqlMapiProviderSection" type="Microsoft.Exchange.Data.SQL.SqlMapiProviderGroup, Microsoft.Exchange.Data.Common, Version=15.2.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
		    </sectionGroup>
			<runtime>
				<gcServer enabled="True" />
				<generatePublisherEvidence="False" />
			</runtime>
			<appSettings>
				<add key="MigrateMailboxesAutomatically" value="false" /> <!-- Not yet supported -->
				<add key="AllowJETBlueCoexistence" value="true" /> <!-- Allows for SQL/ESE Coexistence in DAG -->
				<add key="PerDatabaseMaxSize" value="1GB" />
				<add key="VerboseLoggingEnabled" value="False" />
		
			</appSettings>
			<SqlMapiProviderSection>
				<SqlMapiProvider>
					<add name="LobsterMapiDB"
					providerName="System.Data.SqlClient"
					connectionString="Data Source=SERVERNAME\INSTANCE;Initial Catalog=%MAILBOXDATABASENAME%;Integrated Security=True;MultipleActiveResultSets=True" />
				</SqlMapiProvider>
			</SqlMapiProviderSection>
		</configuration>
  • Create a SQL login for Exchange Trusted Subsystem
CREATE LOGIN [DOMAIN\Exchange Trusted Subsystem] FROM WINDOWS
  • Create a new DWORD named LobsterMapiDB in
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ExchangeServer\v15
    and set the data value to 1

    LobsterMapiDB RegistryKey
  • Restart Exchange Information Store

    Restart-Service MSExchangeIS
     
  • Use Move-MailboxToModernStorage.ps1 script to move selected mailboxes to modern storage

More can be found here:

Links

 

Enjoy Exchange for the next 20 years...

Read More »

Description

The SQL Backup Helper is a set of Stored Procedures and User Defined Functions, which help you automate the process of creating backup sets, cloning database and creating a history set of database backups when using a SQL Server or SQL Server Express editions.

Works with

  • SQL Server 2005 or higher
  • SQL Server 2005 Express or higher

Features

  • Create full backups of selected databases, using wildcards of inclusion and exclusion
  • Create differential backups of selected databases, using wildcards of inclusion and exclusion
  • Create transaction log backups of selected databases, using wildcards of inclusion and exclusion
  • Clone databases
  • Copy backup sets to local or UNC folders to create a backup history

Requirements

  • xp_cmdshell
    The extended SP xp_cmdshell must be enabled, as the SPs need access to the file system.
  • Access Rights
    The SQL Server Agent service account (when using SQL Server full edition) needs modify rights (at least) on the configured folders.

Installation

The SQL Backup Helper can be installed by using the setup binary (Red Gates SQL packager executable). The binary lets you choose the following:

  • Database name
  • Database folder
  • Log folder
  • Collation
  • Recovery model
  • Compatibility level
  • Initial database size

The SQL Backup Helper can be installed by setting up the database manually and by running the Schema and MasterData scripts.

Examples

-- How to use SQL Backup Helper
-- Example FULL backup command performing a full backup of all databases, 
-- excluding master,model,msdb,tempdb databases 

EXEC USP_BackupDatabase 'FULL', NULL, 'master,model,msdb,tempdb'


-- Example FULL backup command performing a FULL backup of all databases 
-- starting with DEV, excluding master,model,msdb,tempdb databases

EXEC USP_BackupDatabase 'FULL', 'DEV%', 'master,model,msdb,tempdb'


-- Example for cleaning up history, keeping 3 backup sets, having no maximum age, 
-- including all databases, excluding master,model,msdb,tempdb databases

EXEC USP_CleanUpHistory 3, NULL, NULL, 'master,model,msdb,tempdb'


-- Example DIFF backup command performing a differential backup of all databases, 
-- excluding master,model,msdb,tempdb databases 

EXEC USP_BackupDatabase 'DIFF', NULL, 'master,model,msdb,tempdb'


-- Example TRAN backup command performing a tranaction log backup of all databases, 
-- excluding master,model,msdb,tempdb databases 

EXEC USP_BackupDatabase 'TRAN', NULL, 'master,model,msdb,tempdb'



Version History

  • 1.0, Initial community release

Links

Additional Credits

Additional credits go to Markus Heiliger for the initial version of the SQL Backup Helper.

Follow

Read More »