Dmitri GropenSQL Server 2012 is the first SQL Server product that can be installed on Windows Server Core. Windows Server 8 is also the first OS which you can switch between Server Core and Server Full (with GUI) implementations. Although SQL Server 2012 can be installed on Windows Server 8 Core directly from the command line or configuration file, the easier way would be installing it on Windows Server 8 Full with GUI and then switching the OS to Windows Core.

Start the Windows Server 8 setup:

Install Window Server 8 with GUI by choosing this option in the setup wizard

Install .NET Framework 3.5 Feature. In Server Manager, click on Add roles and features, and follow the wizard. Make sure the OS has access to the Internet as it will need it to install this feature. Please note that .NET Framework 4.5 is already installed on the server by default.

You can now go ahead and install all SQL Server 2012 features on Windows Server 8 the same way you would do so on any other Windows OS. After the installation, you can do the necessary configurations graphically.

After you have configured and tested your SQL Server instance, you can go ahead and switch the OS to Windows Server Core. Go to your Server Manager and select Remove Roles and Features from Manage menu. Note that the Server Manager in Windows 8 has separate interfaces for removing and adding server roles and you cannot do both at the same time as you were able to do in the previous versions of Windows.

In the Remove Roles and Feature Wizard, clear Graphical Management Tools and Infrastructures and Server Graphical Shell check boxes, and click Next.

After the server is restarted, you will see the command prompt only. Type sconfig in the command prompt and hit Enter and you will be presented with the following screen:

You can switch back to OS with GUI by selecting option 12 “Restore Graphical User Interface (GUI).” Your SQL Server instance is now running on Server Core and you can connect to it remotely with Management Studio.

Tagged with:  

SQL Server 2012 Certifications

On April 10, 2012, in Technical, by Dmitri Gropen

Dmitri GropenThe new SQL Server 2012 certifications are coming up soon and shall be available in June 2012. Instead of three professional-level certifications that have been available before, only two will be offered: Data Platform certification and the Business Intelligence certification.

Each of those new professional certification tracks will include five exams. In addition, those professional-level certifications will require recertification every three years. Recertification requirements will be limited to one or two exams.

Upgrading from SQL Server 2008 to 2012 professional certification will now require passing three exams. The details on upgrading and recertification examination are not available yet. The following are new SQL Server 2012 certificates:

Common Certifications for Both Tracks

  • Exam 70-461: Querying Microsoft SQL Server 2012
  • Exam 70-462: Administering Microsoft SQL Server 2012 Databases
  • Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

Data Platform

  • Exam 70-464: Developing Microsoft SQL Server 2012 Databases
  • Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012

Business Intelligence

  • Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
  • Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012
Tagged with:  

Partially Contained Databases in SQL Server 2012

On April 3, 2012, in How-to, Technical, by Dmitri Gropen

Dmitri GropenA database is called contained when it is isolated and independent from the instance of SQL Server that hosts it and from the objects stored in system databases of that instance. The databases that are dependent on the SQL Server instance objects, such as SQL Server logins mapped to database users, are called non-contained. All databases that are hosted on SQL Server instances prior to SQL Server 2012 are non-contained.

SQL Server 2012 introduces the partially-contained databases where the dependency on SQL Server instance is significantly reduced. One of the most important dependencies that can be bypassed in partially-contained SQL Server 2012 databases is the databases dependency on the SQL Server logins mapped to database users. You can create a database user with password in a partially-contained database and then connect directly to that database. No SQL Server login principal is needed. You will need to specify the initial catalog (name of the database) you are connecting to.  SQL Server will verify that this database exists, and then will let the database handle the authentication.

To be able to use partially contained databases, you will need to enable them at the instance level. In Object Explorer, right-click the server name, and then click Properties. In the Server Properties go to Advanced settings and set Enable Contained Databases to True.

Then you will need to set the Containment Type to Partial in the Database Properties of your database.

You can also set the containment type option to partial during the creation of your database. The system databases such as model database cannot be converted to partially contained databases.

The next step is to create user(s) with password(s) inside your partially contained database(s). In the Object Explorer of your Management Studio go to Databases >> YourDatabase >> Security, right-click on Users folder and select Properties. In the Database User properties go to General and select SQL user with password in the User type drop-down menu. Then enter User name and Password for your new user.

On the Membership page of your Database User option select the fixed database roles that you want your new contained database use to be part of.

Once the contained database user is created, you can connect to that database directly. To do so, you will need to specify the Initial Catalog, i.e. the database to which you are connecting with this user. When connecting to a contained database with Management Studio, in the Connect to Server dialog box, enter contained database user and password, and hit Options button.

In the Connect to database box type in the name of your contained database which you are connecting to.

If no initial catalog is specified, connection to a contained database will not be made, but the authentications will be passed to SQL Server instance instead. If the SQL Server login with the same name exists, connection will be made to the default database of that login. Contained database can have both SQL user with password and SQL user with login, therefore you can establish the connections to your database either directly with contained database user with password or through SQL Server login principal. Below is the approximate algorithm of the authentication process.

When you connect to a contained database with contained db user, you will see this database only. No SQL Server instace objects or other user databases will be displayed. Contained database user can still access mater and tempdb system databases as a guest, as well as other user databases on the same instance where the guest user was enabled.

Contained databases do not support change data capture, change tracking and replication,

Temporary tables in contained database are collated with the collation of that database instead of the collation of tempdb as in non-contained databases.

Contained databases are important in high availability solutions like AlwaysOn Availability Group. When databases are failed over another instance, it is easier for applications and users to re-connect to those databases as their connections do not depend on the SQL Server logins in a new instance.

Dmitri GropenSQL Server 2012 introduces a new beyond-relational platform called Semantic Search.  Semantic Search extends Full-Text Search capabilities by allowing querying not only for words in the text source, but also for meaning of that text.

Semantic Search builds its indexes using the indexes created by Full-Text Search. Semantic Search is also called Statistical Semantic Search because its intelligence is based on statistics.

Before you can use Statistical Semantic Search in SQL Server 2012, you will need to download, install, attach, and register Semantic Language Statistics Database.  This database contains the statistical language models that Semantic Search depends on.

Please follow this link to download msi package that contains the database.

Run the installer, and after installation is complete, find semanticsDB.mdf file in Microsoft Semantic Language Database folder under your Program Files directory. Attach this database file by right-clicking Databases folder in Object Explorer of your Management Studio, and selecting Attach button. Then click Add button and locate the database .mdf file to attach. Once the database is attached, register it by executing the following command:

USE master
GO
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb';
GO

Verify that the database is installed by running the following query:

SELECT * FROM sys.fulltext_semantic_language_statistics_database;
GO

To list the languages supported for semantic search, run this query:

SELECT * FROM sys.fulltext_semantic_languages;
GO

You can create Semantic Search indexes on a table by running T-SQL commands, Full-Text Indexing Wizard, or in Full-Text Catalog properties.

To create Semantic Search indexes in Full-Text Catalog properties, in the Object Explorer of your Management Studio navigate to YourDatabase / Storage / Full Text Catalogs.  Right click on catalog name and select Properties.

Select the Tables/Views from the left list view. Add the tables for which you want to create indexes to the Table/View objects assigned to the catalog list. Select the columns under Available Columns list. Finally, select the check boxes under Statistical Semantic list next to the columns for which you want to build Semantic Search indexes.


Full-Text Catalog properties
Tagged with:  
iBlog by PageLines