What is this course about?
This course provides a comprehensive introduction to Microsoft SQL Server 2012. You learn how to use the various administrative and development tools included with SQL Server. In addition, you are introduced to the Microsoft BI stack: SQL Server Integration Services, Analysis Services, and Reporting Services.
The extensive hands-on exercises provide you with valuable experience leveraging the capabilities of SQL Server and its accompanying components. This course provides the foundation for all other SQL Server courses.
Who will benefit from this course?
Typical participants include:
- Programmers, application designers, developers and system architects who design and write Microsoft SQL Server applications and who require an introduction to the big picture of what SQL Server can do
- Database administrators looking to explore the capabilities of SQL Server
- Project managers who will be leading a team of developers in constructing a database
- Data processing professionals who may have many years of experience working with other database systems and who are now moving to the SQL Server world
- Evaluators of database servers who will be making a decision about whether SQL Server 2012 is right for their organization
What background do I need?
Tools used in the hands-on exercises assume a familiarity with the Windows user interface. Familiarity with relational database constructs such as tables, rows, columns and referential integrity is helpful. Prior experience with SQL Server is not required.
What is SQL Server 2012?
SQL Server 2012 is the latest version of Microsoft's full-featured relational database management system (RDBMS) with support for robust server-side development. The SQL Server name has grown to include the Business Intelligence product stack.
I am currently an experienced database developer/administrator working in SQL Server 2008. Is this course right for me?
No. This course is designed primarily for those with little or no experience working with SQL Server 2012.
I've taken SQL Server® 2008: A Comprehensive Hands-On Introduction, is this course still beneficial to me?
No, while there are some differences in SQL Server 2012, the core concepts of the courses are the same.
We use SharePoint in our organization. Will this course teach me more about how SharePoint uses SQL Server?
No, while this course discusses SharePoint as part of the BI solution, this course focuses on SQL Server 2012. SharePoint is not used in any of the hands-on exercises.
How much time is spent on BI in the course?
The majority of the course is spent on the SQL Server database engine, however, you are introduced to all of the main BI components, including performing exercises with Integration Services, Analysis Services and Reporting Services.
How much time is spent on each topic?
Introducing SQL Server 2012 2.0
Building the database 4.0
Leveraging server programmability 5.0
Administering SQL Server databases 5.0
Integrating external applications 2.0
Implementing Business Intelligence solutions 5.0
Times, including the workshops, are estimates; exact times may vary according to the needs of each class.
What kind of hands-on exercises will I perform?
Approximately 40 percent of class time is spent in hands- on exercises. Hands-on exercises provide immediate reinforcement of the material covered. Exercises include:
- Constructing tables and protecting data with referential constraints
- Retrieving and modifying data with SQL
- Moving data with the import/export utility
- Automating administration tasks with the Maintenance Plan Wizard
- Administering SQL Server security and resilience
- Designing a Report with Reporting Services
- Transforming data into an Analysis cube as part of a Business Intelligence solution
I'm attending this course from work using AnyWare - Learning Tree's web-based remote attendance platform. How will that impact what I learn?
You will participate fully in the course and acquire the same knowledge and skills as your classmates who participate in the classroom. You will have the same course materials, be able to easily communicate back and forth and ask questions of your instructors and peers, and you will control an in-classroom workstation dedicated entirely to you. Your instructor will be able to see exactly what you're doing and can interactively offer concrete help.
Introducing SQL Server 2012
Overview of the SQL Server suite of products
- Fundamentals of the relational database engine
- Benefits of the Business Intelligence stack
SQL Server components and tools
- SQL Server Agent and Management Studio
- Integration Services
- Analysis Services
- Reporting Services
- Data Quality Services
- Master Data Services
Building the Database
Constructing a relational database
- Choosing optimal file size and growth parameters
- Working with database schemas
- Specifying appropriate datatypes
- Indexing tables with Management Studio
- Adding, modifying or removing columns
Safeguarding data with constraints
- Enforcing uniqueness through primary key constraints
- Validating conditions with check constraints
- Protecting relationships with foreign keys
Leveraging Server Programmability
Retrieving data with Transact-SQL
- Employing Query Designer to construct select statements
- Analyzing data for aggregate functions
- Retrieving data as an XML document
Modifying data with SQL commands
- Inserting new rows into tables
- Updating targeted data
- Deleting specific rows
Developing programming constructs
- Creating stored procedures for productivity
- Writing user-defined functions to simplify queries
- Deploying triggers to capture change events
Administering SQL Server Databases
Executing the Maintenance Plan Wizard
- Defragmenting data and checking database integrity
- Improving performance by rebuilding indexes
Automating administrative tasks
- Scheduling repeating jobs
- Implementing a backup strategy
Monitoring SQL Server resources
- Displaying current activity and expensive queries
- Running reports with Management Studio
Securing the database infrastructure
- Authenticating SQL Server and Windows logins
- Assigning users to database and server roles
- Granting or revoking permissions
Integrating External Applications
Connecting to Microsoft Office
- Pulling data from SQL Server into Excel
- Creating a front-end interface with Access
Transferring business information
- Importing and exporting data from text files
- Migrating an Access database to SQL Server
Implementing Business Intelligence Solutions
Extracting data with Integration Services
- Transforming and cleaning data
- Loading data into a data warehouse
Information delivery with Reporting Services
- Designing reports quickly with the Report Builder
- Deploying reports for centralized access
Aggregating data with Analysis Services
- Building multidimensional cubes
- Predicting trends with data mining
Developing a Business Intelligence strategy
- Leveraging SharePoint, Excel and PowerPivot
- Transferring data into knowledge with BI functionality
Those who are new to SQL Server, including database administrators and developers who need a solid foundation in creating SQL Server 2012 databases.