SQL Server 2005

You will learn how to

• Build databases, queries and reports for SQL Server 2005
• Create and manage databases with SQL Server Management Studio
• Generate Transact-SQL statements for data retrieval and modification
• Implement security and back up databases to protect critical data
• Configure merge replication and handle data conflicts
• Deploy business intelligence solutions using Integration, Analysis and Reporting Services

Course benefits
SQL Server 2005 offers a robust architecture for enterprise data management, developer productivity and business intelligence. The ability to employ and capitalise on this powerful feature set is essential for an organisation in the current competitive market.

This course provides the knowledge and practical skills needed to exploit SQL Server 2005. You gain a solid foundation on which to build essential SQL Server development and administrative skills.

Who should attend
This course is valuable to those who are new to SQL Server, as well as those evaluating SQL Server 2005. Familiarity with relational databases is helpful.

Hands-on training
Exercises throughout the course reinforce the knowledge you gain in class, including:

• Installing SQL Server 2005
• Creating databases with SQL Server Management Studio
• Adding primary and foreign key constraints to tables
• Retrieving and joining data from tables
• Securing database object access
• Defining a database maintenance plan
• Recovering a damaged database
• Importing data from text files using SSIS
• Synchronising databases with merge replication
• Building a multidimensional cube with Analysis Services
• Rendering a report in HTML using Reporting Services

Course content

Introducing SQL Server 2005 Overview of database fundamentals Client/Server RDBMS SQL Server 2005 editions Express Standard Enterprise Workgroup Mobile Developer SQL Server components and tools SQL Server Management Studio Business Intelligence Development Studio SQL Server Agent Database engine Assembling a Database Building a database Choosing file names and locations Determining file size and growth parameters Utilising schemas to partition tables Designing tables Defining columns and attributes Selecting appropriate datatypes Setting the primary keys and indexes Modifying or removing columns Applying declarative constraints Enforcing check constraints Protecting relationships with foreign keys Validating the existing data Developing Database Applications Retrieving data with Transact-SQL Writing SQL queries Sorting result sets Joining tables Aggregating data Refining result sets with subqueries Defining and using full-text indexes Altering data with SQL commands INSERT UPDATE DELETE Storing query definitions Generating views with Management Studio Simplifying data access with views Server-side programming Constructing a stored procedure Executing stored procedures Exploring functions and triggers Client-side programming Integrating SQL Server with Office Upsizing from Microsoft Access Displaying data on the Web with ASP.NET Managing SQL Server Security and Administration Administering security Granting SQL Server logins Implementing a security plan Creating database roles Deploying the Maintenance Plan Wizard Checking database consistency Verifying index pointers Backing up the database and log Reporting on maintenance plan success Automating administration Defining operators Generating and scheduling jobs Notifying operators of job outcome Replicating Data Designing replication strategies Configuring the distribution server Publishing data with merge replication Pulling or pushing subscriptions Resolving conflicts using priorities Leveraging Business Intelligence SQL Server Integration Services (SSIS) Importing data into SQL Server Exporting data to text or CSV format Changing packages with SSIS Designer Analysis Services Storing multidimensional data Pivoting cubes in Microsoft Excel Detecting patterns with data mining SQL Server 2005 Reporting Services Authoring and deploying reports Extracting data from Analysis Services Viewing reports from a Web site Providing Application Solutions with SQL Server Exploiting XML B2B capabilities Investigating .NET Common Language Runtime (CLR) assemblies Monitoring changes with Notification Servic