Backup compression in SQL Server 2008 (R2)

What is backup compression?
In the previous versions of SQL Server you can only make an uncompressed backup to disk. The size of this backup is almost the same as the size of the database. To decrease the size of the backup file, you can use compression software like Winzip, WinRar etc. This requires additional CPU power and disk space. As of SQL 2008 a new backup option is introduced called 'Compression' which directly create a compressed backup.

Why using backup compression?

I did some tests with Exact Globe database which resulted in next graph. The uncompressed backup results are set to 100 to compare with the uncompressed results.

• Decrease in backup size up to 65 %. This depends of course on the content of the data.
• CPU usage will increase during the backup process. The more data can be compressed the more increase in CPU usage.
• Faster backup speed (25%) because it requires less disk I/O.

In which SQL editions is this available?Unfortunately backup compression is not available in all editions of SQL 2008 (R2).
In SQL 2008 it is only available in the Enterprise edition.
In SQL 2008 R2 it is available in the Standard and Enterprise edition. 

How to use backup compression?

• Server level: Backup compression is off by default on server level. If you enable it on server level all your backups will be compressed by default. Use next SQL script to enable by default backup compression on server level.

USE master;
GO
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;

• Database level: If you do not want to enable this option on server level you can create a backup by making use of the compression option in the backup. In the Object Explorer of SSMS Select Database, Properties, Tasks, Backup, Options, Compression.

Or using a script. In next example for database 'Mydatabase' :  

[MyDatabase] DISK=N'D:\Backup\Mydatabase.bak'
WITH NOFORMAT, INIT, NAME = N'Mydatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

More information about Backup Compression can be found here.
Enjoy using SQL Server Backup Compression.

Is Microsoft moving to the Enterprise market and how important is the enterpreneur for them?

My company Exact has next mission:

Serving entrepreneurial businesses is at the heart of what we do. We serve local and international companies in more than 125 countries and offer our solutions in more than 40 languages. Our solutions provide our customers the freedom to successfully address challenges and opportunities, creating value for their customers and ultimately for themselves.

With this mission in mind, I look to all new products of Microsoft. Last week SQL Server 2008 R2 is released to manufacturing. I'm indeed enthousiastic about this product. Some new features can be very useful for our customers like data compression and the powerpivots. However our customers are enterpreneurs which do not have a lot of IT knowledge. If you look to PowerPivot, this products is too complex for them to install and configure. Please have a look to the one page architecture. It explains everything:

A readable PDF version of the Power Pivot Architecture can be downloaded here.

If we look to data compression it is only available as of the Enterprise edition. This feature can be very useful for them to save diskspace and improve performance. However 95% of our customers is using the Express, Workgroup or Standard edition of SQL server. The price difference between these versions with the Enterprise edition is too big for them.

In SQL Server 2008 R2 2 new editions are introduced however all for the high end market: SQL Server 2008 R2 Datacenter edition and SQL Server 2008 R2 Parallel Data Warehouse edition.

I hope that this is not a new trend that Microsoft is moving to the high end market. I hope they will not forget the entrepeneur. Let's see what the future will bring.

Photo credit: selmerv

Exact moves to new Corporate Headquarters.

This weekend my company is moving to a really nice new building. The new building is located in Delft’s Technopolis Innovation Park, an area that aims to attract companies with a high R&D component. “Technopolis”, as the park is referred to, is one of Europe’s leading knowledge centers, and hopes to become the IT center of the Netherlands. The new building’s proximity to the TU Delft, with which we have a strategic partnership, will enhance collaboration with the university.


The new Exact Headquarters inspires leadership and innovation; it will be a place where knowledge exchange and teamwork thrive, and where collaboration between business and education flourish.

For the nearly 500 employees based in Delft, the new building means a flexible, open work environment, in terms of both time and space. Flexible working hours, private meeting areas, open working spaces, and at the same time, a welcoming, informal gathering place to meet with colleagues, business partners, customers and visitors.

I'm really looking forward to the first working day on Monday 26 april 2010 in this new inspiring building.

SQL Server 2008 R2 Released to Manufacturing. (RTM)

Today 21 april 2010, the SQL Server team announced on their SQL Server team blog that SQL 2008 R2 is released to manufacturing. Customers can expect availability in the next few weeks through Microsoft’s distribution channels. For more information, visit www.sqlserverlaunch.com. SQL Server 2008 R2 will be available to TechNet and MSDN subscribers on May 3 and available for download worldwide on May 13. For more information visit The Official Microsoft Blog. I'm looking forward to these RTM bits so we can start testing our commercial products on these RTM bits.

Free ebook: Introducing Microsoft SQL 2008 R2

Microsoft Press has made a free ebook available for everybody who is interested in SQL Server 2008 R2. It is an introduction in all new features of SQL 2008 R2.  The book contains 10 chapters and 216 pages:


Part I Database Administration

Chapter 1 SQL Server 2008 R2 Editions and Enhancements 3
Chapter 2 Multi-Server Administration 21
Chapter 3 Data-Tier Applications 41
Chapter 4 High Availability and Virtualization Enhancements 63
Chapter 5 Consolidation and Monitoring 85


Part II Business Intelligence Development

Chapter 6 Scalable Data Warehousing 109
Chapter 7 Master Data Services 125
Chapter 8 Complex Event Processing with StreamInsight 145
Chapter 9 Reporting Services Enhancements 165
Chapter 10 Self-Service Analysis with PowerPivot 189

You can download the ebook in XPS format and in PDF format. This is a good initiative of Microsoft Press. I think all Microsoft Press books should come free available as ebooks. Let see what will happen.
Enjoy reading the topics you like.

My favorite Top 10 features of SQL 2008 R2

Photo credit: Sam_Churchill

In May 2010 the new version of SQL Server will be launched named: SQL 2008 R2.  In this blog I will list my favorite 8 features.

1. Report Builder 3.0. Report Builder 3.0 is a report authoring tool that you can use to design and publish your own reports. It's the successor of Report Builder 2.0 and it's support all new SQL 2008 R2 reporting services features. When you design a report, you specify where to get the data, which data to get, and how to display the data. When you run the report, the report processor takes all the information you have specified, retrieves the data and combines it with the report layout to generate each page as you view it. You can preview your reports in Report Builder, or you can publish your report to a report server where others can run it.
2. Reporting server support for Databars, Sparklines, Indicators and Maps. These visualization features enables a user to build nice dashboard.
   
   
   These visualization features enables you to build nice dashboards which can be used in web applications which have integrated SQL Server Reporting Services (SSRS)  like Exact Synergy Enterprise.
3. Compressed backup support in SQL 2008 R2 Standard Edition. Backup compression is introduced in SQL 2008 Enterprise edition. In SQL 2008 R2 it is also available in the Standard edition. 
4. Unicode compression. As more and more businesses developing global customer base, applications are being developed/modified to use unicode based data types such as NCHAR and NVARCHAR. SQL Server uses UCS-2 encoding scheme that takes two bytes of storage regardless of the locale. For example, in ASCII character set when stored as NCHAR, each character only needs 1 byte of storage but it is stored using 2 bytes with the significant byte being 0. In fact most European languages need only 1 byte of storage. When an application is either converted or written to user unicode based data types, it can, depending on the size of strings, increase the storage requirements significantly. Unfortunatly Unicode compression is not supported for NVARCHAR (MAX) type, including in-row values or for NTEXT. This is something Microsoft will consider in the future release. Unicode compression is automatically included in Page or Row compression. The compression is handled by the SQL server engine so no application changes are needed to use it.
5. Power Pivot for Excel. PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Excel. It’s the user-friendly way to perform data analysis using PivotTable and, PivotChart views, and slicers
6. Master Data Services. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time. This is the first step in centrally manage master data. The challenge in your organization will always be:  Who is allowed to change what master data. The bigger your company is, the more complex it will be. 
7. SQL Server Utility and Multi-Server Dashboards. SQL Server utility and multi-server management will help organizations proactively manage database environments efficiently at scale through centralized visibility into resource utilization and streamlined consolidation and upgrade initiatives across the application lifecycle.
8. SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse. These 2 new premium editions are introduced to meet the needs of large scale datacenters and data warehouses.

What are your favorite features to complete this TOP 10 list?
Enjoy using SQL 2008 R2