Become an Exceptional DBA and Database developer
Many Database admin and developers want to have good exposure and want to do best practices in MSSQL databases, and from the beginning many people put themselves as a DBA. What expertise and skills need to be learned by DBAs? I’m the following
I solved some 30 questions about DBA while doing DBA work and interviewing DBA, and there are several small questions among the 30 big questions, including the more technical knowledge points of SQL Server 2012,SQL Server 2014 training, and share them with you.
Next, I would like to share some points to you if you want to become DBA:
1. The difference between char, varchar, nvarchar (including use and space occupation); what are the xml style methods for finding node data that are more efficient; how to use storage?
What is the distinction between the method and the use of T-SQL for querying data?
2. What are the machine DBs, what are their tasks, why; how to restore them if they are destroyed, do they need to be backed up? (mainly the master library)
3. What operations TempDB can use; what are the potential reasons and how to deal with them if TempDB becomes abnormally large;
4. What are the forms of indexes, what are their differences and principles of implementation, what are the advantages and disadvantages of indexes; how to create suitable indexes for SQL statements, what things need to be paid attention to when creating an index, and how to check whether the index you created was used; how to hold the index; how to check the damage to the index and how to repair it; What could be the reason?
5. Can we build an index on a view, and what are the advantages and disadvantages if it can be constructed? What is the difference between constructing a view index and constructing a table index?
6. From which tables, job information can be obtained; which views can be obtained by which statements are running in the system; how to obtain information such as IO and T-SQL statement time;
7. A table has 50 million documents on the online system. Now, you need to import 20 million records from another server into a table. You need to remove the 20 million data after the export. What are the benefits and drawbacks of coping with it?
8. The database server states that there is insufficient disk space, how you can handle it, and asks for recovery as soon as possible;
9. What are the differences and connections between temporary tables, table variables, and CTE (Common Table Expressions), what are the differences in storage location, and how to decide which one to choose when using;
10. What are the SQLServer isolation levels and which is the default level; what are the database’s key lock types; how to enforce row version control;
11. How to track deadlock; how to track and locate blocking; how to deal with the troublesome phrase after figuring out; what activities we normally need to track while using Profile to track;
12. What kinds of Windows logs are the key ones? How many training logs for SQL Server are normally kept? In what circumstances will new SQL logs be generated; what are the modes of recovery of the database log and what are the differences? Suddenly, the database log becomes very large. And what are the potential causes, how to find the reasons, and how to deal with them respectively, you can not shrink;
13. What is the partition table and partition view definition, under what circumstances are they commonly used, and what are the advantages?
14. How to compare the difference between the data of two tables with the same structure; how to fix it if the table is corrupted; how to restore the data as much as possible when there is a backup file problem; how to reset the table’s Identity property to zero;
15. What is the use of DDL Trigger and DML Trigger, and what is the difference between CheckPoint and LazyWriter, and what is the difference?
16. The Mirroring and Logshipping gap and use scenarios; the Mirroring of SQLServer is similar to the technology of Oracle, and what the difference between them is;
17. Mirroring construction phases, the distinction between the three Mirroring modes, synchronization concepts and specifications and asynchronous in Mirroring, what to do with database logs after the construction of Mirroring;
18. Replication configuration and use scenarios; what are the replication modes; what is the difference between PUSH and PULL; what library is produced after replication is created; what is the specific error statement when an error is recorded, and what statement is cleaned up when using Replication in a library; see which tables are used primarily for synchronization chain information;
19. Will the table on the publishing side of replication truncate, and why? How to handle the Replication Identity column, how to handle the missing field error, how to handle the primary key dispute error, how to skip the specified error, how to handle the subscription table deletion, and how to handle the large-scale data change How to handle it; because a certain table in a synchronization chain changes data at a time, the synchronization chain is seriously de-synchronized. What it needs to be dealt with.
20. Usage scenarios of SSB (Service Broker), how to build, what objects will be created, what are the advantages and disadvantages, and the key ways to transfer messages between different servers; what methods can be used to solve problems;
21. What are the strategies for monitoring changes in database data and their benefits and drawbacks (CDC (Change Data Capture), CT (Change Tracking), Cause, etc.)?
22. SQL tuning steps, how to assess the SQL statement problem, how to locate the issue, how to solve these issues;
23. Troubleshooting measures for databases, how to deal with emergency database issues;
24. How a database backup strategy should be considered and formulated; the organization wants to back up a very large database or table and allows the amount of data to be lost as little as possible, what system you might use;
25. If you want to track your database, pay attention to the metrics (including SQLServer and OCP training), how to establish the performance baseline, and what tracking tools you have used.
26. Database migration steps; rebuilding a relatively large test system (at least 10 instances of a database), if the original DB data volume is not large, but the DB is relatively large, the newly developed system data is not required, how to implement it quickly;
27. The short steps needed to build a cluster, at least a few IPs, what services need to be installed, what fixed disks need to be installed, how to set up Raid, how to break disks, the difference between SQLServerCluster, Mysql Cluster and Oracle RAC, etc.
28. It is hard to solve the problem by database tuning if you encounter a stored procedure with unsatisfactory output and complex code. How do you convince developers to change it (developers may not be prepared to modify it)?
29. Have you ever experienced a device failure due to your misoperation, how did you handle it at the time; if not, assuming by accident that you removed a significant chart, how do you handle it;
30. What kind of DBA education are you going to become and how are you going to prepare for it (or what career plan do you have, how are you going to spend your DBA career); what do you want most if you join the company?
I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.