In this tutorial you will learn about SQL Server 2005 Architecture, What’s New in SQL Server 2005—Enhancements for Data Base Administrators. indexing capabilities, peer to peer model of replication, Table and index partitioning, snapshot isolation, Replication monitor tool, security model, Encryption capabilities, Secure computing, A new application framework, SQL Server Express Manager (XM), Business Intelligence in SQL Server 2005, Integration services, Analysis Services, Data mining, Reporting services, Windows Server System Common Engineering Roadmap.
The innumerable data challenges faced by modern day organizations have leveraged the need for faster and more data driven decisions. The drive is to increase productivity, flexibility of human resources, to reduce overall investments in technology while scaling the infrastructure to meet the growing demand for information that enable informed mission critical decisions.
The release of SQL Server 2000 was one of the corner stones of Microsoft’s strategy for the back office. Its integration with the .NET family of server applications has gone a long way in establishing SQL server as one of the most robust servers for enterprise database management.
MSSQL Server 2005 is truly the next generation data management and analysis solution that is built for scalability, availability, analysis and security of data. The increasing ease with which database applications can be built has reduced the complexities of deploying and managing database applications. Data can now be shared across platforms, applications and devices making it possible to network internal and external systems seamlessly. Performance, availability, scalability and security are now available for lower costs. It is now a secure, reliable and productive platform for enterprise data and business intelligence tools.
SQL Server 2005 has a number of tools to help the Database Administrator and the Developer. The relational database engine has been improved to give better performance and support for both structured and unstructured (XML) data. The Replication services include services for distributed or mobile data processing applications. It provides for high systems availability, scalable concurrency with secondary data stores, enterprise reporting solutions and integration with heterogeneous systems such as Oracle databases. The deployment of scalable, personalized, timely information updates through web based applications has been made possible with the advanced Notification capabilities of the SQL Server 2005. The extraction, transformation and load process has been further enhanced and online analytical processing render rapid, sophisticated analysis of large and complex data sets using multidimensional storage. The Reporting services features have been honed up to create comprehensive solutions for managing, creating and delivering traditional and paper oriented reports or interactive, web based reports. Management tools for database management and tuning have been fine tuned to integrate with Microsoft operations manager and Microsoft System Management Server. The data access protocols reduce the time taken for integrating data in the SQL server with existing systems. A number of development tools have been provided and integrated with Microsoft Visual Studio to provide an end to end application development capability.
With SQL Server 2005 customers will now be able to leverage data assets to get more value from their data by using the reporting, analysis and data mining functionalities embedded in the software. The Business Intelligence capabilities are integrated to the Microsoft Office System, to enable transmission of mission critical business information across the organization. The complexity of developing, deploying and managing line of business and analytical applications has been greatly reduced by use of a flexible development environment and automated tools for database management. Finally, the cost of ownership has been reduced by a focus on ease of use and integrated approach.
What’s New in SQL Server 2005—Enhancements for Data Base Administrators.
SQL Server 2005 has a single management console that enables data base Administrators monitor, manage and tune all databases and services. The SQL Management Object(SMO) is an extensible management infrastructure can be easily programmed. It exposes all the management functionalities of the SQL Server and is implemented as a Microsoft .NET Framework assembly. The primary purpose of the SMO is to automate administrative tasks such as retrieving configuration settings, creating new databases, applying T-SQL scripts, Creating SQL Server Agent jobs and so on. The users can customize or extend the management environment and build additional tools and functions to extend the capabilities that come packaged in the box. It is in short more reliable, scalable than Distributed Management Objects(DMO).
The SQL Server Management Studio is a one point access to a number of services – the relational database, the Integration services, Analysis services, Reporting services, Notification Services and SQL Mobile. Using this interface DBAs can author or execute a query, view server objects, manage an object, monitor system activity or even seek online help. As it is integrated with source control, scheduling of SQL Server Agent jobs also becomes possible. Daily maintenance and operation tasks can be monitored.
Administrators can now proactively monitor and tune the server using the Dynamic Management Views (DMVs). There are more than 70 new measures of internal database performance and resource usage.
One of the major concerns of the database administrator is to ensure continuous availability of data. Database mirroring, failover clustering, snapshots or fast recovery would be areas he would be concerned with. SQL Server 2005 allows continuous streaming of the transaction log from a source server to a destination server which takes over seamlessly in the case of failure of the primary server. Support for server clustering has been extended to Analysis services, Notification Services and SQL Server replication and the number of nodes has also been increased to eight. Instant, read only views of the database can be created using snapshots. These provide a stable view without the time or storage overhead normally required in these instances. The snapshot pages are added automatically as and when the pages are modified. Hence quick recovery becomes possible. This ability is further enhanced with the fast recovery option which enables users connect with a recovering database after a transaction has been rolled forward.
Running server connections can be accessed using the dedicated administrator connection even when the server refuses to respond. As a result diagnostic functions or T-SQL statements can be executed to troubleshoot problems on a server. The sysadmin fixed server role is activated by the members and can be accessed using the SQLCMD command prompt utility remotely or locally.
The indexing capabilities of the SQL Server 2005 have been greatly enhanced. Indexes can be created, rebuilt or dropped online without disturbing existing indexes. This online indexing capability allows parallel processing, concurrent modifications to the table in the database or clustered index data or any other associated indexes. Additionally the online restore option improves the availability of data even when restore operations are being performed.
The peer to peer model of replication enables synchronization of transactions with an identical peer database. This further improves availability.
Enhancements that ensure scalability include table partitioning, snapshot isolation, and 64 bit support. This improves query performance.
Table and index partitioning eases the management of large databases by dividing the whole into manageable chunks. The concept is not new to the SQL Server, but the partitioning of tables horizontally across file groups in the database is new. The partitioning can be made for gigabytes and terabytes and more.
The snapshot isolation feature allows users access the last row that was committed by providing a transactional and consistent view of the database. It makes for increased data availability for read only applications; it allows non blocking read only operations in OLTP environment; it automatically detects conflicts in write transactions and makes for simplified migration of applications from Oracle to SQL Server.
The Replication monitor tool defines a new standard for managing complex data replication operations. Its interface is intuitive and has a number of data metrics that are useful.
The new SQL Server 2005 is optimized for the Intel Itanium processor and takes advantage of the advanced memory capabilities for essential resources such as buffer pools, caches and sort heaps. This reduces the need to perform multiple I/O operations and makes for greater processing capacity without the disadvantage of I/O latency. The support for 32 bid applications is retained while 64 bit capabilities have been introduced to make the migration smooth and efficient.
The security model of the database platform now provides more precise and flexible control for ensuring security of data. It enforces passwords for authentication, provides granularity in terms of specifying permissions in the authorization space and separates owners and schemas for the manager.
The Encryption capabilities of the database have been integrated with the management infrastructure for centralization of security assurance and server policy.
Secure computing measures have been put in place to enable deployment of a secure environment. Confidentiality, integrity and availability of data and systems is the primary focus at every stage of the software life cycle—from design to delivery and maintenance.
A new application framework with Service Broker, Notification Services, Server Mobile and Server Express has been introduced. The Service Broker is a distributed application that provides reliable asynchronous messaging at the database to data base level. Notification services helps in development and deployment of applications that generate and send personalized notifications to a wide variety of devices based on preferences specified by the application user. SQL Server Mobile edition enables the creation of a mobile edition database on the desktop or device directly from SQL Server Management Studio. The schema can be manipulated locally or remotely.
SQL Server Express Manager (XM) is a free Query Editor tool that is available for download and allows for easy database management and query analysis capabilities.
Business Intelligence in SQL Server 2005 is scalable, comprehensive and comes with a number of reporting capabilities. Both basic and innovative kinds of analytical applications can be built from end to end.
The Integration services are a redesigned enterprise ETL platform that enables users integrate and analyze data from multiple heterogeneous sources. Significantly, SQL Server 2005 goes beyond traditional services and supports Web services and XML and out of the box services through SSIS to bring analytics to the data without persisting data, Data mining and text mining in data flow for data quality and data cleansing.
Analysis Services provides a unified and integrated view of the business data by using the Unified Dimensional Model which is mapped to a host of heterogeneous back end data sources. User friendly descriptions and navigation hierarchies make it a pleasure to use.
The Data mining and Intelligence technology is designed to build complex analytical models and integrate such models to the business operations. The rich set of tools, API’s and algorithms provides customized data driven solutions to a broad range of business data mining requirements.
Reporting services is a server based BI platform managed via Web Services. Reports can be delivered in multiple formats interactively. Relational and OLAP Reports comes with an inbuilt query editors—SQL Query Editor and MDX Query Editor. The reports can be built together or separately. The Report builder feature has been newly introduced to enable BI users to create customized reports that rides on the Microsoft Office paradigms of Excel and PowerPoint and has a clickonce feature that enables it to be deployed via a browser.
Windows Server System Common Engineering Roadmap defines a standard set of capabilities of the server system such as common patch management, Watson Support and tools such as Microsoft Baseline Security Analyzer for delivery of a consistent and predictable experience for the Administrator. It creates a set of services that can be implemented across all Windows platforms and raises the bar on server infrastructure by ensuring that security, reliability, manageability and flexibility are taken into consideration. It adopts services oriented architecture and integrates with .NET to connect people, systems and devices through software. It focuses on delivering systems that are focused on dynamic operations building and monitoring. Its corner stone is trustworthy computing. 16 specifications have been defined for application throughout the Windows Server System. Microsoft proposes to add further specifications at regular intervals to address customer requirements.
SQL Server 2005 as described above comes with significant advances in the areas of enterprise data management, developer productivity and business intelligence. It helps leverage data assets, increase productivity, reduces information technology complexity and reduces the overall cost of ownership.
