|
| |
| |
|
|
|
Da Notes -
70-028 - 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: 680, Questions: 52, Time: 90 Minutes
|
Da Notes Index
|
|
|
Recovery
|
Recovery Process :
Committed transactions are rolled forward and written to database
Uncommitted transactions are rolled back and are not written to database
|
Replication
|
Methods for Distributing Data:
Replication: Makes possible the transfer of data from a source to a destination or destinations. Allows for site autonomy and scalabilty. Can be used to ensure transactional integrity without the overhead of distributed transactions.
Distributed Transactions: Transactions occur at the same time to all copies of data on all servers involved in the transaction. Using the 2-phase commit protocol (2PC) ensures transactions are committed on all servers or not at all on any. Requires good connectivity between servers. Useful when data needs to be same across all servers at the same time.
Terminology:
Publisher: Makes data available to other servers (Subscribers) for replication. Data may be published again by Subscribers. Data elements that are replicated have a single publisher.
Distributor: Contains the distribution database that holds metadata (system tables) used for replication, history, and, for transactional replication, transactions. Can be on the same machine as Publisher or Subscriber.
Subscriber: Receives updates. In some cases, can also make updates (see below).
Publication: A collection of articles for publication. Each publication has at least one article. A single publication can be configured for both push and pull subscriptions.
Article: Grouping of data--entire table, selected colums (vertical filtering), selected rows (horizontal filtering), or even a stored procedure. A publication will often have multiple articles. Subscription is to a publication, not an article, which was possible in SQL 6.5.
Push Subscription: The Publisher initiates the replication to the subscribers. Useful when changes have to be sent as soon as they occur, but replication can be scheduled.
Pull Subscription: The Subscriber initiates the replication according to a schedule. Best for situations where there are many Subscribers. Also best for mobile users who have the flexibility to determine when to recieve updates. You can also set up a special type of Pull Subscription for anonymous users. Useful if you are publishing information to the Internet or if you wish to reduce overhead associated with large numbers of subscribers.
Horizontal Filtering: Allows you to publish only a subset of rows to a Subscriber. Useful when the sites only need certain rows in the database. Requires use of columns that can be used to identify sites. Can be used for all replication types. Avoid if DB is small, has low activity, etc.,.
Vertical Filtering: Allows you to publish only a subset of columns. Not supported for Merge Replication (however, you could simply create a table at the publisher that only included the columns for publication). Can be used for improving performance by eliminating large text or image columns, etc.,. Little impact on performance as compared to Horizontal Filtering.
Fragmenting: Allows you to partition data. For example, 2 servers share the same table and complete data, but each needs to update information specific to only its region while being able to view the data from the other region. Each server will be both Publisher and Subscriber to the other and publish data specific to its region and receive data from the other in the same table. Stored procedures could be used to ensure that each region updated its own data. A disadvantage is the need to maintain table schema at multiple locations.
Join Filters: Available for Merge Replication only. Allows you to include rows from other, related tables.
Dynamic Filters: Available for Merge Replication only. Allows you to replicate a subset of data to particular machines or users.
Replication Types:
Snapshot Replication: Takes a picture of the data at a point in time. Not as CPU intensive as Transactional Replication, which has to monitor publications for updates. Simplest type of replication. Guarantees latent transactional integrity between source and destination. Good for read-only subscribers who do not need most recent copy of data.
Snapshot Replication with Immediate-Updating Subscribers: An optional configuration of Snapshot Replication that allows subscribers to make changes at the subscriber and the publisher using 2PC. Transactional integrity is maintained between publisher and subscriber. This method of updating the publisher requires that only the subscriber and the publisher involved in the transaction be enlisted for the distributed transaction, not all the servers subscribing to the publication. Good for situations where subscribers have to make occasional updates to data.
Transactional Replication: Used for replicating tables (all or part of a table) and stored procedures. The Log Reader Agent monitors the logs of publications for INSERT, UPDATE, DELETE statements and other modifications and then stores these modifications in a queue, the distribution database, for replication to subscribers. Changes are made at the publication server, so transactional integrity is guaranteed. Given good network connections, there can be low latency between publisher and subscriber (less than a minute for push subscriptions). Can also be used for pull subscriptions where subscribers are not always connected and require read-only data, eg., salesperson who needs to get inventory and price lists.
Transactional Replication with Immediate-Updating Subscribers: Allows subscribers to make updates to their local data and the data on the publisher using a distributed transaction. Transactional integrity guaranteed using 2PC. All subscribers eventually have transactions replicated to them from the publisher.
Merge Replication: In merge replication, both the publisher and the subscriber update data. The data contained in the replica copies held by the publisher and the subscribers are the result of synchronization (convergence). With merge replication, there is no guarantee of transactional integrity and conflicts between updates can arise. SQL Server resolves conflicts based on 'generation numbers' and configured priorities--some server will "win" in the case of a conflict. Merge replication guarantees that eventually all servers will converge to the same resultant data, but the converged data may be different from the data resulting from other forms of replication that guarantee transactional integrity.
Replication Agents:
Snapshot Agent: Used to initialize all replication types and to perform Snapshot Replication itself. The agent creates the schema and the data to be sent to Subscribers. It first connects from Distributor to Publisher and locks tables for publication (should be run during periods of low activity because no updates can occur in the tables during the lock). It then connects back from Publisher to the distributor and places schema in a .sch file and indexes (if indexes or DRI are requested in the publication) in a .idx file on the Distributor. The agent then takes a snapshot of the published data and stores it in a file on the Distributor--the file is a native .bcp (bulk copy) file for SQL Server data sources and a .txt character mode file if data sources other than SQL are involved in replication. The agent then adds rows to the MSrepl_commands table on the Distributor indicating the location of the .sch, bcp, and .txt files (synchronization set); it also adds rows for the synchronization task in the MSrepl_transactions table on the Distributor.
Distribution Agent: Used for Snapshot and Transactional Replication. For Snapshot Replication, the agent establishes a connection from the server it is running on to the Distributor to read the MS_replcommands and MSRepl_transactions tables and to move schema and data to Subscribers. For pull subscriptions, agent runs on Subscriber; for push on the distributor. Place the agent on a Subscriber (pull subscription) when you have large numbers of Subscribers to save resources on the Distributor. The distribution agent in Transaction Replication moves transactions (commands) stored in distribution database to Subscribers. For push replication, the agent runs on the distributor; for pull on the Subscriber. (The distribution database does not contain any user tables--don't add objects to it.)
Log Reader Agent: Used only for transaction replication. The agent examines the transaction logs of databases marked for replication and identifies transactions (INSERTS, UPDATES, and so on) that need to be replicated. It then copies transactions to the distribution database, which acts as a store-and-forward queue for the transactions. When the transactions are committed in the distribution database, it updates the original transaction logs to indicate which transactions have been copied to the distributor and consequently which rows can be truncated from the original logs on the Publisher. You cannot truncate transactions on the publisher unless they have been committed in the distributor database. Data that is no longer required for transactional replication is cleaned up by 3 tasks: Agent checkup, Transaction cleanup, and History cleanup.
Merge Agent: Used for Merge Replication. The Merge agent looks at rows in the merge article that have a generation column value of "0". (A trigger on the article sets the value of the generation column to 0 every time an update is performed on the row.) The merge agent, which keeps track of generation values it has sent to other sites and that other sites have sent to it, assigns new generation values that are higher than previous values. It then sends the changed data to other sites, where the data is merged according to configurable rules. In the case of a conflict, which is detected through lineage values in the MSmerge_contents table, assigned priorities determine the "winner". (Custom resolution solutions can also be implemented.) It is possible to view all the rows involved with the conflict.
Replication Models:
Central Publisher/Distributor: Both the Publisher and the Distributor are on the same machine with Subscribers on separate servers.
Central Publisher with Remote Distributor: Like above, except Publisher and Distributor are on different machines. In heavy OLTP environments, this scenario is useful in that it reduces the load on the Publisher. Requires good network connectivity between Publisher and Distributor.
Publishing Subscriber: In this scenario, the Subscriber is also responsible for republishing the received data. Useful in situations where there is low available bandwidth between locations. For example, you have a slow link between Vancouver and Hong Kong. The Subscriber in Hong Kong would republish the received data to Canberra, Sydney, Bangkok.
Central Subscribers/Multiple Publishers: A number of publishers replicate data to a common destination table on the subscriber. The data has to be partitioned and a primary key used to identify the source region/server. Useful for rolling up information.
Multiple Publishers/Multiple Subscribers: Each replicates information to and receives replicated information from a common table. Useful for situations where sites have to be able to view information updated in other sites.
Any replication type can be used with any model.
The model is simply the physical topology of your replication.
Installing and Configuring Replication:
In order to set up replication, you must first create a distributor. You should use the replication wizards to install and configure replication. Must be a member of sysadmin role to initialize DB for publication; DBO can then create and modify publications.
Some Planning Considerations:
Depending on the type of replication you are performing some data types must be present or absent. If you are bypassing the initial snapshot replication (doing a manual snapshot by restoring a database backup, for example), you will have to add these datatypes manually at the destination database.
Timestamp (not related to date and time) data type must be present for immediate updating options. It must be removed for merge replication.
Uniqueidentifier data type with the ROWGUIDCOL property must be present for merge replication. It will be added automatically if it is not present.
Text, ntext, and image datatypes can be replicated only with snapshot replication. (It is possible to control the maximum size of these datatypes that will replicate with snapshot replication.)
The replication process will not replicate the IDENTITY property on a column, but will replicate values in the column. If the property itself were replicated, values might be reseeded at the subscriber.
The NOT FOR REPLICATION property is useful in partitioned environments that are using the identity property on columns. This option allows control over the range of values for seeded identity values.
Ensure adequate diskspace exists at the distributor for snapshot or transactional replication. If using push subscriptions, ensure that distributor can handle extra load; otherwise, use pull or anonymous subscriptions.
If using transactional replication and a very large number of rows are affected by a transaction, consider replicating a stored procedure instead (be careful with this kind of replication, since transactional integrity could be compromised).
For fault tolerance, do backups and create scripts based on replication configuration.
|
Restore
Master Database
|
If you can still start SQL:
Stop SQL Services
Restart SQL in single user mode from a command prompt (SQLSERVR.EXE -m)
Restore Master DB using EM or T-SQL
Stop SQL and restart SQL Services
If SQL Doesn't Start:
Run REBUILDM.EXE from mssql7\binn directory
Select sort order, character set, and Unicode collation (you will need the SQL 7.0 source files).
New files for master, msdb, model, pubs, and northwind are copied to installation.
Restore Master from backup; then restore model, msdb, and distribution database (if distribution server).
If no valid copy backup copy of master exists, you must recreate logins, references to databases, etc., in the master. You can use scripts which you have previously generated or
EM. You must then restore databases from backup or use sp_attach_db if the database files are present. The latter is more efficient.
|
Restoring
a Database
|
Specify NORECOVERY on all backups except for the last backup then use
Specify STANDBY to allow access and to restore additional transaction logs
RESTORE DATABASE MyNwind FROM MyNwind_1
RESTORE HEADERONLY Returns header information of backup file
RESTORE FILELISTONLY Returns information about original database or transaction log
RESTORE LABELONLY Returns information about the backup media
RESTORE VERIFYONLY Verifies backup files (complete and readable)
Recovering Master Database:
Assuming you can still start SQL,
· Stop SQL Services
· Restart SQL in single user mode from a command prompt (SQLSERVR.EXE -m)
· Restore Master DB using EM or T-SQL
· Stop SQL and restart SQL Services
Recovering Master DB when SQL Doesn't Start (Rebuilding Master DB):
· Run REBUILDM.EXE from mssql7\binn directory
· Select sort order, character set, and Unicode collation (you will need the SQL 7.0 source files). New files for master, msdb, model, pubs, and northwind are copied to installation.
· Restore Master from backup; then restore model, msdb, and distribution database (if distribution server).
· If no valid copy backup copy of master exists, you must recreate logins, references to databases, etc., in the master. You can use scripts which you have previously generated or EM. You must then restore databases from backup or use sp_attach_db if the database files are present. The latter is more efficient.
|
|
|
|
|
