SQL Server 2008

What is Microsoft SQL Server 2008 R2

There are many facts about SQL Server that can just be put together in one article, but even in conceding of doing that, it would a torture for reading if you aren’t looking exactly for that type of article. That’s why in this article there will be mostly bear facts about SQL Server 2008 that you need to know.

So what is exactly SQL Server? SQL Server, in shortest term, is a database system. SQL Server 2008 is considered as a large and advanced server solution that is used by companies that use thousands of guest OS’s and large firms that really need that kind of performance and storage in their grip. That’s true, but that doesn’t mean that the small individuals are left behind, as they are using a ‘small’ amount of storage unlike huge companies. That’s why the most important thing about a SQL server is his greatness, but literally. As a part of Microsoft community it has become very popular making SQL Server one of the most mission critical applications on a network. The nature itself is very sensitive in every case possible as for it to be on top, top services are necessary, which means in this case high availability solutions such as database mirroring and failover clustering to assure constant data availability to users. One of the main characteristic of the SQL server is the Multiple Instances. Multiple Instances have been part of the SQL system for quite some time, and are no newness. SQL supports multiple installed on a single OS, without being in a risk of their possible ‘touching’. Basically they are meant to be totally isolated one from another as much as that is possible, but that not 100% possible.

The system files and user databases can’t get in touch between themselves. Some registry information and even a service or two, such as the Distributed Transaction Coordinator service, are shared among all instances of SQL Server. However, there are two specific services, MSSQLSERVER and SQLSERVERAGENT, are unique per instance. Logically the more instances there is on the OS, the more the CPU is going to be loaded as every instance has its own set of services that needs to be run. Such schematic has one major flaw in its system. In a case of crashing of the host OS, all the instances that are located there are going down with it. That also goes for a small glitch in the system, as one error can effect badly on the rest of the instances. But in nowadays communities giving all the instances equal privileges won’t end up good. Sure there could be more sysadmin’s that can access the SQL Server, but that doesn’t mean everybody should have those privileges. There is however another solution and that is installing each instance separately on every VM (virtual machine).

SQL Server advantages

SQL Server’s greatest advantage could be its greatest weakness if you don’t take right measures in protecting the sensitive database system. Keeping the data in a SQL Server database up and online all the time requires sophisticated high-availability solutions. There are a couple of technologies that are up and running all the time to prevent any loss of important data:

• Database snapshot: Read-only point-in-time virtual copy of a database
• Log shipping: Latent transaction log copy process between two copies of a database
• Database mirroring: Synchronous or asynchronous transaction commitment to two copies of a database
• Replication: Snapshot, transactional, merge, or peer-to-peer data replication between two databases (granular to row or column level depending on article partitioning)
• Failover clustering: Failover clustering via Server 2008 R2 Failover Cluster to protect against OS or SQL Server service failures

Probably the most used technology for preventing any loss of data in any case of corruption of a host instance is the Clustering System. With the introduction of Hyper-V clustering technologies, the schematic of clustering has changed. This type of clustering using the Hyper-V is more known as Host clustering. The characteristic that makes this technology so attractive is its high availability. If something goes awry with the primary physical node (whether due to the host OS or VM), the services are brought online on the secondary node. The only setback in this type of clustering is the disconnecting of all of the clients, during the migration of the files on the second node after which it will be automatically reconnected. Alternatively, clustering VMs inside the virtualization layer is called guest clustering. This type of clustering works by connecting VM between themselves and creating something like a network of physical servers. In this state two things are monitored the guest OS and the applications, but in the case of failure there is the possibility of only one failure of the guest OS.

High availability of the database

Like previously said high availability of the database is a crucial feature and such a thing is mostly accomplished through Database mirroring. SQL Server has within itself a service that uses another SQL Server as a back-up system for the original one. The two instances are connected through an instance-level TCP Endpoint restricted to forwarding traffic for the purpose of database mirror activity only. During the process of mirroring data from one instance to another the SQL Server goes to offline mode, shutting down the entire group of client’s. To be able to use a fault tolerance system, you have to have minimum two instances of SQL Server (one original, one back-up). But there are two ways of mirroring between the instances. Depending on what you want you decide which one is more suited for you. One of them is characterized by great downtime recovery speed, but not full data protection, while the other supplies full data protection, but the downtime depends on the connection between the VM’s.  The first case is with lower level of security where you create two SQL instances on a single physical device. This provides you with security in case of instance crashing, but in the second case the two instances of SQL are installed on different physical devices. This kind of fault-tolerance protection is probably more common in the community, but while the mirroring is being processed the connection between them will suffer heavy performance fall until it is finished.

With such high availability and lots of services to make your life easier it is no wonder that a lot of companies have started migrating to SQL Server with their classified information and mission-critical data. As a result of more and more companies migrating to their servers Microsoft decides to make their lives easier to with new products line. The SQL Server family of products also boasts an impressive line-up of Business Intelligence (BI) applications that can integrate heterogeneous data platforms, automate routine data management tasks, construct data warehouses, and report on a myriad of data sources.  SQL Server Integration Services (SSIS): Data extraction, transformation, and loading (ETL) solution; SQL Server Analysis Services (SSAS): Data warehouse and mining solution; SQL Server Reporting Services (SSRS): Data reporting solution. All of these services are enabled to run on any of the three versions of the SQL Server.  If you are costumed using Microsoft’s virtualization package and SQL Server then the Hyper-V and Microsoft SQL Server 2008 R2 is something which you mustn’t pass. Hyper-V is free included in the SQL Server package and buying it with this release gives you a lot of advantages from monitoring and health status information and all other application advantages that help you manage it.

Microsoft SQL Server 2008 with Hyper-V

When it CPU usage with the Microsoft SQL Server 2008 with Hyper-V I will mention only some basic features as it is pretty explained in the article available to see on the page ‘Windows Hyper-V’: SQL Server 2008 supports up to 4 CPU’s per Virtual Machine and up to 384 VMs per host. Hyper-V multi-processor support for SQL Server 2008 lets SQL Server take advantage of multiple CPUs in the VM. There is also the great advantage that Hyper-V possesses. It can simulate multiple processors that are very useful for the SQL Server in order of optimizing its performance. For this technology to work in its best a great experience and knowledge is required in order to configure the utilization of resources in the best way so that the servers that need more resources don’t ‘starve’ for more and the minor ones left with huge amounts of unused resources.

Even though CPU represents very important tool in the SQL Server probably the more important one would be RAM (Random Access Memory). Hyper-V is also in charge of distributing RAM to all of the Guest OS, as much as it was predicted for it to have. The hypervisor gives the amount of physical memory and virtual memory to the guest OS and offer virtual addresses to each application installed on the OS. There are three types of these address: System Physical Address (SPA): Physical RAM address space of physical server hosting Hyper-V; uses paging file on physical disk to store overages; Guest Physical Address (GPA): Allocated RAM address space allotted to a VM child partition; Guest Virtual Address (GVA): Memory space greater than GPA allocated by guest OS to installed applications; uses paging file on virtual disk to store overages.  Because of this design of memory, Hyper-V should page every virtual memory address twice, once for the System Physical Address and the other time for the Guest Physical Address. This could cause a great paging overflow written on the hard drive and could cause serious problems in system stability, especially in maintaining multiple VM’s in the same time. So this wouldn’t happen Server 2008 R2 Hyper-V introduces a new technology called Second Level Address Translation (SLAT), wherein the hypervisor adds a second layer of paging to the architectural paging table of compliant CPU hardware. This service allows great improvement with applications that require plenty of memory, giving those operations a huge boost.

Upgrading from SQL Server 2005 to SQL Server 2008

This article was mostly based on the Microsoft SQL Server 2008, and some parts have within themselves included some advantages that the R2 has to offer, but there some that still use the SQL Server 2005, so here are the advantages that you will get in case of update in chronological way:
Upgrading from SQL Server 2005to SQL Server 2008:

• Reduce purchasing requirements by optimizing hardware with the Resource Governor

The Resource Governor, introduced in SQL Server 2008, helps solve the problem of poor application performance on older hardware, especially at peak times. It does this by enabling you to give priority to selected applications and to limit the resources that other workload groups are allocated, ensuring your selected applications always offer acceptable performance levels without the need to spend money upgrading your severs.

• Cut storage requirements and speed up backups and with compression

The data compression technology introduced in SQL Server 2008 can reduce your application storage requirements by as much as two thirds by compressing your tables, hardware indexes, partitions and backups. It can also speed up the backup process by reducing the number of writes required to complete one.

• Consolidate your applications on to fewer systems

Using multi-database support you can consolidate databases with similar security and compatibility requirements into a single SQL Server instance, using the Resource Governor to ensure that each one runs in line with to your performance requirements. If you have databases with different security or compatibility requirements you can also cut costs using multi-instance support to consolidate them onto a single physical server.

• Reduce your hardware, power and licensing costs though virtualization

Using Microsoft's Hyper-V hypervisor, introduced in SQL Server 2008, it's easy to virtualize and run multiple SQL Server virtual machines on a single physical machine. That means you can purchase and maintain less hardware, reduce power and cooling costs, and free up valuable data center space. You can also make significant licensing cost savings only one Windows license and one SQL Server 2008 R2 license is required per physical processor running in each host server, regardless of the number of virtual machines that run in them.

Upgrading from SQL Server 2008 to SQL Server 2008 R2:

• Analyze streaming data in near real-time with StreamInsight

StreamInsight is a powerful platform you can use to develop complex event processing (CEP) applications within SQL Server 2008 R2 to process incoming data at high speed. It has been designed to handle large volumes of streaming data brought in from sources such as RFIDs and sensors and weblogs, enabling your applications to detect meaningful patterns, trends, exceptions, and opportunities in near real time.

• Improve the quality of your data and access a "single version of the truth" with Master Data Services

Master Data Services allow administrators to create a master data hub as a repository for your most important application data and to maintain it in a consistent, up-to -date state, ensuring that all your applications that need this data access a "single version of the truth." A web portal allows secure, role-based access to the master data so that authorized users can browse, edit, author, audit and approve it.

• Simplify application development, deployment, and management with Data-tier Applications

A data-tier application (DAC) contains all of the database and instance objects used by your application, providing a single unit for authoring, deploying, and managing your data-tier objects instead of having to manage them separately. Using a DAC allows you to integrate data-tier development more tightly with the development of your application code.

• Manage multiple servers more efficiently using Multi Server Administration

SQL Server Management Studio (SSMS) has always been useful for managing a single SQL Server, but new features in SQL Server 2008 R2 make it easy to set up and enrol server instances, turning SSMS into a powerful tool for managing multi-server environments. The new control point explorer provides a dashboard view of all the servers under management, and administrators can drill down for utilization views of server instances and applications to make management easier and more efficient.

• Enhance your organization's BI capabilities with improved SQL Server Reporting Service

SQL Server 2008 R2 Reporting Services is a complete, server-based platform which enables you to deliver relevant information whenever and wherever it is needed. It can draw data from local sources, or connect to SQL Azure to create reports from applications run on Microsoft's cloud platform. Users can save report parts to a Report Part Gallery, and other users can then re-use them to make up their own reports (if they have the necessary permissions.) The new Reporting Service supports maps and geospatial visualizations with integration to Bing maps and can also use SharePoint lists and PowerPivot models as data sources.

• Put powerful BI tools into the hands of all users without losing control of data or security

SQL Server 2008 R2's PowerPivot Add-in for Excel® allows users to create "self service" BI applications drawing from multiple data sources in Excel, and to share models and reports with colleagues through Microsoft Office SharePoint® 2010. An important benefit is that the IT department can maintain control over these models and reports to ensure that data is consistently managed and up-to-date, and that they are only accessed by authorized users.

To summary everything together, SQL Server 2008 R2 combined with Hyper-V gives you a great deal of applications that work in your favor, while providing you with very stable data storages and lots of new technologies that are being developed as we speak for new and even better versions yet to come. These two apps have certainly made great impression on the IT world and Microsoft will make sure that they stay there as much as they can regardless if it is being used by a small individual or a huge company.

TAGS: