|
| |
| |
|
|
|
Da Notes -
70-019 - R
Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender. |
Passing Score: 620, Questions: 50, Time: 150 Min.
|
Da Notes Index
|
|
|
RDO
|
Using ODBC, SQL Server also supports applications written to the Remote Data Objects (RDO) and Data Access Objects (DAO) APIs. These are object APIs that encapsulate ODBC.
|
REPLICATION
|
Considerations: transactional consistency, latency, site autonomy, security, existing data sources, data update frequency, performance, administration, and whether to allow updating subscribers.
Transactional and snapshot - best ways to replicate data from an OLTP Server to a data warehouse and from a data warehouse to a data mart. Snapshot replication - best way to replicate aggregate data
Transactional - Only changed data is replicated. Transaction sequence is maintained. No conflicts because there is only one location where data is changed.
Snapshot replication - All data (changed and unchanged) is replicated on a periodic basis or on demand.
Merge replication - Multiple locations make changes to data independently of one another. Changes are merged together at the source server. Conflicts can occur (several resolution methods) so it does not guarantee transactional consistency.
Publisher - Makes published data from that database available for replication. Send changes to the published data to the Distributor.
Subscriber - SQL Server that receives an initial copy of the replicated data and later receives updates to this data. It is possible to allow the subscriber data to be changed and be a Publisher to other Subscribers.
Distributor - Receives a copy of all changes to the published data and sends it to the subscriber(s). A special system database (distribution database) and a folder (distribution working folder - C:\Mssql7\Repldata) are created on the Distributor for storing the data and configuration info.
Vertical Filtering - Contains a subset of table columns. The Subscriber receives only the replicated columns. For example, you might use a vertical filter to publish all but the Salary column from the Employee table. Vertical filtering is similar to specifying only certain columns from a table in a SELECT statement. Merge replication does not support vertical filtering.
Horizontal Filtering - Contains a subset of the table rows. The Subscriber receives only the subset of rows. For example, you can publish order records by region to each region. Horizontal filtering is similar to specifying a WHERE clause in a SELECT statement.
Push Subscriptions - Centralize subscription administration in the following ways:
Defined at the Publisher.
Multiples are set up simultaneously for each publication.
Distributor propagates the changes to a Subscriber without a request from the Subscriber to do so. Typically, push subscriptions are used in applications that must send changes to Subscribers as soon as they occur. Push subscriptions are best for applications that require higher security and near-real-time updates and in cases in which the higher processor overhead at the Distributor does not affect performance.
Replication agents run at the Distributor or the Publisher.
Pull Subscriptions - Can be set while administering a Subscriber. The following are characteristics of a pull subscription:
Subscriber initiates a pull subscription.
Publication must be enabled to allow pull subscriptions.
Only SQL Server Subscribers fully support pull subscriptions.
The Subscriber Sa or dbo decides which publications are received and when to receive them.
Best for applications that need lower security, mobile users and the Internet.
Two kinds of pull subscriptions are available.
Standard pull subscriptions are registered on the Publisher.
Anonymous pull subscriptions (Internet).
Replication agents run at the Subscriber.
|
REPORTS
|
Query Analyzer - users will not be able to write simple queries
Access front end with a stored procs for each report - Forms or canned reports can be used.
Excel with Pivot table service - users can create local cubes to be worked on
|
ROLAP
|
ROLAP (relational)
· Slower query response times. Better when disk space is limited. Uses tables in the data warehouse relational database to store a cube's aggregations. Does not store a copy of the base data
· Typically used for large data sets that are infrequently queried (historical data).
· Aggregations cannot be created for ROLAP cube storage if the data source is OLAP Services
· Custom app steps needed to create a ROLAP cube (see MOLAP)
· ROLAP: specify OPTIONS_DEFER_DATA
|
|
|
|
|
