Academiclub

Q1. Which are new data types introduced in SQL SERVER 2008?

Q2. What is a Filegroup? What is their purpose?

Q3. What are the two mechanisms to authenticate users in SQL server 2008? Write their advantages and disadvantages.

Q4. What is a FileStream Data? What are its advantages?

Q5. What is Policy Management? Briefly explain the four evaluation modes.

PART-B

Q1. What are the basic functions for master, msdb, model, tempdb and resource databases?

Q2. What is SQL Profiler, Server Agent and Service Broker?

Q3. What is Collation? Discuss briefly about the various Collation settings in SQL Server 2008.

Q4. Discuss the various Features of various SQL Server Editions?

Q5. What is stored procedure? What are the key benefits of using Stored Procedures?

PART-C

Q1. What is a role of SQL Server Agent service? Briefly explain its component?

Q2. Compare Full recovery model with Bulk-Logged recovery Model?

Q3. What is a role of SQL Profiler and Log File viewer? Briefly explain them

Q4. What do you understand by High Availability options? Compare all the three major options?

Q5. What is Data Collector? Briefly explain its components?

CASE STUDY-1

SQL Server 2008 Runs SAP ERP Database to Support Microsoft Worldwide

Situation

Microsoft has relied on SAP ERP software to run its financial operations since 1996 when it first deployed the solution on Microsoft® SQL Server® 6.5. Since then Microsoft's SAP ERP deployment, which now has a 6.5-terabyte backend database, has grown to join the top 5 percent of the largest SAP ERP deployments worldwide.

With 92,000 employees, operations in 89 countries, and 2007 revenues exceeding U.S.$51 billion, Microsoft has plenty of financial and operational data to track. The company's SAP ERP system handles Microsoft's treasury, worldwide sales, worldwide finance, worldwide human resources, worldwide operations, material management, U.S. payroll, and other mission-critical functions.

The company was happy with how its SAP ERP deployment was running on Microsoft SQL Server 2005 Enterprise Edition and the Windows Server® 2003 Enterprise Edition operating system. SQL Server 2005 had provided excellent performance and rock-solid stability for the company's worldwide SAP-based operations.

SAP ERP performance at Microsoft includes:

• Over 1,300,000 dialog steps per business day

• 19 million transactions per month

• An average user response time of less than 0.7 seconds

As the SAP team members looked at the beta edition of SQL Server 2008 they saw features that they wanted to benefit from immediately, including database and backup compression technology and improvements in SQL Server Database Mirroring technologies.

But with so many of the company's mission-critical worldwide systems directly or indirectly dependent upon SAP ERP, the upgrade would need to be seamless, and performance and stability would need to be at least as good as the group had enjoyed with SQL Server 2005. In short, SQL Server 2008 needed to be enterprise grade— even in beta release.

"SAP ERP serves as the financial backbone of Microsoft," says Hans Reutter, Principal OE System Manager at Microsoft. "This means that we don't upgrade our database and other infrastructure without having complete confidence that it is enterprise ready."

In February 2008, Microsoft upgraded its SAP ERP environment to the beta edition of SQL Server 2008 Enterprise running on the Wndows Server 2003 Enterprise Edition operating system. The SAP deployment has a three-tier architecture that includes:

• Presentation Tier. The presentation tier includes a fat client, the SAP graphical user interface, which is used by some 2,000 heavy users of the SAP environment. The rest of Microsoft's worldwide employees access SAP through a Web browser, with access handled by 4 load-balanced servers running the Windows Server 2003 Enterprise Edition operating system and Internet Information Services (IIS).

• Application Tier. The application tier includes 12 load-balanced SAP application servers running Windows Server 2003 Enterprise Edition.

• Database Tier. The 6.5-terabyte SAP ERP database is hosted on the beta edition of SQL Server 2008 Enterprise, running on Windows Server 2003 Enterprise Edition. The database grows by about 200 GB a month. The full database is hosted on a single HP DL585-G5 server with 4-socket quad-core processors and 68 GB of RAM. It is connected using fiber optics to an EMC CX3-80 SAN disk storage array. To help ensure high availability, the database is setup with SQL Server 2008 synchronous database mirroring with automatic failover enabled. The second database server and storage is an exact copy of the primary server and storage to allow a failover without any performance impact.

The SAP deployment takes advantage of features new for SQL Server 2008, including:

• Data Compression.

• Backup Compression.

• Enhanced Auditing.

• Resource Governor.

• Enhanced Database Mirroring.

• Enhanced Autogrowth and Lock Management.

Benefits

Upgrading to SQL Server 2008 Enterprise gave Microsoft the data compression it needed to support better resource utilization and to reduce the size of backup storage required for its 6.5-terabyte SAP ERP database. The Microsoft SAP group plans to use the enhanced auditing feature of SQL Server 2008 to capture access information required for compliance reasons, while enjoying improved high availability with Database Mirroring, and reduced downtime needs with Online Indexing.

Resource Governor will enable the team to define the amounts of resources that individual workloads are allowed to use during execution. "We could use Resource Governor to regulate resources available for backups," says Bregler. "We may want to restrict backups to 10 percent of resources. Or if we wanted to increase the rate of backups, we could increase available resources. Resource Governor is a powerful tool for ensuring that key workloads always have the resources required to get the job done."

Results

Microsoft gained the data compression, backup compression, integrity of change tracking, and other benefits it sought by upgrading its 6.5-terabyte SAP ERP database to SQL Server 2008 Enterprise running on Wndows Server 2003 Enterprise Edition. The combination of Database Mirroring and Online Indexing has reduced the need for scheduled downtime, helping the group to enjoy 99.99 percent uptime for its platform.

SQL Server 2008 Runs SAP ERP Database to Support Microsoft Worldwide

Questions:

(a) Why Microsoft decided to move on SQL server 2008?

(b) Briefly explain any new feature of SQL server 2008 used in SAP deployment?

Secar Bohemia operates automobile tracking systems, systems for monitoring vehicles, people and objects, and fleet management services including electronic drive logging. These services are provided in collaboration with Telefonica 02 Czech Republic and branded "02 Car Control".

The CarControl system currently (beginning of 2008) includes about 3 billion records amounting up to 1 TB, and it is being used by tens of thousands users worldwide, from China to the Russian Federation, in ten language versions.

It is designed to simplify car fleet operations for companies, with special focus on decreasing costs and providing logistics functionality. The system allows up to 20% cost savings on company fleet operations. Key system features include monitoring vehicle movement data and facilitating analysis of that information in the form of output reports.

The CarControl system is based on the .NET technology and uses Microsoft SQL Server as the database back-end.

The system is distributed across a number of servers with specific assigned roles. Generally, servers in the system comprise four categories:

1. Routers - communication servers, which pre-process incoming data, identify it, classify specific instances, and also manage the units onboard the vehicles.

2. Application Servers - host the end-user web-/lnternet-based applications that users interact with.

3. Database servers - store data and provide basic application logic on the database level (such as generating standard reports).

4. Analytics servers - these are special-purpose servers that perform complex analytic and database operations, such as evaluation of data from the traffic and logistics features. In addition, these servers are used to generate more sophisticated output reports.

Secar Bohemia had great expectations for the development and new features available in Microsoft SQL Server 2008 for the purposes of improving CarControl. "We realize that in order to succeed in a fiercely competitive environment, we have to constantly evolve and enhance our system and keep offering new features to users, to improve the efficiency of car fleet management. Opportunities for further development are exactly what the new Microsoft SQL Server 2008 offers," said Petr Vacek, Deputy Chairman of Secar Bohemia. In 2007, Secar Bohemia increasingly received requests for advanced analysis of input information, and more importantly, for visual presentation of output data such as charts. In addition, the system required optimizations in terms of performance as well as storage size, due to the massively increasing number of customers in the system. Last but not least, work had commenced on the development of a module to monitor the traffic situation.

Secar Bohemia in partnership with KAKTUS Software, which is responsible for the development of CarControl and is a Microsoft Gold Certified Partner, decided to evaluate the pre-release version of Microsoft SQL Server 2008.

This was motivated by the fact that Secar Bohemia was satisfied with the functionality of Microsoft SQL Server 2005 servers that had acted as the core of the Car Control system. Also considered were the low costs of upgrading to the new SQL Server, ease of migration and overall simplicity of moving to the new system. Microsoft SQL Server 2008 was the undisputable winner in all those considerations.

There are several instances of CarControl in operation, three of them in production and one for development and testing. It stands to reason that the initial deployment was completed on the testing version. This involved updating the central database server and the server that processes analytic workloads. Thanks to new database compression and management features, the volume of data decreased substantially, as did the requirements for available disk storage.

The most important features evaluated were deployed on the analytics server. This is where the testing and successful deployment of the following new features was completed: processing of spatial, geometrical and geographical data for the purposes of implementing traffic monitoring tasks, in conjunction with an application developed using SilverLight, also a new technology. Furthermore, a number of advanced analytics were developed and made available to users as reports. The developers implementing the new reports gave high praise to the new Designer tool and visual data presentation capabilities, especially as charts. The test mode was terminated in March 2008. There were no major issues identified in testing. Secar Bohemia is planning to deploy Microsoft SQL Server 2008 to the remaining instances by the end of 2008.

Testing has confirmed that Microsoft SQL Server 2008 is stable and robust, and it provides a number of new features that can be used to expand CarControl in future.

The new SQL Server 2008 enables storing and processing of a wide range of new data types. These include new date and time types. This simplifies and accelerates processing this information. It is also possible to store larger binary data directly to disk. The HierarchylD data type lets developers structure interdependent information in the form of a tree. Last but not least, there are new data types that handle spatial data.

This is an area where SQL Server 2008 provides a large number of new features. Among the first to be used in CarControl were the output report generating capabilities. For instance, SQL Server enables creating reports in the new Designer tool, closely linking the resulting reports with Microsoft Office products. The key features of the new server that CarControl has put to use include the capability to present data graphically as charts, which can now have a much more attractive appearance compared to the previous version.

"Reliability and rich features are a matter of course in various systems. However, we acknowledge that emotional approach to products is also very significant. That is why systems developed by us offer top-class graphic design and user experience. To achieve this in the CarControl project, Microsoft has provided a tool that enables us to do just that," said David Kalous, Director of KAKTUS Software.

The new Management Studio significantly decreases the complexity of SQL server administration. SQL Server now includes a new policy framework, which enables setting and enforcing the same configuration settings and policies across multiple servers. New data types and data compression capabilities simplify data space management.

Windows Server 2008, SQL Server 2008, and Visual Studio 2008 provide a secure and trusted platform for creating and running your most demanding applications. Combined, the products provide a solid foundation for next-generation web applications, broad support for virtualization technology, and access to relevant information. Advanced security technology, developer support for the latest platforms, improved management and web tools, flexible virtualization solutions, and access to relevant information from throughout your organization enable a broad array of technology solutions.

(b) Briefly explain the benefits reaped by this company after moving to a new system on SQL server 2008.