Skip the navigation

Microsoft raises the bar with SQL Server 2012

By Barry Nance
July 16, 2012 12:40 AM ET

Another convenient, impressive and practical new SQL Server 2012 feature is replication to a Read-only Secondary. By copying database changes to the read-only secondary in a way that assures the integrity of related database contents in the secondary database, SQL Server 2012 makes backing up an active, in-use database painless and quick ... you simply make periodic backup copies of the read-only secondary database, not the primary.

If the read-only secondary is on a separate server, you even avoid using database server CPU and memory during the backup process. Furthermore, read-only secondaries become excellent candidates as the basis for data analysis and reporting, even while the primary database is actively in use. We liked read-only secondaries a lot.

SQL Server 2012's new FileTable concept was somewhat less impressive, but only because we couldn't think of a good, practical use for it. FileTable associates an NTFS file system directory with a database table. Any file you put in the directory appears in the database, and SQL Server 2012 reflects in the database any changes you make to a file.

Backing up the database also backs up files in the associated directory. If you have ancillary data files that bear a critical relationship to the contents of a database and you want to back up the database plus the ancillary files as a consistent single unit, FileTable may be for you.

Contained Databases

Before SQL Server 2012, migrating a database meant much more than just copying database files. You also had to set up or at least synchronize database login user IDs, ensure that collation (i.e., the sort order to be used for each character set as well as the code page used to store non-Unicode character data) was configured the same for the two databases, verify compatibility levels, migrate scheduled jobs and do other tasks to manage database-related data not stored directly in the database files.

SQL Server 2012's Contained Databases feature makes database migration a bit easier by storing the collation setting and the database login user IDs within the database. You no longer have to synchronize database login IDs between the old server and the new one. However, you still have to worry about other database-related configuration steps, such as setting up scheduled jobs on the new server.

ColumnStore Indexes

SQL Server 2012's ColumnStore Index stores data for columns you designate and then joins those database columns to give you a read-only, column-based index into the data (traditional indexes are row-oriented, storing data for each row and then joining those rows to complete the index).

Microsoft claims ColumnStore Index speeds up data retrieval by a factor of 10. Our tests confirmed the performance gain, exhibiting at least 10x and sometimes much faster (12x, 15x and even 20x) data retrieval speeds.

Originally published on Click here to read the original story.
Reprinted with permission from Story copyright 2012 Network World, Inc. All rights reserved.
Our Commenting Policies
Consumerization of IT: Be in the know
consumer tech

Our new weekly Consumerization of IT newsletter covers a wide range of trends including BYOD, smartphones, tablets, MDM, cloud, social and what it all means for IT. Subscribe now and stay up to date!