- 18 Hot IT Certifications for 2014
- CIOs Opting for IT Contractors Over Hiring Full-Time Staff
- 12 Best Free iOS 7 Holiday Shopping Apps
- For CMOs Big Data Can Lead to Big Profits
Page 3 of 4
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.
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.
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.
The big drawback to ColumnStore Indexes is their read-only status, which makes them useful only for queries in data warehouses with huge databases. OLTP databases and ColumnStore Indexes are, by their nature and almost by definition, mutually exclusive.
Even in a data warehouse milieu, frequently loading new data into read-only tables can be quite a hassle. Microsoft describes a workaround for the read-only problem by having you switch out table partitions in your data warehouse tables. If you are desperate for better performance, the workaround might be acceptable. Alternatively, you might opt to use SQL Server 2012's read-only secondary feature to manage the database copies you use for analysis and reporting.