In Memory - Memory optimized tables in SQL Server 2014 and SQL Server 2016

One of the biggest feature enhancement for SQL Server 2014 is In Memory Table.
Which is a different table structure compared to traditional 8 KB/Page structure .This is the one of the limitation of  SQL Server that cant process more requests whether there is more memory or faster disk available.


Design concerns (SQL Server 2014 ) 

Table construct
Fixed ; No ALTER TABLE
No LOB data types
No Constraints support (Only Primary Key
No Identity or calculated column or CLR
Data and Table Size
Maximum size Schema and Data :512 GB

SQL Server 2016 Enhancements on in Memory OLTP
Supports Full schema changes ADD/ALTER/DROP  for Column and constraints
MARS Support
ADD/DROP index Support
MERGE Operation
Improvements to DMVs sys.dm_db_xtp_checkpoint_stats ,sys.dm_db_xtp_checkpoint_files 
Support for Transparent Data Encryption.
Operational Analytics - Columnstore index on in memory table

Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables.

Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure. In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. SQL Server is also able to natively compile memory-optimized tables

Other design enhancement in SQL Server 2016 Management studio for Memory optimized table
Memory Optimization Advisor
Native compilation advisor

In-Memory OLTP provides full durability for memory-optimized tables. When a transaction happens to an in memory table SQL Server guarantees that the changes are permanent (will survive a database restart), provided the underlying storage is available.


When to you use In Memory table ?
increase transnational throughput
You want to improve data ingestion
Reduce latency
Remove latency spike
If you want to store data for staging state

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Scenario

After update SQL Server 2012 or 2014 with new service pack then SQL Server stop working , does not start service with  following event log

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 3906, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

My Environment

SQL Server 2014 Enterprise Edition  running on Windows 2012 R2 .

Reason

In my case the SSISDB was a member of availability group database, The instance which I was updated was secondary. If you face issue the following step with help you to solve this

Start SQL Server service with Trace Flag 902:
 > Net Start MSSQL$InstanceName /T902
Remove SSISB From availability group (do this from primary )
Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under \Program Files\Microsoft SQL Server\MSSQL12.MSSQL$Instancename \MSSQL\Install
Stop SQL Server services: 
 > Net Stop MSSQL$InstanceName
Start SQL server service from SQL Server configuration manager
Add SSISDB back to Availability Group