Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
38 Cards in this Set
- Front
- Back
|
SQL server 2008 components
|
- SQL server database engine
- Analysis services - Reporting services - integration services - tools -- Sql server management studio (SSMS) -- Business intelligence Development studio (BIDS) -- SQL server configuration manager -- SQL server profiler -- Database engine tuning advisor -- command prompt utilities |
|
|
SQL server database engine
|
- contains core services to store, process, manage and secure relational and xml data
- services for full-text search and data replication |
|
|
Analysis services
|
- contain services for creating, processing and managing online Analytical Processing (OLAP) analysis
- services for data mining models for Business Intelligence |
|
|
Reporting Services
|
- server based reporting platform
- provides tools and services to generate reports - reports: -- interactive -- tabular -- graphical -- free-form |
|
|
integration services
|
-platform containing tools and services for building extract, transform and load (ETL) operations for data warehousing, data integration and workflow solutions.
|
|
|
SQL server management Studio (SSMS)
|
- managing all SQL components
- includes relational database - includes business intelligence examples: - developing and managing sql server database engine and notification solutions - managing deployed analysis solutions - managing and running integration services packages - managing report servers, reporting services reports and report models. |
|
|
Business intelligence development studio (BIDS)
|
- used by business application developers.
- platform to create cubes, data sources, reports and integration services packages - develop solutions with analysis services, integration services and reporting services. - now a Microsoft Management Console snap-in managing connectivity components - replaces: -- server network utility -- client network utility -- service manager |
|
|
SQL server profiler
|
- captures SQL server events and saves them to a trace file
- trace file can be analysed and replayed to diagnose problems. - diagnosis by correlating performance indication and a series of events. |
|
|
DTA (database engine tuning advisor)
|
- A tool to analyze a workload and the physical database implementation.
- Can recommend changes to the physical database design structure to improve query performance for a given workload, after analysis. |
|
|
Command prompt utilities
|
Additional tools from the command prompt:
-bcp; copy data between instances of sql server and a data file using a user-specified format - dta; command line version of database engine tuning advisor - dtexec; used to configure and execute SSIS packages -dtutil; used to manage SSIS packages - dqlcmd; command line interface to enter and execute t-sql statement, procedures and script files - rs; a command line tool to manage Reporting Services report server - rsconfig; used to manage and configure a report server connection - rskeymgmt; used to manage report server encryption keys - sqlagent90; Used to start the SQL server agent - SQLdiag; collects diagnostics for use by Microsoft Customer Service and support. - SQLlogship; used for operational tasks such as backup, copy and restore operations - SQLservr; used to start and stop the database engine for a specified instance - SSMS; command that oepns SQL server management studio -SQLPS; used to run PowerShell commands and scripts - tablediff; provides the capabi |
|
|
SQL server 2008 editions
|
- Server
- Specialized - Free |
|
|
SQL server 2008 Server Editions
|
- enterprise
- standard - best suited for high transaction volumes and large production environments |
|
|
SQL server 2008 enterprise edition
|
- contains all server components
- supports the maximum number of processors that the server will allow |
|
|
SQL server 2008 standard edition
|
- departmental or small business environment
- 75 or fewer computers - maxium of four processors in the server |
|
|
SQL server 2008 specialised editions
|
- workgroup edition
- web edition - developer edition |
|
|
SQL server 2008 workgroup edition
|
- reliable data management and reporting components
- small volumes of data on small servers. - supports 2 processors |
|
|
SQL server 2008, Web edition
|
- supports highly available web serving database applications
- four processors supported. |
|
|
sql server 2008, developer edition
|
- fully featured like enterprise edition
- testing and development only |
|
|
Free editions:
|
- targeted at ISV and mobile application community
- express edition - compact edition |
|
|
SQL server 2008 Express edition
|
- free and light weight
- can be redistributed by ISV's - One CPU - One GB of memory - max database size: 4GB |
|
|
Compact edition
|
- embedded database version
- standalone as well as occasionally connected applications - supports desktops and web clients - limited to 4 gb hard disk size/database - one cpu - maxium memory available - not all of the programmable features - stored procedures, triggers and view, native xml and service broker not supported |
|
|
Server Enterprise editions specs:
|
- Enterprise workload
- high availability - scalability - performance - data redundancy - built in business intelligece - CPU: OS max - RAM min 512kb - Ram Recommended: 2.048 GB or more - Ram max: OS Max - DB size: Max unlimited |
|
|
Server standard edition specs:
|
- shared data supporting departments
- small to large businesses - CPU Maximum: 4CPU - RAM min: 512 MB - RAM recommneded: 2.048 GB+ - RAM max: OS max - DB size: Unlimited |
|
|
Specialised Workgroup edition specs
|
- remote offices that need local instances of company data
- CPU maximum: 2 - Ram min; 512 mb - RAM recommended: 2.048 GB+ - ram max: 4 gb - DB size: max: Unlimted |
|
|
Specialised web edition specs
|
- supports web application hosting
- max cpu's: 4 - min memory: 512 mb - recommended memory: 2.048 GB+ - max ram: OS max - max db size: unlimited |
|
|
Specialised developer edition specs
|
- full-featured edition
- development and testing only - cpu max: Os max - min memory: 512 mb - recommended memory: 2.048 gb - max memory: os max - max db size: unlimited |
|
|
free express edition
|
- entry level db
- ideal for learning - ideal for isv redistribution - cpu max: 1 - memory min: 512 mb - memory recommended: 2.048 gb or more - memory max: 1 gb - max db size; 4 gb |
|
|
free compact 3.5 edition
|
- embedded database for developing desktop and mobile applications
- cpu max: os max - min memory: 512 mb - recommended memory: 2.048 gb - max memory: os max - max db size: 4 gb. |
|
|
scalability
|
- the ability to add more resources, ie, cpu's, memory or disks.
- adding to a single server - scaling out by moving services or db's to other services. |
|
|
key scalability features in sql server 2008 database engine:
|
- partitioning
- data compression - resource governor - partition table parellelism - multi-instance support - log shipping - replication |
|
|
partitioning:
|
-segmenting of data inlarge tales and indexes into units to distribute across multiple filegroups
- improves IO performance by distribution - usefull for large OLTP and data-warehousing databases |
|
|
data compression
|
- reduces the size of tables, indexes or partitions
|
|
|
resource governor:
|
- provides control over the amount of cpu and memory allocated to workloads without changing the application
|
|
|
partition table parellelism
|
- improves the query processing performance on partiitoned tables for parallel queries
- it does this by enhancing the partitioning information in the compile-time and runtime execution plans |
|
|
multi-instance support
|
- establishes maximum number of SQL server instances that can be created on a server.
- enterprise edition supports up to 50 inst ances. - compact edition supports 1 edition - all other editions support 16 editions |
|
|
log shipping
|
- allows you to send transaction log backups from a primary database on a primary server instance to a secondary database or multiple secondary databases on seperate server instances.
- supports high availability of database mirroring - express and mobile editions do not support this feature. |
|
|
replication
|
- enables a primary database server to distribute data to one or more secondary servers
|
|
|
features
|
- partitioning, data compression and partition table parellelism only available on Enterprise edition.
- some features are limited or nto available on standard edition |
