Transcript SQL Server & High Availability - E

By George Squillace
New Horizons Great Lakes
Husband, Dad, Coach, MCT, MCSE, MCDBA
MCITP – Database Administration
MCITP – SQL 2008 Business Intelligence Development
MCITP – Enterprise Messaging Administrator on Exchange 2007
MCITP – Enterprise Administrator on Windows Server 2008
» More confidently sell high margin courses that are not
provided by many vendors.
» Be able to respond to customer questions more rapidly.
» Make better course recommendations.
» Grow in your career skills.
» From Wikipedia: Business intelligence (BI)
is a business management term which refers to applications and
technologies which are used to gather, provide access to, and analyze
data and information about company operations.
» Business intelligence systems can help companies have a more
comprehensive knowledge of the factors affecting their business,
such as metrics on sales, production, internal operations, and they
can help companies to make better business decisions.
» BI is about making better decisions.
» Tools 
Facts  Conclusions 
Changes.
» Reporting Services (SSRS)
» Analysis Services (SSAS)
» Integration Services (SSIS)
Each one of these categories involves a
significant body of knowledge.
» The BI capabilities within SQL Server are included with the
price of Microsoft SQL Server.
» Including BI capabilities with SQL Server has produced a
lot of momentum for Microsoft and this opportunity
should be capitalized on while there is time.
» The BI development capabilities are configured within
“BIDS”, or Business Intelligence Design Studio, which is
Visual Studio with BI-specific project templates.
Observe: the complexity of each of these interfaces in
the following screenshots!
»
Report Designers use Visual Studio (BIDS) with specific report development templates to design
reports. This development tool is called “Report Designer”.
»
Users and Administrators connect to a web application through a browser to browse reports and
perform administration on Reporting Services. The web application is called “Report Manager”.
»
Example of available report features are:
˃ Parameterization (where users can pick from available lists, available date ranges, etc.
˃ Navigation features, such as Document Maps, Bookmarks, and Linking
˃ Drill-down
˃ Drill-through
˃ Charting and Visualizations (Gauges, and Geographical Data)
»
Supported Report Export Formats are:
˃ .pdf
˃ Excel
˃ Webpage/HTML
˃ Text file
˃ XML
˃ Others
» SSIS succeeds “Data Transformation Services”
(DTS) in SQL 2000.
» The Bulk of SSIS Is Used for “ETL”.
˃ Extract (connect to a data source)
˃ Transform (change data along its path as
necessary)
˃ Load (send the data to a destination)
» Very common requirement for database
administrators and developers to perform ETL.
» Specifically useful for data warehousing.
» Supports almost any data source and data
destination.
»
The Point: Better Decision Making.
»
Usually involves a specially designed (often humongous) relational database, called a data
warehouse, populated through SSIS to perform advanced data analysis and data mining.
»
The Data Warehouse requires population of a special database design called a Star
Schema, which is a Fact Table connected to a number Dimension Tables. The warehouse is
populated from other operational databases in the organization. This population requires
significant effort. SSIS can be used to populate the data warehouse.
»
The Data Warehouse is used to create an “OLAP Cube” (or more than one Cube).
»
An example of supported analysis of an OLAP Cube would permit the rapid answer to the
question,…
˃ How many Male, Married, w/Children under 13, w/College Degree, > 40 years old,
Paid by VISA, ordered Product X?
» OLAP cubes are non-trivial to design and implement.
» May involve the use of Excel and its Pivot Table capabilities but more likely
an OLAP Client program.
» Analysis Services may involve learning additional query languages such as:
˃ Multi-Dimensional Expressions (MDX)
˃ Data Mining Extensions (DMX)
˃ XML for Analysis (XMLA)
» Data Mining is its own large functional area within Analysis Services and is
used for predictive analyses.
» Again, this component is included with the purchase of Microsoft SQL
Server but costs $10,000 or more for other platforms like Oracle
Notice:
Cube Browsing
is normally done
with a special
front-end called
an OLAP client.
Excel (2007+)
can serve as an
OLAP client.
The cube
browsing
capabilities
shown here only
provide a reality
check for your
cube design.
The figure
reveals one
type of data
mining usage
called a Tree
Viewer.
The figure
reveals
another
type of data
mining
usage
called a
Decision
Tree.
6234A Implementing and Maintaining SQL Server 2008 Analysis Services
 Describe how SQL Server Analysis Services can be used to implement
analytical solutions.
 Create multidimensional analysis solutions with SQL Server Analysis
Services.
 Implement dimensions in an Analysis Services solution.
 Implement measures and measure groups in an Analysis Services solution.
 Query a multidimensional Analysis Services solution.
 Customize an Analysis Services cube.
 Deploy and Secure an Analysis Services database.
 Maintain a multidimensional Analysis Services solution.
 Implement a Data Mining solution.
2792A Implementing and Maintaining SQL Server 2005 Integration Services
or,
6235A Implementing and Maintaining SQL Server 2008 Integration Services
»
»
»
»
»
»
»
»
»
Describe SQL Server Integration Services and its tools.
Create an Integration Services package.
Implement control flow in an Integration Services package.
Implement data flow in an Integration Services package.
Implement logging in an Integration Services package.
Debug and implement error handling in an Integration Services package.
Implement checkpoints and transactions in an Integration Services package.
Deploy an Integration Services package.
Manage and secure an Integration Services package.
2793A Implementing and Maintaining SQL Server 2005 Reporting Services
or,
6236A Implementing and Maintaining SQL Server 2008 Reporting Services
»
»
»
»
»
»
»
»
»
Describe SQL Server Reporting Services and its components.
Create a Reporting Services report.
Enhance a Reporting Services report.
Create and manipulate data sets.
Use report models to implement reporting for business users.
Configure report publishing and execution settings.
Implement subscriptions for reports.
Administer Reporting Services.
Implement custom Reporting Services applications.
» SQL 2008 BI Exams
˃ MCTS, 70-448
˃ MCITP, 70-452 (Very challenging…mine was 88 lengthy story problems, three full
hours long)
» Remember, the BI exams cover all three functional BI
areas!
» See also this diagram to understand the difference
between Microsoft Exams and Microsoft Courses.
» See also this diagram for more info about SQL Server
certification tracks.
»
Microsoft SQL Server homepage
˃
»
Microsoft Learning homepage
˃
»
www.microsoft.com/sql
www.microsoft.com/learning
My website
˃
Homepage
»
˃
Diagrams
»
˃
www.e-Squillace.com/tech/screenshotgallery (look in the SQL screenshot category; a SQL BI category exists too)
˃
SQL Server reference homepage
˃
SQL Server BI reference homepage
»
»
www.e-Squillace.com/tech/techreference/sql (a huuuuge amount of links)
www.e-Squillace.com/tech/techreference/sqlbi/sqlbi.htm (also a huuuuge amount of links)
My Library Books (click on this link)
˃
˃
˃
˃
˃
˃
»
www.e-Squillace.com/tech/techdiagrams (several SQL and SQL BI diagrams)
Screenshot Gallery
»
»
www.e-Squillace.com (Search button, Test taking tips link)
SSAS Step-by-Step
SSIS Step-by-Step
SSRS Step-by-Step
Microsoft Data Warehouse Toolkit
Data Mining with SQL Server 2008
FastTrack to MDX
User Groups
˃
˃
West Michigan SQL Server User Group (WMSSUG)
Detroit Area SQL ServerUser Group (Detroit SSUG)
September 17, 2011
» Microsoft released SQL 2008 R2 (the second SQL
2008 release) in April 2010.
» As of yet there are no plans to release SQL 2008
R2-specific course versions.
» Miscellaneous note: Microsoft offers free versions
of SQL Server and Visual Studio with smaller
feature sets. These are called Express Editions.
» Basic Windows Server Operating System knowledge
» Background database experience
˃ (Microsoft Access experience “counts”)
» SQL Server administration and possibly programming
experience
˃ Course 2780 or 6231
˃ Course 2779 or 6232
» Basic query writing knowledge
˃ Course 2778
» Learn & study?
» Download?
» Call a client with a recommendation or new
opportunity?
» Other?
» Implementation Courses
˃ Hands-on oriented.
˃ Procedural.
˃ Students leave class having had success completing a related series of steps.
» Design Courses
˃ Asked during class, “What questions do I need answers to?”
˃ What decisions must I make?
˃ Conceptual, minimally or rarely procedural.
˃ Student participation and involvement (when experienced) is a big value to other students.
» 2794A Designing a Business Intelligence Solution
for the Enterprise Using Microsoft® SQL Server 2005 (2 days)
» 2795A Designing an ETL Solution Architecture Using Microsoft®
SQL Server 2005 Integration Services (2 days)
» 2796A Designing an Analysis Solution Architecture Using
Microsoft® SQL Server 2005 Analysis Services (2 days)
» 2797A Designing a Reporting Solution Architecture Using
Microsoft SQL Server 2005 Reporting Services (2 days)
» Availability will be difficult