Contained databases are the best choice if you have migration in mind and with
SQL Servers both in-house and the cloud, migration will always be an important
item to consider.
Read this article for the benefits of Contained databases:
http://stackoverflow.com/questions/5932293/what-is-the-real-benefit-of-
contained-databases
SQL Server 2012 only supports partially contained databases and in future
versions this may change.
You can create a partially contained database in SQL Server 2012 Express using
either T-SQL or SQL Server Management Studio.
Creating a contained database
1.The first step is enabling containment in the Server. It is assumed here that
you have an instance of SQL Server 2012 Express installed on your desktop/laptop.
Right click the server and click on Properties to open the Server Properties
window. Click on Advanced in the Select a Page list.
The Advanced Page is displayed and here you can enable containment as shown.
Right click Databases node and click New Database... to open the New Database
window.
The database will be created as shown in the Server (local)/PCATT owned by
Hodentekwin7\mysorian, the computer administrator.
2. Now you need to create a User with a password (which means the server
authentication should be SQL Server and Windows Authentication mode as shown in
the Server properties page):
3. Click Security node of the HContained database to expand and right click Users
node and click New User....
This opens the Database User - New window where you provide a username and a
password (herein huser, password:resuh). By default the new user will neither
own any schema,nor has any membership in database role. For him to be able to
connect, he should be the owner of db_owner schema. The new user should also
have login for the computer.
Here is how the user huser was created
USE [HContained]
GO
/****** Object: User [huser] Script Date: 1/27/2014 10:40:01 PM ******/
CREATE USER [huser] WITH PASSWORD=N'hÀ]™t#Ux ÐöÀ Ó <Ö—Y¡nWâýgÁ˜éayÅÄ',
DEFAULT_SCHEMA=[dbo]
GO
Connecting to partially contained database HContained
1. Bring up the Connect to Server dialogue as shown. Change authentication to SQL
Server Authentication, enter the user created earlier(huser, password resuh)
and if you just click connect you will get an error as huser has no server
login.
2.Now click Options button in the Connect to Server dialogue.
Click the download handle on Connect to database and replace <default> with
HContained as shown.
3. Now click Connect.
You immediately get connected to Hcontained database as shown.
This was achieved after taking care of some errors that cropped up. The fixes
were as follows:
1. SQL Server Management Studio version 11.0.2100.60 was used. Attempts to
connect to HContained resulted in the following error message. The fix to
rectify the situaion is also shown in the image.
2. The above fix still did not allow connecting to the Contained database. The
reason appears to be that the RTM version of SSMS does not support connection as described. In order to connect, the SP1 version of SSMS 2012 Express was needed. There are atleast two ways to get the SP1 version of SSMS 2012. Install SQL Server 2012 Express SP1 Advanced version which installs the client tools or install SSMS 2012 Express SP1.
Follow this link to download SQL Server 2012 Express SP1 files:
http://msdn.microsoft.com/en-us/evalcenter/hh230763.aspx
You will be able to download any of the following:
SQL Servers both in-house and the cloud, migration will always be an important
item to consider.
Read this article for the benefits of Contained databases:
http://stackoverflow.com/questions/5932293/what-is-the-real-benefit-of-
contained-databases
SQL Server 2012 only supports partially contained databases and in future
versions this may change.
You can create a partially contained database in SQL Server 2012 Express using
either T-SQL or SQL Server Management Studio.
Creating a contained database
1.The first step is enabling containment in the Server. It is assumed here that
you have an instance of SQL Server 2012 Express installed on your desktop/laptop.
Right click the server and click on Properties to open the Server Properties
window. Click on Advanced in the Select a Page list.
The Advanced Page is displayed and here you can enable containment as shown.
Right click Databases node and click New Database... to open the New Database
window.
- In the General page you can provide a name for the database, herein HContained.
- In the Options page pick Partial for Containment type as shown.
The database will be created as shown in the Server (local)/PCATT owned by
Hodentekwin7\mysorian, the computer administrator.
2. Now you need to create a User with a password (which means the server
authentication should be SQL Server and Windows Authentication mode as shown in
the Server properties page):
3. Click Security node of the HContained database to expand and right click Users
node and click New User....
This opens the Database User - New window where you provide a username and a
password (herein huser, password:resuh). By default the new user will neither
own any schema,nor has any membership in database role. For him to be able to
connect, he should be the owner of db_owner schema. The new user should also
have login for the computer.
Here is how the user huser was created
USE [HContained]
GO
/****** Object: User [huser] Script Date: 1/27/2014 10:40:01 PM ******/
CREATE USER [huser] WITH PASSWORD=N'hÀ]™t#Ux ÐöÀ Ó <Ö—Y¡nWâýgÁ˜éayÅÄ',
DEFAULT_SCHEMA=[dbo]
GO
Connecting to partially contained database HContained
1. Bring up the Connect to Server dialogue as shown. Change authentication to SQL
Server Authentication, enter the user created earlier(huser, password resuh)
and if you just click connect you will get an error as huser has no server
login.
2.Now click Options button in the Connect to Server dialogue.
Click the download handle on Connect to database and replace <default> with
HContained as shown.
3. Now click Connect.
You immediately get connected to Hcontained database as shown.
This was achieved after taking care of some errors that cropped up. The fixes
were as follows:
1. SQL Server Management Studio version 11.0.2100.60 was used. Attempts to
connect to HContained resulted in the following error message. The fix to
rectify the situaion is also shown in the image.
2. The above fix still did not allow connecting to the Contained database. The
reason appears to be that the RTM version of SSMS does not support connection as described. In order to connect, the SP1 version of SSMS 2012 Express was needed. There are atleast two ways to get the SP1 version of SSMS 2012. Install SQL Server 2012 Express SP1 Advanced version which installs the client tools or install SSMS 2012 Express SP1.
Follow this link to download SQL Server 2012 Express SP1 files:
http://msdn.microsoft.com/en-us/evalcenter/hh230763.aspx
You will be able to download any of the following:
- LocalDB (MSI installer)
- Express (Containing only the database engine)
- Express with Tools (with LocalDB, Includes the database engine and SQL Server Management Studio Express)
- SQL Server Management Studio Express (Tools only)
- Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search)
Better you guys don't play with these settings without being enough confident.
ReplyDeleteI highly recommend that you contact ServerBuddies instead, they are fantastic, I highly recommend to use these guys!
They provide quality remote server management, including troubleshooting, Server Management, Plesk Support, Server Maintenance, Server Monitoring, Server Troubleshooting and support at a affordable rates. They also provide Server Optimization, Plesk Support, Linux Support, cPanel Support and Plesk Support.