Contained Database

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. Basically it includes all database settings and the metadata within itself thereby resulting in no configuration dependencies on the instance of the SQL Server Database Engine where the database is actually installed.

This feature is available from SQL Server 2012 and it is available on Azure SQL Database. This feature is not available on Azure SQL Data Warehouse or Parallel Data Warehouse.

One of the benefits of contained databases is that you will no longer struggle with orphaned users when moving databases or working with high availability. From SQL Server 2012 it introduces the notion of boundaries which are borders that define where a database and its metadata and settings end and where server-level features, metadata, and dependencies begin. In addition to implicitly allowing increased portability, boundaries are also used to help explicitly isolate data, settings, features, and capabilities.

There are 3 types of containment levels:

  • NONE. This default containment mode is what exists prior to SQL Server 2012. There are no boundaries defined or visible.
  • PARTIAL. With partially contained databases, you have the ability to define clearer boundaries between databases and the server, making it easier for metadata to be hosted within the databases. This, in turn, makes SQL Server databases more portable and less dependent on underlying hosts.
  • FULL. Full containment will enable greater database portability and allow for strict enforcement of containment boundaries as a means of fully isolating databases from each other and from the underlying host.

In SQL Server 2016 there are 4 ways in which SQL Server helps users to isolate the database from the instance.

  • The metadata that describes the database is maintained in the database and not in the master database.
  • All metadata are defined using the same collation
  • User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server
  • The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.

There are two types of users for contained databases:

  • Contained database user with password. These are authenticated by the database.
  • Windows principals. Authorized windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database. The database trusts the authentication by Windows.

Benefits of using Partially Contained Databases
Database Movement
When moving a database, problems occur with some important information being unavailable after moving to another instance. Login information is stored within an instance and not within a database, therefore when moving the database to another instance will lose this information. The partially contained database can store important information in the database so the database still has the information after it is moved.

Benefit of Contained Database Users with Always On
By reducing the ties to the instance of SQL Server, partially contained databases can be useful during failover when you use Always On Availability Groups. The contained users is a very significant feature in an Always On solution. If the users are contained users, in the event of a failover, people would be able to connect to the failover node without creating logins on the instance hosting the failover node.

Initial Database Development
When creating a database, the developer may not always know where the database will be deployed and all the environmental variables that could impact the database. With a non-contained model, the developer would need to consider all the environmental variables and the impacts it could have on the database and develop accordingly. With using partially contained databases, the developer can identify instance-level impacts on the database.

Database Administration
Database administration becomes easier with maintaining database settings, instead of having the settings in the master database you can now maintain them in the database. This allows each database owner to have more control over their database without giving the database owner sysadmin permission.

Limitations with Partially Contained Databases
With almost any feature it comes with certain limitations. The following are limitations of a Partially Contained Database where the below features are not allowed:

  • Cannot use replication, change data capture, or change tracking.
  • Numbered procedures
  • Schema-bound objects that depend on built-in functions with collation changes
  • Binding change resulting from collation changes, including references to objects, columns, symbols, or types.

Identifying Database Containment
There are two tools to identify the containment status of a database, sys.dm_db_uncontained_entities and database_uncontained_usage event. sys.dm_db_uncontained_entities is a view that shows all potentially uncontained entities in the database. However if any entities are using dynamic SQL, it cannot be determined until runtime whether the entity is contained or not. Database_uncontained_usage Xevent occur whenever an uncontained entity is identified at run time. Therefore, any uncontained user entities you have not run will not be identified by this XEvent.

This could be an awesome feature to use for database developers from SQL Server 2012, hope this post was helpful for you!

10252137_641249792618222_6085988990639587497_n

Registered Servers in SQL Server Management Studio

Many people hear about Registered Servers in SSMS and then completely forget about it and how useful it can be if they are in a large environment where they need to connect to many servers. Registered Servers allows you to save the server details in SSMS and give it a friendly name so when you need to connect to the server you can just double click on it.

Benefits of Registered Servers:
• Preserve the connection information.
• Determine if a registered server is running.
• Easily connect Object Explorer and Query Editor to a registered server.
• Edit or delete the registration information for a registered server.
• Create groups of servers.
• Provide user-friendly names for registered servers.
• Provide detailed descriptions
• Export registered server groups.
• Import registered server groups.
• View the SQL Server log files for online or offline instances of SQL Server.

The simplest way to register a server is to right click on the instance of the server you are connected to in your object explorer and select Register…
Register

In the popup it will automatically put in the server that you are connected to with the method of connection, in my case Windows Authentication. At the bottom you can give the server a friendly name and description and click save.
ServerDetails

To open up the Registered Servers window, you can select View on the top menu and Registered Servers.
View

Your Registered Servers window shall appear above your Object Explorer window. You can them simply double click on a server to connect to it in Object Explorer.
Registered Servers

10372334_640888532654348_5170809058818787420_n

Creating a “Run As” Account for Scheduled ETL Jobs

When working in large SQL production environments that have multiple jobs doing different things you might want the ETL jobs to run as another account other than SQL Server Agent.

This can help with security by limiting the amount of access the account has to the databases the packages in the ETLs are connecting to.

This is quite simple to set up. I recommend using a Windows AD account. In my example below of the setup I shall be demonstrating how to create an account to run the ETL jobs.

Step 1:
You will need to add the account to the Logins under Security on the server where the jobs and are executing and the ETL projects are deployed to.
Step 1

Step 2:
You will need to create a Credential under Security that links to the account you created. Right click on Credential and select New Credential…
Step 2

Step 3:
You will give the Credential a name and select the ellipse just below it so that you can select the Windows account you added under Logins in step 1 and click Ok. You will then type in the same password for the Windows AD account and click Ok. Now your credential is set up.
Step 3
Step 3a

Step 4:
Next you will need to add the Credential to the SSIS Package Execution Proxy so that it can execute SSIS packages on the server. Expand the SQL Server Agent node, expand the Proxies node and right click on SSIS Package Execution and select New Proxy…
Step 4

Step 5:
Give your Proxy a name. I choose to use the same name as the Windows AD account throughout the entire setup. Then select the ellipse next to Credential so that you can add the Credential you created in step 3 and click Ok.
Step 5

You will notice that SQL Server Integration Services Package is checked under Active to the following subsystems. This allows the account to execute SSIS packages. You can click Ok and the setup is complete.
Step 5a

You will now see the Proxy you created under SSIS Package Execution.
Step 5b

Step 6:
When you create the new job and in the steps section set the type to SQL Server Integration Services Package, you will then be able to click on the drop down below Run as and you shall see the Proxy account you set up. You can select it and then your job will run as that account.
Step 6

An important note to remember is that the Windows AD account you are using will need Read/Write access on the databases that are used as connections in the ETL packages.

LearnWithPleasure

Running Visual Studio as a different user

When you are working as a consultant at clients and your machine is not on their network and you need to develop in Visual Studio with the credential they provide, it can be quite handy to run your Visual Studio with the Windows credentials they provide.

There are a few ways of doing this, in the post I shall list two different methods and how to achieve this.

Option 1:
Shift + Right-click on the Visual Studio icon and select “Run as a different user”

A pop up will come up for you to insert the Windows Credentials. Fill in the details and click ok, Visual Studio will then open up running as the user you entered.

NB: type the username as domain\username
runas

Option 2:
Step 1:
Open up Run

Step 2:
Type in the following:

runas /netonly /user:domain\username “C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe”

Step 3:
Set the domain and username for the Windows Account details you want to run Visual Studio as. Also make sure that the path specified is correct for your laptop (depends on where you installed Visual Studio)

Step 4:
A CMD prompt will popup asking you to type in the password. You need to type in the password for the Windows Account that you are impersonating.

NB! While you are typing the password in, it will not show that you are typing in anything.