1. There are 14 73G disks in the disk cabinet, and the database size is 200G, including log files. How to set up a disk (explain how to use the 14 disk)?
2. There are two server clusters, namely node 1 and node2. Now we need to patch win200 system. After patching, we need to restart it. No matter how to patch it, it can't affect users' use (please specify it in cluster terms).
3. there is a database a, which is copied to b and C B respectively. Every data update requires simultaneous updating, and C only needs to be updated once a day. How to make a replication strategy!
4. There is a sequential table with 90 fields, 20 indexes and 15 composite indexes, among which 3 index fields exceed 10. How to optimize it?
5. There is a database with a size of 200G, which is increased by 50M every day, so that users can access it at any time and make a backup strategy (detailed description).
6. Manage 50 databases, and check whether the database work is completed every day. How do you complete this check?
7. What is the difference between a user-defined function and a stored procedure? When can I only use one custom function and one stored procedure?
8.8 What's new? SQL 2005？ What's the difference with Oracle Bone Inscriptions?
9. What qualities should a DBA have, what do you have and what are your shortcomings?
1. There are 14 73G disks in the disk cabinet, and the database size is 200G, including log files. How to set up a disk (explain how to use the 14 disk)?
This problem should be to examine the hardware knowledge and the physical deployment of the database.
First, you need to know whether these disks will be used to store database backup files and database performance (read/write) requirements. To determine the level of raid.
1), if performance is emphasized and database backup files do not need to be stored, consider using raid0+ 1, so that the available disk capacity is:14 * 73 * 50% = 511g.
2) If the reading and writing performance requirements are not high, and you are stingy, you can consider raid5, then the available disk capacity is: 13*73=949G.
As for how to use it, it should be the deployment of database physical files. Note that tempdb, datafile and logfile are stored separately to reduce I/O competition. In fact, now the striped disk will automatically share files, and manual distribution is becoming less and less important.
2. There are two server clusters, namely node 1 and node2. Now we need to patch win200 system. After patching, we need to restart it. No matter how to patch it, it can't affect users' use (please specify it in cluster terms).
This specific operation is a little forgotten. Generally speaking, first look at which node is in use, access another idle node through node IP (private), patch it, and then stop the node in the cluster manager (or use the command line) and restart it. After the startup is completed, the node will be switched and patched for another node. Then restart.
3. There is a database A, which is copied to B and C B respectively. Every data update requires simultaneous updating, and C only needs to be updated once a day. How to make a replication strategy!
This should be about copying knowledge.
a-& gt； b
1), if SQL Server copy function is used, then let a->; Use transactional replication (synchronous replication).
2) If there are not many tables, you can also write your own triggers and use LinkServer+Distribute Transaction.
a-& gt； c
1), if SQL Server copy function is used, then let a->; B. Use snapshot replication to make one-time replication at a certain point in time.
2) You can also write your own bat. After backing up A, you can call the backup media via ftp to restore C. (It is troublesome and not recommended).
4. There is a sequential table with 90 fields, 20 indexes and 15 composite indexes, among which 3 index fields exceed 10. How to optimize it?
This question is not level. You don't elaborate on how this table is used (reading and writing, or almost static), just ask others how to optimize it. ! ! Why don't you ask the principle of index distributed access?
I can see that he wants you to say: those three indexes exceed 10, and the traversal efficiency of B-tree is very low, so reduce the number of fields appropriately. If it is SQL2005, you can put the fields with poor selectivity in the "index additional fields" to ensure the index coverage. And because of the lock upgrade problem, SQL Server can consider disassembling the table.
5. There is a database with a size of 200G, which is increased by 50M every day, so that users can access it at any time and make a backup strategy (detailed description).
In this case, an incremental backup can be used. Make a full backup every Sunday, and make incremental backups from Monday to Saturday (because of the small amount of data, you can consider making incremental backups every 30 minutes). This can minimize performance consumption, and if the transaction log is lost, it can guarantee up to 30 minutes of data loss.
6. Manage 50 databases, and check whether the database work is completed every day. How do you complete this check?
This is relatively simple. Establish linkserver on each machine, and then create a distributed view on the DBA management server. Every time you query this view, you can see the job situation on each machine at a glance. Distributed view writing:
Create View Public _ Job
be like
Select "machine one" from linkserver 1 as the name *..sysjobactivity.
Joint ownership
Select' Machine 2' as the name *...sysjobactivity from linked server 2.
Joint ownership
Select "machine three" as the name *...sysjobactivity from linkserver3.
7. What is the difference between a user-defined function and a stored procedure? When can I only use one custom function and one stored procedure?
This should be the experience of writing stored procedures. Universal custom functions are mainly used for calls in other sql, such as:
Choose your func (...) from the table.
In this case, it can only be realized by functions.
Stored procedures are far more powerful than functions, such as using dynamic execution sql(sp_executesql) and some special functions, which are not supported by user-defined functions and can only be realized through stored procedures.
8.8 What's new? SQL 2005？ What's the difference with Oracle Bone Inscriptions?
The new features of SQL 2005 are generally learned from Oracle.