sql server dba checklist

As a database administrator for billing applications (DBA), the company's chief information Officer (CIO) often invites me to meet with Sarbanes-oxley reviewers to discuss the security and consolidation of corporate data. Simply put, he wants us to answer a number of questions through the right documents: who has access to our data, how to authorize access, and how we monitor access to prevent someone from invading, logging into data, or doing somethi

Tags: DBA file data restore temp table begin SEL Cyclic taint adaptationOriginal: A lightweight backup restore operation that uses database snapshots in SQL Server to complete a database in a test environment In the development or testing environment of the database, often found that there are developers or testers mistakenly delete the table or data situation,fo

Label:Tags: SQL server/mssql server/database/dba/failback/masterOverviewSQL Server maintains a set of system-level databases, called system databases, that are critical to the operation of the server instance. You must back up mul

words, is the partition 3 merged into Partition 4 or partition 4 merged into Partition 3? This needs to see if our partitioning function defines left or right. If you define a ieft. The partition 3 on the other side is merged into Partition 4 on the other side. Instead, the partition 4 is merged into Partition 3:Summary This article explains how partitioned tables are used in SQL Server. Partitioned

Tags: add cannot mat varchar create clean alt get scriptWith the progress of the Times, the development of society, all kinds of technology endless variety of chaotic countless (writing!!!) ） No nonsense, in short, many companies will use a variety of databases at the same time, so the data in the import and export between different databases become an egg pain problem, for the requirements of the cycle line, you can develop a special program processing, but for the occasional uncertainty of dem

How to solve the problem of invalid isolated users and object names 'xxx' caused by SQL Server database backup and Restoration Time : 2006-11-24 11:36:08 Source : Csdn Author : In the process of using the database, zhiguang often encounters database migration or data migration problems, or sudden database damage. In this case, you need to directly recover from the database backup. However, a problem may

wait statistics 5.1 Batch requests/sec;sql Compilations/sec;sql re-compilations/sec The number of requests, compilations, and recompilation per second that are optimized for reference. 5.2 Lock waits the number of processes waiting for the lock. 5.3 Log write waits number of processes waiting to be written to the log cache The 5.4 page IO latch waits statistics related to page I/O latches. Used to determin

Label:In SQL Server 2005/2008/2008 R2, we can use SQL Server's own pre-editing 200-line feature, which is intended to simplify the use of update, allowing developers, DBAs and some people who do not understand T-SQL are able to directly modify data in the database through a visual interface.In the use of the first 200

related to clearing the cache:Apart from dbcc unpintable in SQL 2000, it seems that this operation does not immediately release the table memory Buffer.(Dbcc unpintable does not cause the table to be immediately flushed from the data cache. it specifies that all of the pages for the table in the buffer cache can be flushed if space is needed to read in a new page from disk .) SQL 2005/2008 allows

fixed server roles:Described in the order of the lowest-level roles (bulkadmin) to the highest level of the role (sysadmin):Bulkadmin: Members of this server role can run BULK INSERT statements. This statement allows data to be imported from a text file into a SQL Server 2008 database and is designed for domain account

than the number of rows of data The statements and data used in the example are used only as demos, the actual development application is more complex than the sample data, the same query in different circumstances may produce the opposite result, how to apply well is mainly in our personal understanding and understanding, hope to see this article friends can deepen some understanding of the index and understanding , and get rid of the error of index and develop high performance application.

, SQL Server discards the use of Fields that are not suitable for index creation: Gender, 0/1, True/false Fields suitable for index creation: ORDERID, UID, etc. 　Take advantage of unique indexes　　A unique index provides SQL Server with information that ensures that a column has absolutely no duplicate valu

clause operator =====- - operator description operator Description -- = equal to > greater than - - -- ! = not equal to!> -- - - -- ! 4.2.1 Checking a single valueList all items priced at less than $10:SELECT Prod_name, Prod_price from Products WHERE ten;　　Retrieve all products with a price less than or equal to $10: (Result same)SELECT Prod_name, Prod_price from Products WHERE ten;4.2.2 Mismatch

fact tables of the data warehouse usually have one or several hot partitions, The data in these partitions is often updated, and the compression options can be turned off to prevent data compression from causing additional processing load to the data updates on these partitions.CREATE TABLE PartitionedTable (col1 int, col2 varchar(200)) ON PS1 (col1) WITH ( DATA_COMPRESSION = ROW ON PARTITIONS(1), DATA_COMPRESSION = PAGE ON PARTITION(2 TO 4)); If you are setting compression options for an inde

it carefully, triggers can help us deal with some fixed rules of incremental data things, oh yes, SQL Server does not use things very much, but this is common in Oracle. Of course, when it comes to data operations, sometimes triggers are not good if they don't understand.SQL Server I have not seen using cursors, perhaps you would be surprised, when writing code

a temporary solution. Same, in the final analysis, because there are many problems in the code and even database design, such as missing index, query performance in the transaction and logical order problems, T-SQL statement performance and so on. For some of the years to solve similar problems of the DBA is not valuable, but for people who do not understand the database can still temporarily solve some ur

automatically updated when the critical value is reached. This standard is: When inserting data to an empty table, the number of rows in the table with less than 500 rows is increased by 500 or more. When the number of rows in the table is greater than 500, the amount of data changed is greater than 20%. If the preceding conditions are met, the statistics are updated. Of course, you can also use the following statement to manually update statistics. Update statistics table name [index name] Co

create the index: ORDERID, Full use of unique indexes such as UIDA unique index provides SQL Server with information that ensures that a column has absolutely no duplicate values, and when Query Analyzer finds a record through a unique index, it exits immediately without continuing to look up the indexTable index number not exceeding 6There are no more than 6 table indexes (this rule was developed by Ctrip

unique index provides SQL Server with information that ensures that a column has absolutely no duplicate values, and when Query Analyzer finds a record through a unique index, it exits immediately without continuing to look up the indexTable index number not exceeding 6There are no more than 6 table indexes (this rule was developed by Ctrip's DBA after a trial .

Server discards the use of Fields that are not suitable for index creation: Gender, 0/1, True/false Fields suitable for index creation: ORDERID, UID, etc. Take advantage of unique indexesA unique index provides SQL Server with information that ensures that a column has absolutely no duplicate values, and when Query Analyzer finds a record through a