Home » 2012 » August

Monthly Archives: August 2012

SQL Server 2012 || Contained Databases Concept


A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.

Now question raises about the user need on database to login. Contained Database is database that is no more depend on master database for logins. Contained databases contains the user details with database & database user work as login to connect. Once the contained database is moved, the users are moved as well, and users who belong to the contained database will have no access outside the contained database.

In summary, “Database is now self-contained. Database which is ’contained’ will not depend on anything on the server where it is installed.”

Steps:

1) Enable Contained Database

· From SSMS : Steps to enable Contained Database Authentication with SQL Server Management Studio:

1) In Object Explorer, right-click a SQL Server instance, and then click Properties.

2) Select the Advanced page, and in the Containment section, set the Enable Contained Databases to True, and then click OK.

· From T-SQL : Run the following code on SQL Server Denali. This code will enable the settings for the contained database.

sp_configure ‘show advanced options’,1

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure ‘contained database authentication’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

2) Create Contained Database

CREATE DATABASE [ContainedDatabase]

CONTAINMENT = Partial

3) Create User in Contained Database

· Create SQL User in Contained Database for which login not exits or not already exits

USE [ContainedDatabase]

GO

CREATE USER ContainedUser

WITH PASSWORD = ‘india@1234′;

GO

· Create Windows User in Contained Database

CREATE USER DOMAINUSER

· Create SQL User in Contained Database for which login

USE ContainedDatabase

GO

sp_migrate_user_to_contained

@username = N’<LOGIN>’,

@rename = N’keep_name’,

@disablelogin = N’do_not_disable_login’

@username = N’user : Name of a user in the current contained database that is mapped to a SQL Server authenticated login. The value is sysname, with a default of NULL.

@rename = ] N’copy_login_name | N’keep_name‘ : When a database user based on a login has a different user name than the login name, use keep_name to retain the database user name during the migration.

@disablelogin = N’disable_login | N’do_not_disable_login‘ :disable_login disables the login in the master database. To connect when the login is disabled, the connection must provide the contained database name as the initial catalog as part of the connection string.

4) Try if this user can access Contained Database

We will attempt to login in the database with default settings (Change the database : SSMS> Option > Connection Properties > Connect Database).

You will notice that the login would be successful in the server. When expanded it, the user will have access to the contained database only, and not to any other database.

5) Possible Risk :

· A user can grant and create contained database users within contained database without the knowledge of the administrators

· A user of contained database can gains the access of other database, if these databases have the guest account enabled.

Color Coding for SQL Server Management Studio Status Bar


DBA or Developers both are habitual of using multiple SQL server remotely from one server. But the normal issue of running commands on wrong servers due to less of attention on server name displaying in SSMS query windows pane. We can give different color codes to different server to make them more identifiable in single look. I will help in closing careless mistakes & make query window identification for different server more handy & presentable. Once you have different color for different server in the status bar, it will be easier to notice the server against which they are about to execute the script.

Step 1:

Step 2:

Step 3:

New built-in String functions in SQL Server 2012……..Part 5 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. 2 Out 14 explained below.

String functions

· CONCAT : CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string.

CONCAT ( string_value1, string_value2 [, string_valueN ] )

· FORMAT : Use the FORMAT function for locale-aware formatting of date/time and number values as strings. This really a good function because converting a date into desired format with convert along with format number is little uneasy.

FORMAT ( value, format [, culture ] )

New built-in Logical functions in SQL Server 2012……..Part 4 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. 2 Out 14 explained below.

Logical functions

· CHOOSE : CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.

CHOOSE ( index, val_1, val_2 [, val_n ] )

· IIF : It returns one of the two values depending upon whether the Boolean expression evaluates to either True or False.

IIF ( boolean_expression, true_value, false_value )

New built-in Date and time functions in SQL Server 2012……..Part 3 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. 7 Out 14 explained below.

Date and time functions

· DATEFROMPARTS : DATEFROMPARTS returns a date value with the date portion set to the specified year, month and day, and the time portion set to the default. If the arguments are not valid, then an error is raised. If required arguments are null, then null is returned.

DATEFROMPARTS ( year, month, day )

· DATETIMEFROMPARTS : DATETIMEFROMPARTS returns a datetime value. If the arguments are not valid, then an error is raised. If required arguments are null, then a null is returned.

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

· DATETIME2FROMPARTS : DATETIME2FROMPARTS returns a datetime2 value. If the arguments are not valid, an error is raised. If required arguments are null, then null is returned. However, if the precision argument is null, then an error is raised.

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

· SMALLDATETIMEFROMPARTS : SMALLDATETIMEFROMPARTS returns a smalldatetime value. If the arguments are not valid, then an error is thrown. If required arguments are null, then null is returned.

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

· DATETIMEOFFSETFROMPARTS : DATETIMEOFFSETFROMPARTS returns a datetimeoffset data type. The offset arguments are used to represent the time zone offset. If the offset arguments are omitted, then the time zone offset is assumed to be 00:00, that is, there is no time zone offset. If the offset arguments are specified, then both arguments must be present and both must be positive or negative. If minute_offset is specified without hour_offset, an error is raised. If other arguments are not valid, then an error is raised. If required arguments are null, then a null is returned. However, if the precision argument is null, then an error is raised.

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

· TIMEFROMPARTS : TIME returns a time value. If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. However, if the precision argument is null, then an error is raised.

 TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

· EOMONTH : EOMONTH Returns the last day of the month that contains the specified date.

EOMONTH ( start_date [, month_to_add ] )

New built-in Conversion functions in SQL Server 2012……..Part 2 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. 3 Out 14 explained below.

Conversion functions

· PARSE (Transact-SQL) : PARSE ( string_value AS data_type [ USING culture ] )

string_value : nvarchar(4000) value representing the formatted value to parse into the specified data type. string_value must be a valid representation of the requested data type, or PARSE raises an error.

data_type : Literal value representing the data type requested for the result.

Culture : Optional string that identifies the culture in which string_value is formatted.

Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value. PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Returns the result of the expression, translated to the requested data type.

· TRY_PARSE (Transact-SQL) : TRY_PARSE ( string_value AS data_type [ USING culture ] )

string_value : nvarchar(4000) value representing the formatted value to parse into the specified data type. string_value must be a valid representation of the requested data type, or TRY_PARSE returns null.

data_type : Literal representing the data type requested for the result.

Culture : Optional string that identifies the culture in which string_value is formatted.

Use TRY_PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value. TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Returns the result of the expression, translated to the requested data type, or null if the cast fails.

· TRY_CONVERT (Transact-SQL) : TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

data_type [ ( length ) ] : The data type into which to cast expression.

Expression : The value to be cast.

Style : Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error. TRY_CONVERT is a new keyword in compatibility level 110. This function is capable of being remote to servers that have a version of SQL Server 2012 and above. It will not be remote to servers that have a version below SQL Server 2012.

New built-in functions in SQL Server 2012……..Part 1 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. These functions are divided into 4 categories.

In this article I will be covering usage & functionality of these new 14 functions. I strongly feel, these functions are very useful for developer point of view.

These new functions are:

1) Conversion functions

· PARSE (Transact-SQL)

· TRY_PARSE (Transact-SQL)

· TRY_CONVERT (Transact-SQL)

2) Date and time functions

· DATEFROMPARTS (Transact-SQL)

· DATETIMEFROMPARTS (Transact-SQL)

· DATETIME2FROMPARTS (Transact-SQL)

· SMALLDATETIMEFROMPARTS (Transact-SQL)

· DATETIMEOFFSETFROMPARTS (Transact-SQL)

· TIMEFROMPARTS (Transact-SQL)

· EOMONTH (Transact-SQL)

3) Logical functions

· CHOOSE (Transact-SQL)

· IIF (Transact-SQL)

4) String functions

· CONCAT (Transact-SQL)

· FORMAT (Transact-SQL)

How to Restart an Interrupted SQL Server Database Restore ?


I am trying to restore database on SQL cluster & failover happen. Once the SQL Server came up on the other node all the databases came up, except for the database which was in restoration prior to the failover.

Now, We have 2 options one is to restore database again or second to start restore by WITH RESTART.

RESTORE DATABASE…WITH RESTART command is a very useful command which is available in SQL Server 2005 and higher versions. A Database Administrator can use this command to finish restoring an interrupted database restore operation.

Database Lamp is in restoring mode after unexpected failover.

After executing the RESTORE DATABASE…WITH RESTART command to successfully complete the database restore operation.

Below you can see that after running the RESTORE DATABASE…WITH RESTART command the database was successfully

restored allowing user connectivity.

How To Create Server Role In SQL Server 2012?


In previous versions of SQL Server, the user-defined role-based security are there in order to achieve access rights as per requirement. The user-defined role customized roles at the database level, not at the server level. This is because at the server level, administrators had to use fixed roles. Server roles were fixed and could not be customize as per different security requirements. This led to database administrators providing users to a higher roles ,such as the sysadmin role, because available server roles are not meeting up with business requirement.

SQL Server 2012, user-defined roles have been introduced at the server level also to increase flexibility, increase manageability and for better control & compliance follow-up of access rights as per requirement.

How to create a server role with SSMS:

1. In SQL Server Management Studio, use Object Explorer to connect to an instance of the SQL Server Database Engine.

2. In Object Explorer, expand the instance of SQL Server, and expand the Security folder.

3. Right-click the Server Roles folder, and select New Server Role.

4. On the General page of the New Server Role Wizard, do the following:

a. Specify the name of the new server role.

b. Select the owner for the new server role.

c. Choose the appropriate securables as they mention to the new server role.

d. When a securable is selected, apply explicit permission by selecting the checkbox for one of the following permissions: Grant, With Grant, or Deny.

5. On the Members page, add logins that represent individuals or groups to be added to one or more server roles.

6. Finally, on the Memberships page, because a user-defined server role can be a member of

another server role, specify the appropriate server roles that the new server Role will be a

member of.

SQL Server 2012 – Database Recovery Advisor


With the introduction of the Database Recovery Advisor, restoring databases to a point in time will be made much easier. SQL Server provides a variety of backup types & creating the right recovery sequence for any point in time can get tricky. To help make this process much more streamlined, SQL Server 2012 introduces a new Recovery Advisor to help DBA to create a more predictable and optimal restore sequence.

It has Capabilities include a visual timeline that presents the backup history of the database and the available points in time to which the user can restore the database. Although DBA can do all these task by scripts but using a GUI for restore & selecting recovery sequence is quite useful & handy.

Follow

Get every new post delivered to your Inbox.

Join 189 other followers