sql server audit specification

----------------------------------------- DECLARECur_columnCURSOR for SELECTNAME fromsyscolumnsWHEREId= object_id(@tablename) OPENCur_columnFETCH NEXT fromCur_column into @columnname while @ @fetch_status = 0 BEGIN DECLARE @columnnamefirstchar CHAR(1), @tablenamefirstchar CHAR(1), @ch VARCHAR( -), @ch1 VARCHAR( -), @uppertablename

Save the SQL Server Default trace file for more than half a year based on the level of protection Project SQL Server audit requirements. Solution Solutionsfor users, Default Trace you can only turn off or turn on the trace, and you cannot modify any parameters. Therefore, we

is the T-SQL code that enables auditing: Use masterCREATE SERVER AUDIT audit1 to FILE (filepath= ' D:\SQLAudit ')Use AdventureWorks2012CREATE DATABASE AUDIT specification serialpic for SERVER

Tags: SQL Server audit CDCIntroductionSQL Server 2008 introduces CDC (Change Data Capture), which can record:1. What data lines have changed2. The history of data row changes, not just the final value.It implements asynchronous change tracking (like transactional replication) through the job, compared to the Tracking C

JobWhen CDC is first enabled in the specified database and there is no transactional replication, two jobs are created for capture and cleanup:The capture job is used to scan the log files and write the change records to the change table. Called Sp_mscdc_capture_job, the scan parameters and scan intervals can be set based on the actual transaction throughput of the current library, resulting in a reasonable balance between performance overhead and tracking requirements.The cleanup job is to cle

Label:Original: Use SQL Server audit to monitor the activation and disabling of triggersUsage scenarios:Sometimes it is found that the business logic in the trigger is not executed, possibly due to a logic error in the trigger. But sometimes it is because some triggers are disabled.Because SQL

method, except that the selected object can only be a user, database role, or application role.650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/79/6C/wKiom1aQpcKQjDkvAAAhTkB7nmw492.png "title=" Select the principal name. png "alt=" Wkiom1aqpckqjdkvaaahtkb7nmw492.png "/>When you are finished, click the OK button.Examples of T-SQL syntax are: Use [SQLDB01]GOCREATE DATABASE AUDIT

Label:Original: SQL Server audit feature Getting Started: CDC (change Data Capture)IntroductionSQL Server 2008 introduces CDC (Change Data Capture), which can record:1. What data lines have changed2. The history of data row changes, not just the final value.It implements asynchronous change tracking (like transactional

the temporary table is sound and reliable, but the statistics on the table variable are unreliable; 5) There is a locking mechanism in the temporal table, and there is no mechanism for locking in the table variable. Selection of temporary tables and table variables: 1) The main thing to consider when using table variables is the pressure on the memory of the application, and if the code runs a lot of instances, pay special attention to memory variables ' memory consumption. We use table variabl

index of the table is the primary key column, the name of the primary key has already been mentioned before, and the index name is the same as the primary key name (pk_ table name) For nonclustered indexes on a table, it is recommended to use (ix0_ table name, ix1_ table name) ...., so go on, so also very clearly expressed the index, for many named articles mentioned in the need for detailed expression of specific columns, I personally feel no need, first clustered index often involves mult

deferred to read the corresponding to the writing and reading library • Read/write separation should consider automatically switch to write-side schema decoupling when read is not availableProhibit cross-Library joinData life cycleArchiving of large-scale periodic sub-libraries based on the frequency of use of dataPhysical separation of the main library/archive libraryTable of log type should be partitioned or divided into tablesFor large tables to be partitioned, partition operations divide ta

Common SQL Server Specification highlightsCommon field type selection1. Varchar/nvarchar data types are recommended for character types2. Money data type is recommended for monetary value3. Scientific counting recommended numeric data type4. Self-growth logo recommended to use the bigint data type (a large amount of data, with the type of int will not fit, then t

of reading features read into real-time read and can be deferred to read the corresponding to the writing and reading library • Read/write separation should consider automatically switch to write-side schema decoupling when read is not availableProhibit cross-Library joinData life cycleArchiving of large-scale periodic sub-libraries based on the frequency of use of dataPhysical separation of the main library/archive libraryTable of log type should be partitioned or divided into tablesFor large

with a join relationship, the field must indicate which table the field belongs to, or the program compatibility error of ambiguous column name may occur after a future table structure changeArchitecture Design Read/write separation Schema decoupling Data life cycle Read/write separation At the beginning of the design consider read and write separation, even read and write the same library, facilitate rapid expansion Read into real-time reading and deferred re

the same library, to facilitate rapid expansion of reading features read into real-time read and can be deferred to read the corresponding to the writing and reading library • Read/write separation should consider automatically switch to write-side schema decoupling when read is not availableProhibit cross-Library joinData life cycleArchiving of large-scale periodic sub-libraries based on the frequency of use of data Physical separation of the main library/archive libraryTable of log type shoul

Insert into dbo. TableA (id,name) Select1, ' AA ' Insert into dbo. TableA (id,name) Select2, ' BB ' Suggested wording : Insert into dbo. TableA (id,name) Select1, ' AA ' Unionall Select2, ' BB ' Suggested notation 2: Insert into Dbo.tablea (id,name) Values (1, ' AA '), (2, ' BBB ') Such as: General wording : UpdateaSetA.userno=A.userno+1 fromdbo. TableA aWhereA.username=' Zhang San 'Go UpdateaSetA.userno=A.userno+1 fromdbo. TableA aWhereA.username=' John Doe ' Suggested wording Update a

the ID in the nonclustered index), and find out all the paging data by ID Re-association (find all the data through the clustered index) IV. Maintenance Specifications Production libraries use full mode, test, development library available in simple mode Operation log table, ID usually only the key, no use. You can use the field "Addtime" as a clustered index field and a partition field. Each time you modify a function, stored procedure, and so on, back up the old copy and record the modifica

index of the table is the primary key column, the name of the primary key has already been mentioned before, and the index name is the same as the primary key name (pk_ table name) For nonclustered indexes on a table, it is recommended to use (ix0_ table name, ix1_ table name) ...., so go on, so also very clearly expressed the index, for many named articles mentioned in the need for detailed expression of specific columns, I personally feel no need, first clustered index often involves mult

Tags: output CTI BSS date IDT NiO tor Access CollectionCommon SQL Server Specification highlights Source: Jing Yi Links: www.cnblogs.com/liyunhua/p/4534442.html Common field type selection 1. Varchar/nvarchar data types are recommended for character types 2. Money data type is recommended for monetary value 3. Scientific counting recommend

single sheet——— Scripting ————— –In scripting, it is forbidden to perform complex calculationsIn scripting, avoid using SELECT *: Reduce the number of queries and unnecessary returnsIn scripting, it is forbidden to perform calculations on indexed columnsUse fewer cursorsUse fewer triggersIn scripting, the parameter types and column types in the table need to be consistentscripting, avoid splicing SQL, using parametric processingIn scripting, minimize