Fixing Orphan Users....

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. When you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the permissions.

The problem is that the user in the database is an “orphan”. This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a SID that does not match.

First, to detect orphaned users. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, you can do either of the below.

EXEC sp_change_users_login 'Auto_Fix', 'user'

EXEC sp_change_users_login @Action='update_one', 
@UserNamePattern='TestUser1', 
@LoginName='TestUser1'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Deep into TEMP DB...

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

• Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
• Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
• Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
• Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged.

In SQL Server, tempdb performance is improved in the following ways:

• Temporary tables and table variables may be cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.
• Allocation page latching protocol is improved. This reduces the number of UP (update) latches that are used.
• Logging overhead for tempdb is reduced. This reduces disk I/O bandwidth consumption on the tempdb log file.
• Setup adds multiple tempdb data files during a new instance installation. This task can be accomplished with the new UI input control on theDatabase Engine Configuration section and a command line parameter /SQLTEMPDBFILECOUNT. By default, setup will add as many tempdb files as the CPU count or 8, whichever is lower.
• When there are multiple tempdb data files, all files will autogrow at same time and by the same amount depending on growth settings. Trace flag 1117 is no longer required.
• All allocations in tempdb use uniform extents. Trace flag 1118 is no longer required.
• For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

The tempdb database is similar to the operating system paging file. It’s used to hold temporary objects created by users, temporary objects needed by the database engine, and row-version information. The tempdb database is created each time SQL Server is restarted.

What is SQL Azure...

SQL Azure is Microsoft’s cloud database service. Based on SQL Server database technology and built on Microsoft’s Windows Azure cloud computing platform, SQL Azure enables organizations to store relational data in the cloud and quickly scale the size of their databases up or down as business needs change.

It’s the only database as a service that scales on-the-fly without downtime and helps you efficiently deliver multi tenant apps ultimately giving you more time to innovate and accelerating your time to market. SQL Database’s built-in intelligence quickly learns your app’s unique characteristics and dynamically adapts to maximize performance, reliability, and data protection. You can build secure apps and connect to SQL Database using the languages and platforms you prefer.

• No upfront cost
• No termination fees
• Pay only for what you use
• Per hour billing

You can try out Azure for free https://azure.microsoft.com/en-us/free/

Whats New in SQL Server 2016 Database Engine

Whats New in SQL Server 2016 Database Engine

You can now configure multiple tempDB database files during SQL Server installation and setup.
New Query Store stores query texts, execution plans, and performance metrics within the database, allowing easy monitoring and troubleshooting of performance issues. A dashboard shows which queries consumed the most time, memory or CPU resources.
Temporal tables are history tables which record all data changes, complete with the date and time they occurred.
New built-in JSON support in SQL Server supports JSON imports, exports, parsing and storing.
New PolyBase query engine integrates SQL Server with external data in Hadoop or Azure Blob storage. You can import and export data as well as executing queries.
The new Stretch Database feature lets you dynamically, securely archive data from a local SQL Server database to an Azure SQL database in the cloud. SQL Server automatically queries both local and remote data in the linked databases.
In-memory OLTP:
Now supports FOREIGN KEY, UNIQUE and CHECK constraints, and native compiled stored procedures OR, NOT, SELECT DISTINCT, OUTER JOIN, and subqueries in SELECT.
Supports tables up to 2TB (up from 256GB).
Has column store index enhancements for sorting and Always On Availability Group support.
New security features:
Always Encrypted: When enabled, only the application that has the encryption key can access the encrypted sensitive data in the SQL Server 2016 database. The key is never passed to SQL Server.
Dynamic Data Masking: If specified in the table definition, masked data is hidden from most users, and only users with UNMASK permission can see the complete data.
Row Level Security: Data access can be restricted at the database engine level, so users see only what is relevant to them.