Objectives: This course provides a comprehensive introduction to Microsoft SQL Server 2008. You learn how to use the various administrative and development tools included with SQL Server.
- Build and manage SQL Server 2008 databases
- Retrieve and manipulate data with SQL queries
- Secure and monitor databases with Management Studio
- Replicate data over multiple servers with merge replication
- Transform data into strategic information using Business Intelligence processes
Target Audience:Typical participants include:
- Programmers, application designers, developers/system architects who design/write MS SQL Server applications and who require an Intro to the big picture of what SQL Server can do
- Database administrators looking to explore the capabilities of SQL Server
- Data processing professionals who have experience working with traditional database systems (hierarchical or network) and who are now moving to the client/server world
Pre-Requisites: Familiarity with relational database constructs such as tables, rows, columns and referential integrity is helpful. Prior experience with SQL Server is not required.
Duration: 3 Days
Module 1 - Getting Started with SQL Server 2008
- Overview of SQL Server architecture
- Fundamentals of relational databases
- Client/server model
- SQL Server components and tools
- SQL Server Management Studio
- Business Intelligence Development Studio
- SQL Server Agent
- Database engine
Module 2 - Constructing the Database
- Creating a database
- Allocating file size and growth parameters
- Implementing database schemas
- Designing tables
- Defining column attributes
- Indexing tables with Management Studio
- Adding, deleting or changing columns
- Protecting data with constraints
- Enforcing uniqueness with primary key constraints
- Building relationships with foreign keys
Module 3 - Server-Side Programming
- Data retrieval with Transact-SQL
- Leveraging Query Designer to construct SQL statements
- Writing multiple table join queries
- Aggregating data for business analysis
- Modifying data
- Adding new rows with the INSERT command
- Changing targeted data with UPDATE
- Deleting specific rows
- Programming constructs
- Writing stored procedures for performance and productivity
- Capturing change events with triggers
- Simplifying data access with user-defined functions
Module 4 - Integrating with Client Applications
- Connecting to SQL Server
- Authenticating with SQL Server and Windows logins
- Programming client applications
- Interacting with Microsoft Office
- Pulling data from SQL Server into Excel
- Migrating an Access database to SQL Server
- Leveraging XML and .NET features
- Retrieving data in XML form
- Utilising a .NET CLR component
Module 5 - Administering SQL Server 2008
- Securing the database infrastructure
- Controlling access to SQL Server
- Granting or revoking permissions
- Defining roles and user memberships
- Running the Maintenance Plan Wizard
- Automating backups by scheduling jobs
- Checking database integrity and defragmenting data
- Notifying operators via e-mail
Module 6 - Sharing Data through Replication
- Defining the replication architecture
- Snapshot
- Transactional
- Merge replication
- Choosing a replication strategy
- Configuring merge replication
- Distributing published data
- Subscribing to a publication
Module 7 - Business Intelligence Components
- Transforming data with Integration Services
- Importing and exporting files
- Moving and denormalising data into a data warehouse
- Producing information with Analysis Services
- Building multidimensional cubes
- Predicting trends with data-mining
