SQL 2005SP4
|
SQL2012SP1
| |
USE [master]
GO CREATE LOGIN [donghua] WITH PASSWORD=N'p_ssw0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @loginame = N'donghua', @rolename = N'sysadmin' GO |
USE [master]
GO CREATE LOGIN [donghua] WITH PASSWORD=N'p_ssw0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [donghua] GO | |
create database TestDb;
go use TestDb; go create table t(c1 varchar(20)); insert into t values(getdate()); go select * from t; | ||
Backup & Restoration
| ||
Set Full recovery model
|
USE master;
GO ALTER DATABASE TestDB SET RECOVERY FULL; GO | |
Backup Database
|
BACKUP DATABASE TestDb
TO DISK = 'C:\Backup\TestDb_Full.bak' WITH FORMAT GO |
Copy the backup to this Server
|
Restore Database
|
RESTORE DATABASE TestDb
FROM DISK='C:\backup\TestDb_Full.bak' WITH NORECOVERY, MOVE 'TestDb' TO 'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb.mdf', MOVE 'TestDb_log' TO 'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb_log.LDF'; GO | |
Backup Log
|
BACKUP LOG TestDb
TO DISK = 'C:\Backup\TestDb_Log.bak' WITH FORMAT GO |
Copy the backup to this Server
|
RESTORE LOG TestDb
FROM DISK = 'C:\Backup\TestDb_Log.bak' WITH NORECOVERY GO | ||
The two server instances run in nontrusted Windows domains, so certificate-based authentication is required
| ||
On the master database, create the database master key, if needed.
|
USE master
GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!QAZXSW#$%e'; GO |
USE master
GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '093j45&*he'; GO |
Make a certificate for this server instance.
|
USE master;
GO CREATE CERTIFICATE SQL2005_cert WITH SUBJECT = 'SQL2005 certificate', START_DATE = '20121031', EXPIRY_DATE = '20151031'; GO |
USE master;
GO CREATE CERTIFICATE SQL2012_cert WITH SUBJECT = 'SQL2012 certificate', START_DATE = '20121031', EXPIRY_DATE = '20151031'; GO |
Create a mirroring endpoint for server instance using the certificate.
|
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED AS TCP ( LISTENER_PORT=7024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SQL2005_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO |
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED AS TCP ( LISTENER_PORT=7024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SQL2012_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO |
Back up the HOST_A certificate, and copy it to other system, HOST_B.
|
BACKUP CERTIFICATE SQL2005_cert TO FILE = 'C:\Backup\SQL2005_cert.cer';
GO |
BACKUP CERTIFICATE SQL2012_cert TO FILE = 'C:\Backup\SQL2012_cert.cer';
GO |
Setup inbound connection
| ||
Create a login on HOST_A for HOST_B.
|
USE master;
CREATE LOGIN sql2012_login WITH PASSWORD = '1Sample_Strong_Password!!#'; GO |
USE master;
CREATE LOGIN sql2005_login WITH PASSWORD = '2Sample_Strong_Password!!#'; GO |
Create a user for that login.
|
CREATE USER sql2012_user FOR LOGIN sql2012_login;
GO |
CREATE USER sql2005_user FOR LOGIN sql2005_login;
GO |
Associate the certificate with the user.
|
CREATE CERTIFICATE SQL2012_cert
AUTHORIZATION sql2012_user FROM FILE = 'C:\backup\SQL2012_cert.cer' GO |
CREATE CERTIFICATE SQL2012_cert
AUTHORIZATION sql2012_user FROM FILE = 'C:\backup\SQL2012_cert.cer' GO |
Grant CONNECT permission on the login for the remote mirroring endpoint.
|
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sql2012_login];
GO |
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sql2012_login];
GO |
Setup mirror database
| ||
On the mirror server instance on HOST_B, set the server instance on HOST_A as the partner (making it the initial principal server instance).
|
--At HOST_B, set server instance on HOST_A as partner (principal server):
ALTER DATABASE TestDb SET PARTNER = 'TCP://WIN2003SQL2005:7024'; GO | |
On the principal server instance on HOST_A, set the server instance on HOST_B as the partner (making it the initial mirror server instance)
|
--At HOST_A, set server instance on HOST_B as partner (mirror server).
ALTER DATABASE TestDb SET PARTNER = 'TCP://WIN-HN1Q39OK9JF:7024'; GO | |
This example assumes that the session will be running in high-performance mode. To configure this session for high-performance mode, on the principal server instance (on HOST_A), set transaction safety to OFF.
|
--Change to high-performance mode by turning off transacton safety. ALTER DATABASE TestDb
set partner SAFETY off GO | |
Setup mirror database
| ||
Create database snapshot
|
CREATE DATABASE TestDb_SS ON
( NAME = TestDb, FILENAME = 'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb.ss' ) AS SNAPSHOT OF TestDb; GO Msg 946, Level 14, State 1, Line 1 Cannot open database 'TestDb_SS' version 611. Upgrade the database to the latest version. Msg 1823, Level 16, State 7, Line 1 A database snapshot cannot be created because it failed to start. | |
Perform insert in SQL2005
|
use testdb
go insert into t values(getdate()); go | |
Manual Failover
|
USE master
go ALTER DATABASE TestDb SET PARTNER FAILOVER go Msg 1477, Level 16, State 1, Line 1 The database mirroring safety level must be FULL to manually failover database "TestDb". Set safety level to FULL and retry. | |
Force Service in a Database Mirroring Session
|
use master;
go ALTER DATABASE TestDb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS go Msg 1455, Level 16, State 106, Line 1 The database mirroring service cannot be forced for database "TestDb" because the database is not in the correct state to become the principal database. | |
Use the restore command to open the DB
|
Use master
go ALTER DATABASE TestDb SET PARTNER OFF go RESTORE DATABASE TestDb WITH RECOVERY go -- Data is in-sync | |
Friday, May 24, 2013
Setup SQL Database Mirroring using T-SQL between SQL2005 to SQL2012
Wednesday, December 12, 2012
Quick way to add iscsi device in Solaris 10
bash-3.2# iscsiadm modify discovery --static enable
bash-3.2# iscsiadm list discovery
Discovery:
Static: enabled
Send Targets: disabled
iSNS: disabled
bash-3.2# iscsiadm add discovery-address 192.168.214.200:3260
bash-3.2# iscsiadm list discovery-address -v;
Discovery Address: 192.168.214.200:3260
Target name: iqn.2011-03.example.org.istgt:freenas
Target address: 192.168.214.200:3260, 1
bash-3.2# iscsiadm add static-config iqn.2011-03.example.org.istgt:freenas,192.168.214.200:3260
bash-3.2# format
Searching for disks...done
AVAILABLE DISK SELECTIONS:
0. c1t0d0
/pci@0,0/pci15ad,1976@10/sd@0,0
1. c2t2d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,0
2. c2t3d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,1
3. c2t4d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,2
4. c2t5d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,3
5. c2t6d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,4
Specify disk (enter its number):
Reference: http://sys-admin.wikidot.com/iscsi-solaris
bash-3.2# iscsiadm list discovery
Discovery:
Static: enabled
Send Targets: disabled
iSNS: disabled
bash-3.2# iscsiadm add discovery-address 192.168.214.200:3260
bash-3.2# iscsiadm list discovery-address -v;
Discovery Address: 192.168.214.200:3260
Target name: iqn.2011-03.example.org.istgt:freenas
Target address: 192.168.214.200:3260, 1
bash-3.2# iscsiadm add static-config iqn.2011-03.example.org.istgt:freenas,192.168.214.200:3260
bash-3.2# format
Searching for disks...done
AVAILABLE DISK SELECTIONS:
0. c1t0d0
/pci@0,0/pci15ad,1976@10/sd@0,0
1. c2t2d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,0
2. c2t3d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,1
3. c2t4d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,2
4. c2t5d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,3
5. c2t6d0
/iscsi/disk@0000iqn.2011-03.example.org.istgt%3Afreenas0001,4
Specify disk (enter its number):
Reference: http://sys-admin.wikidot.com/iscsi-solaris
Wednesday, August 15, 2012
Where DEK in SQL TDE stored, and when it created
It's stored in the boot record (page 0 of file 1) of encrypted database. It created when database encryption turn on.
Below is the testing scenarios.
CREATE DATABASE [TDE]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TDE', FILENAME = N'C:\SQL2012\MSSQL11.PDB\MSSQL\DATA\TDE.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TDE_log', FILENAME = N'C:\SQL2012\MSSQL11.PDB\MSSQL\DATA\TDE_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
DBCC TRACEON(3604)
DBCC PAGE (TDE, 1, 0, 3);
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:0)
BUFFER:
BUF @0x0000000003BE0E40
bpage = 0x000000016FCFC000 bhash = 0x0000000000000000 bpageno = (1:0)
bdbid = 8 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 2526 bstat = 0x9
blog = 0x1215a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000016FCFC000
m_pageId = (1:0) m_headerVersion = 1 m_type = 15
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 7029 m_freeData = 1161
m_reservedCnt = 0 m_lsn = (0:0:1) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -734960185
DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
File Header Data:
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065
Memory Dump @0x000000003C15A060
0000000000000000: 30000800 00000000 31000000 00000000 002e007f 0.......1...........
0000000000000014: 007f0081 00830087 008b008f 0093009d 00a700b1 .....ƒ.‡.‹...“...§.±
0000000000000028: 00b100b5 00b900bd 00c100cb 00e700f1 00fb0005 .±.µ.¹.½.Á.Ë.ç.ñ.û..
000000000000003C: 0115011f 012f0133 013d013d 01430153 01530153 ...../.3.=.=.C.S.S.S
0000000000000050: 01530153 01530153 01630163 0163016d 01770193 .S.S.S.S.c.c.c.m.w.“
0000000000000064: 019d01ad 01c901d1 012904c2 98df59fe efd44db3 ....É.Ñ.).Â.ßYþïÔM³
0000000000000078: d0b304a0 913a1301 00010080 010000ff ffffff80 г. ‘:.........ÿÿÿÿ.
000000000000008C: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000A0: 00000000 00000000 00000000 00000000 00800100 ....................
00000000000000B4: 00000000 00ffffff ff000200 00000000 00000000 .....ÿÿÿÿ...........
00000000000000C8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000DC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000F0: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000104: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000118: 00000000 0000004d c664e895 a00048a9 77bd01c9 .......MÆdè• .H©w½.É
000000000000012C: 93d0aa00 00000000 00000000 00000000 00540044 “Ъ..............T.D
0000000000000140: 00450000 00000000 00000000 00000000 00000000 .E..................
0000000000000154: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000168: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000017C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000190: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001A4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001B8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001CC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001E0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001F4: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000208: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000021C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000230: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000244: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000258: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000026C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000280: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000294: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002A8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002BC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002D0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002E4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002F8: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000030C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000320: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000334: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000348: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000035C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000370: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000384: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000398: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003AC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003C0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003D4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003E8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003FC: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000410: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000424: 00000000 00 .....
BindingID = 59df98c2-effe-4dd4-b3d0-b304a0913a13 FileIdProp = 1
FileGroupId = 1 Size = 384 MaxSize = 65535
Growth = 128 Perf = 0 BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL]
MinSize = 384 Status = 0 UserShrinkSize = 65535
SectorSize = 512 MaxLsn = (0:0:0) FirstLsn = (0:0:0)
CreateLsn = (0:0:0) DifferentialBaseLsn = (0:0:0)
DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000 FileOfflineLsn = (0:0:0)
FileIdGuid = e864c64d-a095-4800-a977-bd01c993d0aa RestoreStatus = 0
RestoreRedoStartLsn = (0:0:0) RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL]
FstrContainerSize = [NULL] MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0) RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000
RestorePathOrigin
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
DatabaseEncryptionFileState.m_maxScannedPage = 0 DatabaseEncryptionFileState.m_keyId = 0
FCBFileDEK
m_dbeStatusBits = 0 m_dtCreated = 1900-01-01 00:00:00.000
m_dtLastRegenerated = 1900-01-01 00:00:00.000
m_dtLastModified = 1900-01-01 00:00:00.000 m_dtLastSet = 1900-01-01 00:00:00.000
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 0 m_algId = 0
m_dwBitLen = 0 m_cbThumbprint = 0 m_rgbThumbprint = 0x
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE TDE;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
DBCC PAGE (TDE, 1, 0, 3);
GO
PAGE: (1:0)
BUFFER:
BUF @0x0000000003BE0E40
bpage = 0x000000016FCFC000 bhash = 0x0000000000000000 bpageno = (1:0)
bdbid = 8 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 2652 bstat = 0x9
blog = 0x1215a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000016FCFC000
m_pageId = (1:0) m_headerVersion = 1 m_type = 15
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 7029 m_freeData = 1161
m_reservedCnt = 0 m_lsn = (0:0:1) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -734960185
DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
File Header Data:
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065
Memory Dump @0x000000003C15A060
0000000000000000: 30000800 00000000 31000000 00000000 002e007f 0.......1...........
0000000000000014: 007f0081 00830087 008b008f 0093009d 00a700b1 .....ƒ.‡.‹...“...§.±
0000000000000028: 00b100b5 00b900bd 00c100cb 00e700f1 00fb0005 .±.µ.¹.½.Á.Ë.ç.ñ.û..
000000000000003C: 0115011f 012f0133 013d013d 01430153 01530153 ...../.3.=.=.C.S.S.S
0000000000000050: 01530153 01530153 01630163 0163016d 01770193 .S.S.S.S.c.c.c.m.w.“
0000000000000064: 019d01ad 01c901d1 012904c2 98df59fe efd44db3 ....É.Ñ.).Â.ßYþïÔM³
0000000000000078: d0b304a0 913a1301 00010080 010000ff ffffff80 г. ‘:.........ÿÿÿÿ.
000000000000008C: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000A0: 00000000 00000000 00000000 00000000 00800100 ....................
00000000000000B4: 00000000 00ffffff ff000200 00000000 00000000 .....ÿÿÿÿ...........
00000000000000C8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000DC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000F0: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000104: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000118: 00000000 0000004d c664e895 a00048a9 77bd01c9 .......MÆdè• .H©w½.É
000000000000012C: 93d0aa00 00000000 00000000 00000000 00540044 “Ъ..............T.D
0000000000000140: 00450000 00000000 00000000 00000000 00000000 .E..................
0000000000000154: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000168: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000017C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000190: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001A4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001B8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001CC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001E0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001F4: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000208: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000021C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000230: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000244: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000258: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000026C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000280: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000294: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002A8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002BC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002D0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002E4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002F8: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000030C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000320: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000334: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000348: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000035C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000370: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000384: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000398: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003AC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003C0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003D4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003E8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003FC: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000410: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000424: 00000000 00 .....
BindingID = 59df98c2-effe-4dd4-b3d0-b304a0913a13 FileIdProp = 1
FileGroupId = 1 Size = 384 MaxSize = 65535
Growth = 128 Perf = 0 BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL]
MinSize = 384 Status = 0 UserShrinkSize = 65535
SectorSize = 512 MaxLsn = (0:0:0) FirstLsn = (0:0:0)
CreateLsn = (0:0:0) DifferentialBaseLsn = (0:0:0)
DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000 FileOfflineLsn = (0:0:0)
FileIdGuid = e864c64d-a095-4800-a977-bd01c993d0aa RestoreStatus = 0
RestoreRedoStartLsn = (0:0:0) RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL]
FstrContainerSize = [NULL] MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0) RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000
RestorePathOrigin
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
DatabaseEncryptionFileState.m_maxScannedPage = 0 DatabaseEncryptionFileState.m_keyId = 0
FCBFileDEK
m_dbeStatusBits = 0 m_dtCreated = 1900-01-01 00:00:00.000
m_dtLastRegenerated = 1900-01-01 00:00:00.000
m_dtLastModified = 1900-01-01 00:00:00.000 m_dtLastSet = 1900-01-01 00:00:00.000
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 0 m_algId = 0
m_dwBitLen = 0 m_cbThumbprint = 0 m_rgbThumbprint = 0x
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
ALTER DATABASE TDE
SET ENCRYPTION ON;
GO
DBCC PAGE (TDE, 1, 0, 3);
GO
PAGE: (1:0)
BUFFER:
BUF @0x0000000003BE0E40
bpage = 0x000000016FCFC000 bhash = 0x0000000000000000 bpageno = (1:0)
bdbid = 8 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 2801 bstat = 0x9
blog = 0x7a7a9a7a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000016FCFC000
m_pageId = (1:0) m_headerVersion = 1 m_type = 15
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x208
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 7029 m_freeData = 7551
m_reservedCnt = 0 m_lsn = (41:453:2) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -26638716
DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
File Header Data:
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065
Memory Dump @0x000000003F8AB956
0000000000000000: 30000800 00000000 31000000 00000000 002e007f 0.......1...........
0000000000000014: 007f0081 00830087 008b008f 0093009d 00a700b1 .....ƒ.‡.‹...“...§.±
0000000000000028: 00b100b5 00b900bd 00c100cb 00e700f1 00fb0005 .±.µ.¹.½.Á.Ë.ç.ñ.û..
000000000000003C: 0115011f 012f0133 013d013d 01430153 01530153 ...../.3.=.=.C.S.S.S
0000000000000050: 01530153 01530153 01630163 0163016d 01770193 .S.S.S.S.c.c.c.m.w.“
0000000000000064: 019d01ad 01c901d1 012904c2 98df59fe efd44db3 ....É.Ñ.).Â.ßYþïÔM³
0000000000000078: d0b304a0 913a1301 00010080 020000ff ffffff80 г. ‘:.........ÿÿÿÿ.
000000000000008C: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000A0: 00000000 00000000 00000000 00000000 00800100 ....................
00000000000000B4: 00000000 00ffffff ff000200 00000000 00000000 .....ÿÿÿÿ...........
00000000000000C8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000DC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000F0: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000104: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000118: 00000000 0000004d c664e895 a00048a9 77bd01c9 .......MÆdè• .H©w½.É
000000000000012C: 93d0aa00 00000000 00000000 00000000 00540044 “Ъ..............T.D
0000000000000140: 00450000 00000000 00000000 00000000 00000000 .E..................
0000000000000154: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000168: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000017C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000190: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001A4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001B8: 00000000 00000000 00000000 00000000 007f0200 ....................
00000000000001CC: 00010000 0023ff94 6f010000 0001ff94 6f010000 .....#ÿ”o.....ÿ”o...
00000000000001E0: 004917f3 00aea000 004917f3 00aea000 004917f3 .I.ó.® ..I.ó.® ..I.ó
00000000000001F4: 00aea000 00a3d5f3 00aea000 00011e94 6f140000 .® ..£Õó.® ....”o...
0000000000000208: 00398846 e0829a98 1e2b96ef 99a9a14a 26607762 .9.Fà‚š..+–ï.©¡J&`wb
000000000000021C: 91800000 00c7561c 270e5961 ffcc9a71 e061aa6e ‘....ÇV.'.YaÿÌšqàaªn
0000000000000230: 38b63e24 b7946d6c 9e354e8f a12efe24 5e91f5db 8¶>$·”mlž5N.¡.þ$^‘õÛ
0000000000000244: 559890ee b0b806c1 8f380331 e3cbc65e 4ae05fb7 U..î°¸.Á.8.1ãËÆ^Jà_·
0000000000000258: 863504fe a8f6e963 60c2b263 b3643a18 4479a95d †5.þ¨öéc`²c³d:.Dy©]
000000000000026C: 367c47d9 c335f285 a7e1bb96 9d30e5c0 a98869ca 6|GÙÃ5ò…§á»–.0åÀ©.iÊ
0000000000000280: dc807b44 0724be52 29cbbdc5 202a754c b1870d83 Ü.{D.$¾R)˽Š*uL±‡.ƒ
0000000000000294: 9de05bd6 4dcb6cad 4de45248 81400044 6e010000 .à[ÖMËlMäRH.@.Dn...
00000000000002A8: 00909d94 6f010000 00409c76 eefe0700 00010000 ...”o....@œvîþ......
00000000000002BC: 00010000 00400044 6e010000 00000000 00000000 .....@.Dn...........
00000000000002D0: 00000000 00000000 00803e95 6f010000 00000000 ..........>•o.......
00000000000002E4: 00000000 0040b542 6e010000 00000000 00000000 .....@µBn...........
00000000000002F8: 00030000 000000f0 3f000000 00000000 00807a59 .......ð?.........zY
000000000000030C: 6f010000 00805d95 6f010000 00000000 000000f0 o.....]•o..........ð
0000000000000320: 3f400044 6e010000 00e0a244 6f010000 00007844 ?@.Dn....à¢Do.....xD
0000000000000334: 6e010000 0020a244 6f010000 00c00a77 eefe0700 n.... ¢Do....À.wîþ..
0000000000000348: 00140000 00010000 0040208d 6f010000 0040a044 .........@ .o....@ D
000000000000035C: 6f150000 00150000 00010000 00a01ebc 6f010000 o............ .¼o...
0000000000000370: 00003e00 00fe0700 00000000 0000003f 40000000 ..>..þ.........?@...
0000000000000384: 00000000 00000000 00010000 00010000 00000000 ....................
0000000000000398: 00000000 00010000 00005579 eefe0700 00010000 ..........Uyîþ......
00000000000003AC: 00000000 00400044 6e010000 00100b77 eefe0700 .....@.Dn......wîþ..
00000000000003C0: 00010000 00000000 00400044 6e010000 00000a77 .........@.Dn......w
00000000000003D4: ee030000 00040000 00000000 00209994 6f010000 î............ .”o...
00000000000003E8: 00109f79 eefe0700 00010000 00000000 00400044 ..Ÿyîþ...........@.D
00000000000003FC: 6e010000 00000000 00030000 00040000 00000000 n...................
0000000000000410: 00f06692 6f010000 00400044 6e000000 000e6600 .ðf’o....@.Dn.....f.
0000000000000424: 00800000 00 .....
BindingID = 59df98c2-effe-4dd4-b3d0-b304a0913a13 FileIdProp = 1
FileGroupId = 1 Size = 640 MaxSize = 65535
Growth = 128 Perf = 0 BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL]
MinSize = 384 Status = 0 UserShrinkSize = 65535
SectorSize = 512 MaxLsn = (0:0:0) FirstLsn = (0:0:0)
CreateLsn = (0:0:0) DifferentialBaseLsn = (0:0:0)
DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000 FileOfflineLsn = (0:0:0)
FileIdGuid = e864c64d-a095-4800-a977-bd01c993d0aa RestoreStatus = 0
RestoreRedoStartLsn = (0:0:0) RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL]
FstrContainerSize = [NULL] MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0) RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000
RestorePathOrigin
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
DatabaseEncryptionFileState.m_maxScannedPage = 639 DatabaseEncryptionFileState.m_keyId = 1
FCBFileDEK
m_dbeStatusBits = 35 m_dtCreated = 2012-08-15 14:45:04.030
m_dtLastRegenerated = 2012-08-15 14:45:04.030
m_dtLastModified = 2012-08-15 14:45:04.030 m_dtLastSet = 2012-08-15 14:47:46.463
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 26126 m_algId = AES
m_dwBitLen = 128 m_cbThumbprint = 20
m_rgbThumbprint = 0x398846E0829A981E2B96EF99A9A14A2660776291
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Below is the testing scenarios.
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TDE', FILENAME = N'C:\SQL2012\MSSQL11.PDB\MSSQL\DATA\TDE.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TDE_log', FILENAME = N'C:\SQL2012\MSSQL11.PDB\MSSQL\DATA\TDE_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
DBCC TRACEON(3604)
DBCC PAGE (TDE, 1, 0, 3);
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:0)
BUFFER:
BUF @0x0000000003BE0E40
bpage = 0x000000016FCFC000 bhash = 0x0000000000000000 bpageno = (1:0)
bdbid = 8 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 2526 bstat = 0x9
blog = 0x1215a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000016FCFC000
m_pageId = (1:0) m_headerVersion = 1 m_type = 15
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 7029 m_freeData = 1161
m_reservedCnt = 0 m_lsn = (0:0:1) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -734960185
DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
File Header Data:
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065
Memory Dump @0x000000003C15A060
0000000000000000: 30000800 00000000 31000000 00000000 002e007f 0.......1...........
0000000000000014: 007f0081 00830087 008b008f 0093009d 00a700b1 .....ƒ.‡.‹...“...§.±
0000000000000028: 00b100b5 00b900bd 00c100cb 00e700f1 00fb0005 .±.µ.¹.½.Á.Ë.ç.ñ.û..
000000000000003C: 0115011f 012f0133 013d013d 01430153 01530153 ...../.3.=.=.C.S.S.S
0000000000000050: 01530153 01530153 01630163 0163016d 01770193 .S.S.S.S.c.c.c.m.w.“
0000000000000064: 019d01ad 01c901d1 012904c2 98df59fe efd44db3 ....É.Ñ.).Â.ßYþïÔM³
0000000000000078: d0b304a0 913a1301 00010080 010000ff ffffff80 г. ‘:.........ÿÿÿÿ.
000000000000008C: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000A0: 00000000 00000000 00000000 00000000 00800100 ....................
00000000000000B4: 00000000 00ffffff ff000200 00000000 00000000 .....ÿÿÿÿ...........
00000000000000C8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000DC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000F0: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000104: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000118: 00000000 0000004d c664e895 a00048a9 77bd01c9 .......MÆdè• .H©w½.É
000000000000012C: 93d0aa00 00000000 00000000 00000000 00540044 “Ъ..............T.D
0000000000000140: 00450000 00000000 00000000 00000000 00000000 .E..................
0000000000000154: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000168: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000017C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000190: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001A4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001B8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001CC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001E0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001F4: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000208: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000021C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000230: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000244: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000258: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000026C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000280: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000294: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002A8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002BC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002D0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002E4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002F8: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000030C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000320: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000334: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000348: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000035C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000370: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000384: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000398: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003AC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003C0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003D4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003E8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003FC: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000410: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000424: 00000000 00 .....
BindingID = 59df98c2-effe-4dd4-b3d0-b304a0913a13 FileIdProp = 1
FileGroupId = 1 Size = 384 MaxSize = 65535
Growth = 128 Perf = 0 BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL]
MinSize = 384 Status = 0 UserShrinkSize = 65535
SectorSize = 512 MaxLsn = (0:0:0) FirstLsn = (0:0:0)
CreateLsn = (0:0:0) DifferentialBaseLsn = (0:0:0)
DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000 FileOfflineLsn = (0:0:0)
FileIdGuid = e864c64d-a095-4800-a977-bd01c993d0aa RestoreStatus = 0
RestoreRedoStartLsn = (0:0:0) RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL]
FstrContainerSize = [NULL] MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0) RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000
RestorePathOrigin
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
DatabaseEncryptionFileState.m_maxScannedPage = 0 DatabaseEncryptionFileState.m_keyId = 0
FCBFileDEK
m_dbeStatusBits = 0 m_dtCreated = 1900-01-01 00:00:00.000
m_dtLastRegenerated = 1900-01-01 00:00:00.000
m_dtLastModified = 1900-01-01 00:00:00.000 m_dtLastSet = 1900-01-01 00:00:00.000
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 0 m_algId = 0
m_dwBitLen = 0 m_cbThumbprint = 0 m_rgbThumbprint = 0x
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE TDE;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
DBCC PAGE (TDE, 1, 0, 3);
GO
PAGE: (1:0)
BUFFER:
BUF @0x0000000003BE0E40
bpage = 0x000000016FCFC000 bhash = 0x0000000000000000 bpageno = (1:0)
bdbid = 8 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 2652 bstat = 0x9
blog = 0x1215a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000016FCFC000
m_pageId = (1:0) m_headerVersion = 1 m_type = 15
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 7029 m_freeData = 1161
m_reservedCnt = 0 m_lsn = (0:0:1) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -734960185
DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
File Header Data:
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065
Memory Dump @0x000000003C15A060
0000000000000000: 30000800 00000000 31000000 00000000 002e007f 0.......1...........
0000000000000014: 007f0081 00830087 008b008f 0093009d 00a700b1 .....ƒ.‡.‹...“...§.±
0000000000000028: 00b100b5 00b900bd 00c100cb 00e700f1 00fb0005 .±.µ.¹.½.Á.Ë.ç.ñ.û..
000000000000003C: 0115011f 012f0133 013d013d 01430153 01530153 ...../.3.=.=.C.S.S.S
0000000000000050: 01530153 01530153 01630163 0163016d 01770193 .S.S.S.S.c.c.c.m.w.“
0000000000000064: 019d01ad 01c901d1 012904c2 98df59fe efd44db3 ....É.Ñ.).Â.ßYþïÔM³
0000000000000078: d0b304a0 913a1301 00010080 010000ff ffffff80 г. ‘:.........ÿÿÿÿ.
000000000000008C: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000A0: 00000000 00000000 00000000 00000000 00800100 ....................
00000000000000B4: 00000000 00ffffff ff000200 00000000 00000000 .....ÿÿÿÿ...........
00000000000000C8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000DC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000F0: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000104: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000118: 00000000 0000004d c664e895 a00048a9 77bd01c9 .......MÆdè• .H©w½.É
000000000000012C: 93d0aa00 00000000 00000000 00000000 00540044 “Ъ..............T.D
0000000000000140: 00450000 00000000 00000000 00000000 00000000 .E..................
0000000000000154: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000168: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000017C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000190: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001A4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001B8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001CC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001E0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001F4: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000208: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000021C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000230: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000244: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000258: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000026C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000280: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000294: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002A8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002BC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002D0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002E4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000002F8: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000030C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000320: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000334: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000348: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000035C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000370: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000384: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000398: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003AC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003C0: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003D4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003E8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000003FC: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000410: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000424: 00000000 00 .....
BindingID = 59df98c2-effe-4dd4-b3d0-b304a0913a13 FileIdProp = 1
FileGroupId = 1 Size = 384 MaxSize = 65535
Growth = 128 Perf = 0 BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL]
MinSize = 384 Status = 0 UserShrinkSize = 65535
SectorSize = 512 MaxLsn = (0:0:0) FirstLsn = (0:0:0)
CreateLsn = (0:0:0) DifferentialBaseLsn = (0:0:0)
DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000 FileOfflineLsn = (0:0:0)
FileIdGuid = e864c64d-a095-4800-a977-bd01c993d0aa RestoreStatus = 0
RestoreRedoStartLsn = (0:0:0) RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL]
FstrContainerSize = [NULL] MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0) RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000
RestorePathOrigin
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
DatabaseEncryptionFileState.m_maxScannedPage = 0 DatabaseEncryptionFileState.m_keyId = 0
FCBFileDEK
m_dbeStatusBits = 0 m_dtCreated = 1900-01-01 00:00:00.000
m_dtLastRegenerated = 1900-01-01 00:00:00.000
m_dtLastModified = 1900-01-01 00:00:00.000 m_dtLastSet = 1900-01-01 00:00:00.000
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 0 m_algId = 0
m_dwBitLen = 0 m_cbThumbprint = 0 m_rgbThumbprint = 0x
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
ALTER DATABASE TDE
SET ENCRYPTION ON;
GO
DBCC PAGE (TDE, 1, 0, 3);
GO
PAGE: (1:0)
BUFFER:
BUF @0x0000000003BE0E40
bpage = 0x000000016FCFC000 bhash = 0x0000000000000000 bpageno = (1:0)
bdbid = 8 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 2801 bstat = 0x9
blog = 0x7a7a9a7a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000016FCFC000
m_pageId = (1:0) m_headerVersion = 1 m_type = 15
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x208
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 7029 m_freeData = 7551
m_reservedCnt = 0 m_lsn = (41:453:2) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -26638716
DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
File Header Data:
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065
Memory Dump @0x000000003F8AB956
0000000000000000: 30000800 00000000 31000000 00000000 002e007f 0.......1...........
0000000000000014: 007f0081 00830087 008b008f 0093009d 00a700b1 .....ƒ.‡.‹...“...§.±
0000000000000028: 00b100b5 00b900bd 00c100cb 00e700f1 00fb0005 .±.µ.¹.½.Á.Ë.ç.ñ.û..
000000000000003C: 0115011f 012f0133 013d013d 01430153 01530153 ...../.3.=.=.C.S.S.S
0000000000000050: 01530153 01530153 01630163 0163016d 01770193 .S.S.S.S.c.c.c.m.w.“
0000000000000064: 019d01ad 01c901d1 012904c2 98df59fe efd44db3 ....É.Ñ.).Â.ßYþïÔM³
0000000000000078: d0b304a0 913a1301 00010080 020000ff ffffff80 г. ‘:.........ÿÿÿÿ.
000000000000008C: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000A0: 00000000 00000000 00000000 00000000 00800100 ....................
00000000000000B4: 00000000 00ffffff ff000200 00000000 00000000 .....ÿÿÿÿ...........
00000000000000C8: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000DC: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000000F0: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000104: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000118: 00000000 0000004d c664e895 a00048a9 77bd01c9 .......MÆdè• .H©w½.É
000000000000012C: 93d0aa00 00000000 00000000 00000000 00540044 “Ъ..............T.D
0000000000000140: 00450000 00000000 00000000 00000000 00000000 .E..................
0000000000000154: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000168: 00000000 00000000 00000000 00000000 00000000 ....................
000000000000017C: 00000000 00000000 00000000 00000000 00000000 ....................
0000000000000190: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001A4: 00000000 00000000 00000000 00000000 00000000 ....................
00000000000001B8: 00000000 00000000 00000000 00000000 007f0200 ....................
00000000000001CC: 00010000 0023ff94 6f010000 0001ff94 6f010000 .....#ÿ”o.....ÿ”o...
00000000000001E0: 004917f3 00aea000 004917f3 00aea000 004917f3 .I.ó.® ..I.ó.® ..I.ó
00000000000001F4: 00aea000 00a3d5f3 00aea000 00011e94 6f140000 .® ..£Õó.® ....”o...
0000000000000208: 00398846 e0829a98 1e2b96ef 99a9a14a 26607762 .9.Fà‚š..+–ï.©¡J&`wb
000000000000021C: 91800000 00c7561c 270e5961 ffcc9a71 e061aa6e ‘....ÇV.'.YaÿÌšqàaªn
0000000000000230: 38b63e24 b7946d6c 9e354e8f a12efe24 5e91f5db 8¶>$·”mlž5N.¡.þ$^‘õÛ
0000000000000244: 559890ee b0b806c1 8f380331 e3cbc65e 4ae05fb7 U..î°¸.Á.8.1ãËÆ^Jà_·
0000000000000258: 863504fe a8f6e963 60c2b263 b3643a18 4479a95d †5.þ¨öéc`²c³d:.Dy©]
000000000000026C: 367c47d9 c335f285 a7e1bb96 9d30e5c0 a98869ca 6|GÙÃ5ò…§á»–.0åÀ©.iÊ
0000000000000280: dc807b44 0724be52 29cbbdc5 202a754c b1870d83 Ü.{D.$¾R)˽Š*uL±‡.ƒ
0000000000000294: 9de05bd6 4dcb6cad 4de45248 81400044 6e010000 .à[ÖMËlMäRH.@.Dn...
00000000000002A8: 00909d94 6f010000 00409c76 eefe0700 00010000 ...”o....@œvîþ......
00000000000002BC: 00010000 00400044 6e010000 00000000 00000000 .....@.Dn...........
00000000000002D0: 00000000 00000000 00803e95 6f010000 00000000 ..........>•o.......
00000000000002E4: 00000000 0040b542 6e010000 00000000 00000000 .....@µBn...........
00000000000002F8: 00030000 000000f0 3f000000 00000000 00807a59 .......ð?.........zY
000000000000030C: 6f010000 00805d95 6f010000 00000000 000000f0 o.....]•o..........ð
0000000000000320: 3f400044 6e010000 00e0a244 6f010000 00007844 ?@.Dn....à¢Do.....xD
0000000000000334: 6e010000 0020a244 6f010000 00c00a77 eefe0700 n.... ¢Do....À.wîþ..
0000000000000348: 00140000 00010000 0040208d 6f010000 0040a044 .........@ .o....@ D
000000000000035C: 6f150000 00150000 00010000 00a01ebc 6f010000 o............ .¼o...
0000000000000370: 00003e00 00fe0700 00000000 0000003f 40000000 ..>..þ.........?@...
0000000000000384: 00000000 00000000 00010000 00010000 00000000 ....................
0000000000000398: 00000000 00010000 00005579 eefe0700 00010000 ..........Uyîþ......
00000000000003AC: 00000000 00400044 6e010000 00100b77 eefe0700 .....@.Dn......wîþ..
00000000000003C0: 00010000 00000000 00400044 6e010000 00000a77 .........@.Dn......w
00000000000003D4: ee030000 00040000 00000000 00209994 6f010000 î............ .”o...
00000000000003E8: 00109f79 eefe0700 00010000 00000000 00400044 ..Ÿyîþ...........@.D
00000000000003FC: 6e010000 00000000 00030000 00040000 00000000 n...................
0000000000000410: 00f06692 6f010000 00400044 6e000000 000e6600 .ðf’o....@.Dn.....f.
0000000000000424: 00800000 00 .....
BindingID = 59df98c2-effe-4dd4-b3d0-b304a0913a13 FileIdProp = 1
FileGroupId = 1 Size = 640 MaxSize = 65535
Growth = 128 Perf = 0 BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0) OldestRestoredLsn = (0:0:0) FirstNonloggedUpdateLsn = [NULL]
MinSize = 384 Status = 0 UserShrinkSize = 65535
SectorSize = 512 MaxLsn = (0:0:0) FirstLsn = (0:0:0)
CreateLsn = (0:0:0) DifferentialBaseLsn = (0:0:0)
DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000 FileOfflineLsn = (0:0:0)
FileIdGuid = e864c64d-a095-4800-a977-bd01c993d0aa RestoreStatus = 0
RestoreRedoStartLsn = (0:0:0) RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL] ReplTxfTruncationLsn = [NULL] TxfBackupLsn = [NULL]
FstrContainerSize = [NULL] MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL] SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0) ReadWriteLsn = (0:0:0) RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000
RestorePathOrigin
hex (dec) = 0x00000000:00000000:0000 (0:0:0)
m_guid = 00000000-0000-0000-0000-000000000000
DatabaseEncryptionFileState.m_maxScannedPage = 639 DatabaseEncryptionFileState.m_keyId = 1
FCBFileDEK
m_dbeStatusBits = 35 m_dtCreated = 2012-08-15 14:45:04.030
m_dtLastRegenerated = 2012-08-15 14:45:04.030
m_dtLastModified = 2012-08-15 14:45:04.030 m_dtLastSet = 2012-08-15 14:47:46.463
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 26126 m_algId = AES
m_dwBitLen = 128 m_cbThumbprint = 20
m_rgbThumbprint = 0x398846E0829A981E2B96EF99A9A14A2660776291
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Monday, August 13, 2012
Sample script to test OGG "DBOPTIONS SUPPRESSTRIGGERS"
create table hr.tbl_trigger_test
(id number,
updated_host varchar2(100),
updated_user varchar2(30),
updated_time timestamp,
constraint tbl_trigger_test_pk primary key(id))
;
create or replace trigger hr.trg_trigger_test
before insert on hr.tbl_trigger_test
for each row
begin
:new.updated_host:=sys_context('userenv','host');
:new.updated_user:=sys_context('userenv','session_user');
:new.updated_time:=systimestamp;
end;
/
create table hr.tbl_trigger_test_2
(id number,
updated_host varchar2(100),
updated_user varchar2(30),
updated_time timestamp,
constraint tbl_trigger_test_2_pk primary key(id))
;
create or replace trigger hr.trg_trigger_test_2
after insert on hr.tbl_trigger_test
REFERENCING NEW AS newRow
for each row
begin
insert into hr.tbl_trigger_test_2 values(
:newRow.id,:newRow.updated_host,:newRow.updated_user,:newRow.updated_time);
end;
/
insert into hr.tbl_trigger_test values ('1',null,null,null);
insert into hr.tbl_trigger_test values ('2',null,null,null);
insert into hr.tbl_trigger_test values ('3',null,null,null);
insert into hr.tbl_trigger_test values ('4',null,null,null);
insert into hr.tbl_trigger_test values ('5',null,null,null);
commit;
drop trigger hr.trg_trigger_test_2;
drop trigger hr.trg_trigger_test;
drop table hr.tbl_trigger_test_2 purge;
drop table hr.tbl_trigger_test purge;
(id number,
updated_host varchar2(100),
updated_user varchar2(30),
updated_time timestamp,
constraint tbl_trigger_test_pk primary key(id))
;
create or replace trigger hr.trg_trigger_test
before insert on hr.tbl_trigger_test
for each row
begin
:new.updated_host:=sys_context('userenv','host');
:new.updated_user:=sys_context('userenv','session_user');
:new.updated_time:=systimestamp;
end;
/
create table hr.tbl_trigger_test_2
(id number,
updated_host varchar2(100),
updated_user varchar2(30),
updated_time timestamp,
constraint tbl_trigger_test_2_pk primary key(id))
;
create or replace trigger hr.trg_trigger_test_2
after insert on hr.tbl_trigger_test
REFERENCING NEW AS newRow
for each row
begin
insert into hr.tbl_trigger_test_2 values(
:newRow.id,:newRow.updated_host,:newRow.updated_user,:newRow.updated_time);
end;
/
insert into hr.tbl_trigger_test values ('1',null,null,null);
insert into hr.tbl_trigger_test values ('2',null,null,null);
insert into hr.tbl_trigger_test values ('3',null,null,null);
insert into hr.tbl_trigger_test values ('4',null,null,null);
insert into hr.tbl_trigger_test values ('5',null,null,null);
commit;
drop trigger hr.trg_trigger_test_2;
drop trigger hr.trg_trigger_test;
drop table hr.tbl_trigger_test_2 purge;
drop table hr.tbl_trigger_test purge;
Subscribe to:
Posts (Atom)
