Windows 2012 Hosting - MVC 6 and SQL 2014 BLOG

Tutorial and Articles about Windows Hosting, SQL Hosting, MVC Hosting, and Silverlight Hosting

SQL Server 2012 Hosting - How To Create Contained Databases in SQL Server 2012

clock April 29, 2013 08:52 by author andy_yo

Contained databases are a new feature in SQL Server 2012 and are defined on MSDN Library as ”a database that is isolated from other databases and from the instance of SQL Server that hosts the database”.

About ASPHostPortal.com
ASPHostPortal.com is Microsoft No #1 Recommended Windows and ASP.NET Spotlight Hosting Partner in United States. Microsoft presents this award to ASPHostPortal.com for ability to support the latest Microsoft and ASP.NET technology, such as: WebMatrix, WebDeploy, Visual Studio 2012, ASP.NET 4.5, ASP.NET MVC 4.0, Silverlight 5 and Visual Studio Lightswitch. Click here for more information

The containment setting of a database can be NONE, PARTIAL or FULL. But only NONE and PARTIAL are supported on SQL Server 2012.

Benefits and characteristics.
The following are some of the benefits and characteristics that contained databases have:

  • They make easier to migrate databases from one server to another. Errors related to orphan users are no longer an issue with contained databases, since a contained database user can now be created without an associated login.
  • Authentication can now occur at the database level.
  • Contained database users can be Windows and SQL Server authentication users.
  • A contained database user can access only contained database objects. They cannot access system databases and cannot access server objects.
  • Metadata is stored on the contained database and not stored on system databases. This makes contained databases more portable than the databases we know. 

Disadvantages and limitations.
Some disadvantages and limitations are the following:

  • There are some security concerns. A database owner can create contained database users without the permission of a DBA. The possibility of denial of service attacks exist with contained databases using AUTO_CLOSE option. For security best practices about contained databases, please see the references shown at the end of the article.
  • Partially contained databases cannot use replication, change data capture, change tracking, numbered procedures, schema-bound objects that depend on built-in functions with collation changes. You may find more limitations on the references shown at the end of this article.

Requirements of contained databases.

  • It is required to enable contained databases on the instance.
  • The contained database needs to be added to the connection string or specified when connecting via SQL Server Management Studio.

Step-by-step instructions on how to create a contained database.
To be able to create contained databases on a SQL Server 2012 instance, we need to enable the contained database authentication option on the instance.
Open SQL Server 2012 Management Studio, connect to the instance, make a right click on the name of the instance on Object Explorer, select the Advanced page on the "Select a page" panel, and set to true the "Enable Contained Databases" option.


Alternatively, you can use sp_configure system stored procedure to enable contained databases on the instance, as shown below

EXEC sp_configure 'show advanced', 1
GO
RECONFIGURE
GO

EXEC sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO

When a database is created the "Containment type" should be set to "partial" to make the database a contained database, as shown below.

This can be done using T-SQL too, as shown below.

A contained database allows the creation of a database user that is not associated to an instance login. A contained database user can be created expanding the Security folder on the contained database, making a right click on the Users folder and selecting the "New User" option.

Next, set the user type to "SQL user with password", assign a user name, set the password for the database user and specify the default schema for the user.

Specify the roles for this user on the database and click OK.

If you would like to create the contained database user using T-SQL, please see the example below.
CREATE USER [MorilloCD2User]
    WITH PASSWORD=N'[email protected]',
   
DEFAULT_SCHEMA=[dbo]
GO

To connect a contained database user to a contained database, the database name should be specified on the connection string. If you are using SQL Server Management Studio, on the Connect to Server dialog specify the authentication, specify the user name, provide the password, click on the Options button and specify the database name on the "Connect to database" combo box.



SQL Hosting - ASPHostPortal :: How to Schedule Backup with SQL Server Express

clock December 14, 2012 09:31 by author Jervis

Microsoft’s SQL Server Express is a fantastic product for anyone needing a relational database on a limited budget. Server Express is free but it comes with a few limitations such as only utilizing 1 GB of RAM,  databases are limited to 10 GB, and it does not include SQL Profiler. For low volume sites that do not need enterprise level capabilities, this is a compelling solution. Here is a complete SQL Server feature comparison of all the SQL Server editions.

There is one other important limitation that needs to be mentioned which is that SQL Server Express does not include SQL Agent. This means that you can not schedule administrative tasks such as backing up your databases.  As everyone knows backing up data is critical and with SQL Server Express you can still backup your databases but you have to do it manually. So what does one do if you don’t have a budget to license SQL Server  but you need scheduled backups of your databases?

The answer is SQL Scheduler. SQL Scheduler is a simple lightweight application that installs on your server and runs as a service. Using the application’s GUI you can create multiple jobs to run your backups on a predefined schedule and you can even configure it to send you an email notification if it completes or fails to run properly.

After you download the program, unzip the archive and run InstallService.bat file from the command prompt. The installation will complete quickly.

Once you launch the program you just need to connect to your local SQL Server Express instance on your server. Here is how it will appear without any jobs configured.

From the File menu create a new job. In this example we’ll create 1 job to take a full backup of each database that is configured on the server.

Click on the Details tab and you’ll be able to enter your SQL statements for whatever task you’re trying to accomplish. Be sure to edit the script and change the path of where you want the database backups stored on your server.

On the schedule tab you can define the time and date of your job will run.

To have the job send you an email upon completion or in the event of a failure open the SQLScheduler.WindowsService.exe.config file and enter the mail server address along with the and username and password for authentication.

<system.net>
  <mailSettings>
    <smtp deliveryMethod="Network" from=”[email protected]”>
      <network host="locahost" userName="x" password="x” />
    </smtp>
  </mailSettings>
</system.net>

Once the job runs a full backup will be created for each database and it will be stored in a subfolder of the path you specified in the script.
Now that your daily full backups are being created locally on the server the next step would be to either configure Windows Server Backup or configure 3rd party online backup solution.

 



About ASPHostPortal.com

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Sign in