SQL:Deadlock Graph

Batch 1:

BEGIN TRAN
USE AdventureWorks
UPDATE Person.Address
SET AddressLine2='Lockccc'
WHERE AddressID='1'
WAITFOR DELAY '0:0:5'
SELECT * FROM Person.Address
WHERE AddressID='2'
COMMIT TRAN


Batch 2:


BEGIN TRAN
USE AdventureWorks
UPDATE Person.Address
SET AddressLine2='Lockvvv'
WHERE AddressID='2'
WAITFOR DELAY '0:0:5'
SELECT * FROM Person.Address
WHERE AddressID='1'
COMMIT TRAN



Create a trace with LOCK:DeadlockGraph selected in SQL Server Profiler and run it.



Execute the Process 1 and Process 2,

You must change the value of the UPDATE statement. If you don't and re-run both batches, no action occurs in SQL Server. No deadlock happens.

Table Scan, Index Scan, Index Seek

Table scan -- searches through every data page in a heap table;

Index scan -- if database engine optimizer finds an index can be used, the engine can scan every index leaf page. Indexes are usually thinner than the base table, so it is cheaper to scan an index.

For example,
SELECT TransactionType
FROM Production.TransactionHistoryArchive;

The database storage engine finds the Clustered Index can be used. Because of no WHERE clause to limit the output of the statement, Clustered Index Scan has to be used.

Index seek - if an index with a matching first column to your query is available, it can use the b-tree structures to get the data row(s) (start --end) . For example,

USE AdventureWorks;

GO

SET NOCOUNT ON;

GO

SET SHOWPLAN_ALL ON;

GO

SELECT NameFROM Production.UnitMeasure

WHERE UnitMeasureCode BETWEEN 'Each' AND 'Inch';

GO

SET SHOWPLAN_ALL OFF;

It is better to have a clustered index in a table. Table scan is the slowest possible way of execution. Table scan means not only that no index is used, but that there is no clustered index for this table at all. Even if you can only replace table scan with clustered index scan, it is still worth it.

If you see clustered index scan, find out whether it can be replaced with index seek. For that, find what conditions are applied to this table. Usually, conditions exist for two or three fields of the table. Find out the most selective condition (that is, the condition that would produce the smallest number of records if applied alone), and see whether an index on this field exists. Any index that lists this field first will qualify.

SQL Server determines whether to use an index by examining only the first column defined in the index. For example,

USE AdventureWorks

GO

CREATE INDEX fl_index
ON Person.Contact(FirstName,LastName)

GO

The following batch will show the index scan is used.

USE AdventureWorks;
GO
SET NOCOUNT ON;
GO
SET SHOWPLAN_ALL ON;
GO
SELECT LastName,FirstName FROM Person.Contact
WHERE LastName LIKE 'M%'
GO
SET SHOWPLAN_ALL OFF;
GO

The following batch will show the index seek is used.
USE AdventureWorks;
GO
SET NOCOUNT ON;
GO
SET SHOWPLAN_ALL ON;
GO
SELECT LastName,FirstName FROM Person.Contact
WHERE FirstName LIKE 'M%'
GO
SET SHOWPLAN_ALL OFF;
GO

Clustered Index and primary key

Usually, the Primary Key has the clustered index. But it does not have to be that case. The following is the example to show the Clustered Index and Primary Key can be separately defined.

create table [videotitle] (
[videoid] int IDENTITY(1,1)primary key nonclustered,
[videotitle] nvarchar(100) not null ,
[description] nvarchar(255)null ,
[videolanguage] nvarchar(50) null ,
[releasedate] datetime null ,
[isbn] nvarchar(25) ,
[upc_no] nvarchar(25) ,
[format] nvarchar(25) ,
[cost] money ,
[retailprice] money);
go
create clustered index [clustered_videotitle] on [videotitle] (videotitle);

When a table has a clustered index defined, data rows in the table is sorted as shown below. Because the data rows can be sorted only in one direction, you can only have one and only one clustered index for a table.

If a table do not have a clustered index defined, the data rows are in an unordered structure called heap -- no such doubly-link chain.

For a clustered table, when a non-clustered index is created. Its leaf node contains the non-clustered index key and clustered index key, which points to the actual data row. Because all non-clustered indexes automatically include the clustered index key, you should choose the column(s) with a smaller length.

SQL--Lock vs. Latch

Both lock and latch provide concurrency; at same time lock and latch maintain data consistency and integrity. Locking maintains the transactional integrity of the data while latching maintains the physical integrity of the data in memory.
Latch is a lock but with short period. Latch applies only to the page in RAM (data cache). Whereas lock applies to both RAM pages and stable media (storage).

Example

assume the following transaction affects only page 150. There is no index in the table.

BEGIN TRANSACTION
INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

When SQL Server executes the statement: INSERT INTO tblTest VALUES(1), the following events occur:

1. Page 150 is not currently present in the SQL Server cache so data page 150 is retrieved into the SQL Server data cache.
2. Appropriate locks for the transaction are obtained and the page is latched.
3. An Insert log record is built and added to the log cache.
4. A new row is added to the data page and the page is marked dirty.
5. The latch is released. All changes remain in volatile storage.

When SQL Server executes the statement: COMMIT TRANSACTION, transactional locks are released and the batch is considered complete.

If there are many statements in the transaction, many latches are obtained and released. SQL Server holds the latch for only the time necessary to perform the physical changes on the page. Depending on how many pages or tables are affected, the appropriate lock type is established to protect the row, range, page, or table as necessary through entire transaction period.

Addon Management for IE 7--how to find a clsid for an add-on?

Usining GPO to allow or disallow an add-on, as shown below:

The question is: how do you find the clsid for an addon?

You need a target computer. For example, download the Yahoo! Toolbar and install it in the target computer. Run the REGEDIT tool and locate the registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID

Edit menu--Find--

Type in Yahoo! Toolbar

It will lead you to the record shown below:

Then, right click on the Registry Key and select Copy Key Name and Paste it to Addon List window--only keep the { } :

0--disable the addon

1--allow the addon

Search your registry for SSVHelper, you will find the clsid for Java SSVHelper Class ssv.dll
{761497BB-D6F0-462C-B6EB-D4DAF1D92D43}

Search your registry for Sun Java Console, you will find the clsid for Sun Java Console

{08B0E5C0-4FCB-11CF-AAA5-00401C608501}

Smart use of SNAPSOT

Original Database Definition: AdventureWorks

CREATE DATABASE [AdventureWorks] ON PRIMARY
( NAME = N'AdventureWorks_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf' , SIZE = 180992KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N'AdventureWorks_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\adventureWorks.ldf' , SIZE = 133120KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO

Create a snapshot of the AdventureWorks database--Logical Name must be the same of the database definition


USE MASTER
GO
CREATE DATABASE AdventureWorks_Snapshot_Aug
ON (NAME=N'AdventureWorks_Data',FILENAME='C:\Data\Adv_Snapshot_Aug.mdf')
AS SNAPSHOT OF AdventureWorks

A user deleted many wrong records.

USE AdventureWorks
GO
DELETE FROM Sales.SalesOrderDetail
WHERE SalesOrderID=43659

You want to revert it --if you have multiple snapshots created, drop all other snapshots except the AdventureWorks_Snapshot_Aug'

USE MASTER
RESTORE DATABASE AdventureWorks FROM
DATABASE_SNAPSHOT='AdventureWorks_Snapshot_Aug'

You can restore only one row from the snapshot.


USE AdventureWorks
ALTER TABLE Sales.SalesOrderDetail
Add tmp CHAR(3) NULL
GO
UPDATE Sales.SalesOrderDetail
SET tmp='A'
WHERE SalesOrderDetailID=1;
UPDATE Sales.SalesOrderDetail
SET tmp='B'
WHERE SalesOrderDetailID=2;
GO

USE MASTER
GO
CREATE DATABASE AdventureWorks_Snapshot_Sept
ON (NAME=N'AdventureWorks_Data',FILENAME='C:\Data\Adv_Snapshot_Sept.mdf')
AS SNAPSHOT OF AdventureWorks
GO
--12 records deleted
USE AdventureWorks
DELETE FROM Sales.SalesOrderDetail
WHERE SalesOrderID=43659
GO
--restore only one record
USE AdventureWorks
DECLARE @SalesOrderID INT;
DECLARE @CarrierTrackingNumber NVARCHAR(25);
DECLARE @OrderQty SMALLINT;
DECLARE @ProductID INT;
DECLARE @SpecialOfferID INT;
DECLARE @UnitPrice MONEY;
DECLARE @UnitPriceDiscount MONEY;
DECLARE @LineTotal NUMERIC(38,6);
DECLARE @rowguid UNIQUEIDENTIFIER;
DECLARE @ModifiedDate DATETIME;
DECLARE @tmp CHAR(3);
SELECT @SalesOrderID=SalesOrderID,
@CarrierTrackingNumber=CarrierTrackingNumber,
@OrderQty=OrderQty,
@ProductID=ProductID,
@SpecialOfferID=SpecialOfferID,
@UnitPrice=UnitPrice,
@UnitPriceDiscount=UnitPriceDiscount,
--@LineTotal=LineTotal,
@rowguid=rowguid,
@ModifiedDate=ModifiedDate,
@tmp =tmp
FROM AdventureWorks_Snapshot_Sept.Sales.SalesOrderDetail
WHERE tmp ='A'
INSERT INTO Sales.SalesOrderDetail ( SalesOrderID,CarrierTrackingNumber,
OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount
,rowguid,ModifiedDate,tmp)
VALUES (
@SalesOrderID,@CarrierTrackingNumber,
@OrderQty,@ProductID,@SpecialOfferID,@UnitPrice,
@UnitPriceDiscount
,@rowguid,@ModifiedDate,@tmp )
GO

Restore a field value from a snapshot

UPDATE Sales.SalesOrderDetail
SET OrderQty=100,
UnitPrice=200
WHERE tmp='A'


DECLARE @OrderQty SMALLINT;
DECLARE @tmp CHAR(3);
SELECT
@OrderQty=OrderQty,
@tmp =tmp
FROM AdventureWorks_Snapshot_Sept.Sales.SalesOrderDetail
WHERE tmp ='A'
UPDATE Sales.SalesOrderDetail
SET OrderQty=@OrderQty
WHERE tmp='A'
GO

Copy a encrypted data from one row to another row

The purpose of an anthenticator is to ensure when you copy the encrypted value from one row onto another row within a table, the data is not valid any more.

Table structure:

USE [AdventureWorks]
GO
CREATE TABLE [Sales].[Card](
[CreditCardID] [int] NOT NULL,
[CardNumber] [varbinary](256) NOT NULL,
PRIMARY KEY CLUSTERED ( [CreditCardID] )
)

Insert two records into the [Sales].[Card] without authenticator:

USE AdventureWorks;
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
DECLARE @CreditCardID INT;
DECLARE @CreditCardNumber NVARCHAR(25);
DECLARE @Encrypted varbinary(256);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SET @CreditCardID=1;
SET @CreditCardNumber='66663635401028';

SET @Encrypted = EncryptByPassPhrase(@PassphraseEnteredByUser
, @CreditCardNumber);
INSERT Sales.Card
(CreditCardID,CardNumber)
VALUES(@CreditCardID,@Encrypted);
GO


USE AdventureWorks;
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
DECLARE @CreditCardID INT;
DECLARE @CreditCardNumber NVARCHAR(25);
DECLARE @Encrypted varbinary(256);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SET @CreditCardID=2;
SET @CreditCardNumber='55553635401028';

SET @Encrypted = EncryptByPassPhrase(@PassphraseEnteredByUser
, @CreditCardNumber);
INSERT Sales.Card
(CreditCardID,CardNumber)
VALUES(@CreditCardID,@Encrypted);
GO

Insert two records into the [Sales].[Card] with authenticator:



USE AdventureWorks;
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
DECLARE @CreditCardID INT;
DECLARE @CreditCardNumber NVARCHAR(25);
DECLARE @Encrypted varbinary(256);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SET @CreditCardID=3;
SET @CreditCardNumber='77773635401028';

SET @Encrypted = EncryptByPassPhrase(@PassphraseEnteredByUser
, @CreditCardNumber, 1, CONVERT( varbinary, @CreditCardID));
INSERT Sales.Card
(CreditCardID,CardNumber)
VALUES(@CreditCardID,@Encrypted);
GO

USE AdventureWorks;
GO
-- First get the passphrase from the user.
DECLARE @PassphraseEnteredByUser nvarchar(128);
DECLARE @CreditCardID INT;
DECLARE @CreditCardNumber NVARCHAR(25);
DECLARE @Encrypted varbinary(256);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SET @CreditCardID=4;
SET @CreditCardNumber='88883635401028';

SET @Encrypted = EncryptByPassPhrase(@PassphraseEnteredByUser
, @CreditCardNumber, 1, CONVERT( varbinary, @CreditCardID));
INSERT Sales.Card
(CreditCardID,CardNumber)
VALUES(@CreditCardID,@Encrypted);
GO

Without Authenticator

Copy the encrypted data from record 1 onto record 2

UPDATE Sales.Card
SET CardNumber=(SELECT CardNumber FROM Sales.Card WHERE CreditCardID='1')
WHERE CreditCardID='2'

Using the same passphrase, you can decrypt the data

DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SELECT CONVERT(nvarchar(25),
DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber))
FROM Sales.Card
WHERE CreditCardID = '2';

With Authenticator

Copy the encrypted data of row 3 onto row 4

UPDATE Sales.Card
SET CardNumber=(SELECT CardNumber FROM Sales.Card WHERE CreditCardID='3')
WHERE CreditCardID='4'

The following batch returns NULL --cannot decrypt the data --the data is not valid.

DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';
SELECT CONVERT(nvarchar(25),
DecryptByPassphrase(@PassphraseEnteredByUser, CardNumber, 1
, CONVERT(varbinary, CreditCardID)))
FROM Sales.Card
WHERE CreditCardID = '4';
============
Observation:
By using the primary key (CreditCardID) of the table to authenticate the encryption and decryption, you can ensure that the encrypted value is valid only within the row in which it resides and invalid if the encrypted text is copied to any other row.

The phrase "Preventing the encrypted data from being copied" is wrong. You can copy the encrypted data but the data is useless.

SPN and Kerberos and Linked server

You have two SQL Servers, each hosts one instance. The service account uses the domain user account.

During installation of a SQL Server 2005, if the service account is LOCAL SYSTEM, the Service Principal Name is created automatically. If the service account is a regular domain user account, to support Kerberos authentication, you must run setSPN utility to register the service name: setspn –A MSSQLSvc/:1433

To set up a LINKed server connection to use the Impersonation, you must configure the SQL Server service account to be Trusted for Delegation.

For example, SQL ServerA has a query, which retrieves data from SQL ServerB. SQL ServerA uses a domain\user1 as the service acccount; SQL ServerB uses a domain\user2 as the service account. At ServerA, a Linked server object is created with impersonation authentication. To make the impersonation work, you must register SPN for ServerA SQL service and Configure the domain\user1 to be Trusted for Delegation.

Smart use of schemas

The schemas of HumanResources and Person share the same owner of dbo.

USE 1:
The User user2 does not have permissions on schema Person.
Because the owner of the stored procedure and schema Person are same, the user2 can execute the HumanResources.uspSSS stored procedure without any error.

Why?

When a user has the EXECUTE permission on a stored procedure, the Database Engine checks the owner of schemas: HumanResources and Person referenced. If they share the same owner, no permission check is required.


USE MASTER
GO
CREATE LOGIN login1 WITH PASSWORD='password'
GO
USE AdventureWorks
GO
CREATE USER user2 FOR LOGIN login1
GO
GRANT EXECUTE ON HumanResources.uspSSS TO user2
GO
EXECUTE AS USER='user2'

SELECT SUSER_NAME(),SUSER_SNAME()

EXEC HumanResources.uspSSS

Observation:
If you change the owner of schema Person to another user, e.g. testUser, when you re-run the HumanResources.uspSSS, error occurs. Why? The stored procedure has EXECUTE AS CALLER option, because the Owners of HumanResources and Person are different, Database Engine will check who want to access the database object. Here is User2. User2 does not have permissions on objects in Person schema.


Change the owner of Person from dbo to another user, for example, testUser,
Re-run the HumanResources.uspSELECT, an error reports.

USE 2:simplify administration

If you have many Web Applications (e.g. 1000) to access your databases by using stored procedures, you can use the schemas to simplify the process.

The following is an example:

• Create a schema owned by the ASPNET account;
• assign the schema as the owner of all database items created;
  
• All applications should be configured to impersonate the ASPNET account.
• Stored procedures and executable code in the database should be configured to execute as the ASPNET account.

If you create the stored procedures with EXECUTE AS CALLER option, there will be many login and database users to be created and maintained.

USE 3: isolate database objects

How should you design the database to allow only database developers access to database components?

1. Add all developers to the Developers group.
2. Create a schema Dev that owns all database objects.
3. Grant only the Developers group permissions to the Dev schema.

====

Another use of Schema

AdventureWorks database has a view: Sales.vSalesPerson.

Sales.vSalesPerson depends on tables: Address, Contact, Employee, EmployeeAddress, and SalesPerson.

You create a login X1 and database user X1 without any perssion granted on any table. Run the statement with user X1: Select * From Sales.vSalesPerson

The SELECT permission was denied on the object 'vSalesPerson', database 'AdventureWorks', schema 'Sales'.

Create a stored procedure:

use adventureworks
GO
CREATE PROCEDURE Sales.getContact
AS
BEGIN

SET NOCOUNT ON;
SELECT * from Sales.vSalesPerson
END
GO

GRANT EXECUTE ON Sales.getContact to X1

And then, connect to SQL Server as X1,

You can run the statement without any error.

USE AdventureWorks
EXEC Sales.getContact

FULL RECOVERY vs. Bulk-logged Recovery

Full Recovery mode supports point-in-time (marked transaction) restore.

Bulk-logged Recovery mode does not support this feature. It is for temporary use. Before you bulk insert, create a snapshot of your database and then configure your database with bulk-logged recovery mode, bulk insert the data into your empty table. Last, configure your database back to FULL RECOVERY MODE and create a full backup of your database.

Very large database--use multiple filegroups --why

If you have a history database, which has 3GB, to fully back up this database takes long time. It is unachievable for every day full backup. You could create multiple filegroups for the database and create partitions for them. You can fully back up the current-used filegroup daily. Monthly create a full backup of database.

Restore a damaged page

Individual pages can be restored from a full data backup or file backup (or filegroup). Transaction log and differential backups cannot be used as a source for page level restore. you must restore transaction log backups, however, after you restore the damaged page to make the database consistent.
Generally, damaged pages will be listed in the error log of a database. To obtain ID of damaged pages, run the statement:
SELECT * FROM msdb..suspect_pages


Backup the active part of the transaction log(assuming you create the dataDISK):
BACKUP LOG AdventureWorks to dataDISK='active_log.bak',NO_TRUNCATE

Restore damaged pages from the most recent full backup:
RESTORE DATABASE AdventureWorks PAGE='3:3456'
FROM dataDISK WITH FILE='1',NORECOVERY

Restore transaction logs: NORECOVERY
Restore the active transaction log: RECOVERY

Increase the speed of the full database backups

perform the backup to multiple backup devices.

HAS_PERMS_BY_NAME

All users belong to SQLusers group. A login is created for the [Domain\SQLusers]. Suppose all the users have the same permissions on database objects. However, you find the user VANSTUDENTS\Vancouver can View more tables and can delete records on tables which he should not. How can you find out?

• Impersonate VANSTUDENTS\vancouver login:

EXECUTE AS LOGIN ='vanstudents\vancouver'

• SELECT Name FROM sys.tables WHERE HAS_PERMS_BY_NAME(name,'object','DELETE')=1;
  

• SELECT Name FROM sys.tables WHERE HAS_PERMS_BY_NAME(name,'object','SELECT')=1;

SQL Server Clustering vs. Database Mirroring

Server Cluster suits that all user and system databases should be fault-tolerant when a server has a failure. This cannot be achieved by Database Mirroring. Database Mirroring technology supports automatic fail-over when the database is offline. For database mirroring, you should manually copy all other user objects to the failover server.

Partitioning a large table to improve query performance

For example, you have a very large database: OrderDetail. Sales department runs a large number of short-running T-SQL batches, which mostly perform the WRITE operation with data for current month. Administration department executes many long running queries, which mostly perform the READ operations with data for prior months.

Solution: Designing a partitioning scheme to store the current data in one partition on disk volume 1 and store the prior months data on disk volume 2. The RAID 0 could be used to improve the performance further.

Application Role:
Domain Administrator and Database Administrator

If your company policy states that Domain Administrator cannot manage the SQL Server database, the following steps should be followed without violating the policy.

• Domain Administrator creates a global group or universal group, such as DataAccess;
• Database Administrator creates an application role with necessary permissions. Database Administrator creates the Login for DataAccess windows group and assigns it as a member of the application role;
• Domain Administrator controls the membership of DataAccess;

DBCC and RESTORE VERIFYONY WITH CHECKSUM

If you want to check whether a backup contains any location, structural, or logical integrity problems, you must restore the backup onto a standby SQL Server and run DBCC CHECKDB statements.
Using the RESTORE VERIFYONLY statement is not a guarantee of reliable data restoration-it is merely to check the readability of the file, even with CHECKSUM option.

EXECUTE AS user

If you have a module, e.g. stored procedure, which references many objects (for example, mary.Contact, steve.Address, etc), EXECUTE AS user should be used instead of EXECUTE AS OWNER because the ownership chain is broken.

EXECUTE AS SELF

When Smith creates the GetCustomer stored procedure with EXECUTE AS SELF, the result stored procedure will have EXECUTE AS Smith.

You have a stored procedure script. An application can create the stored procedure from the script. When John runs the application, the stored procedure will have EXECUTE AS John. When Joanne runs the application, the stored procedure will have EXECUTE AS Joanne.

As you see, the result is the same as EXECUTE AS user.

EXECUTE AS CALLER

The user who run the stored procedure must have the permissions on Customer table and have the EXECUTE permission on the storede procedure.

EXECUTE AS OWNER

When Tim runs the GetCustomer stored procedure, the Database Engine verifies that Tim has the EXECUTE permission on the GetCustomer object. When the GetCustomer stored procedure accesses the Customer table, because both Customer table and the stored procedure have the same OWNER (EXECUTE AS OWNER), Database Engine will not check the permissions. When SAM creates the GetCustomer object, SAM is the owner.

Archiving -- Switch operator --sliding windows

As shown in the following diagrams, you can switch oldest data to another table for analysis. This strategy can improve the Customer table query performance.

For the Customer table, the first partition is empty, none of the rows need to move from the first to the second partition, when the merge process runs. If the first partition is not empty and the boundary point is merged, then rows have to move from the first to the second partition, which can be a very expensive operation.

When you will switch out a partition of a table to a nonpartitioned table within the same filegroup, SQL Server can make this switch as a metadata change and no actual data moves.. As a metadata-only change, this can occur within seconds.

Leaving a partition empty at the end will allow you to split the empty partition (for the new data coming in) and not need to move rows from the last partition (because none exist) to the new filegroup that's being added (when the partition is split to include another chunk of data).

However, the Archive table has a very expensive operation when FG1 and FG2 are merged. It is better to assign Partition 1 and Partition 2 to FG1 and FG1 for both Customer Table and Archive table. In this way, only metadata changes and no actual data moves.

---------------

----------

When do you use SNAPSHOT?

Business requirements:

The Manager must view the current state of the data in SaleProduct database at the end of business day. He must be able to view the data state of any day within last 5 days. A single field in the database will not be updated more than once per day. However, it might be updated on any day. The Manager can rollback the changes if necessary.

Solution:

Create 1 scheduled job: occurs at end of each day: create a snapshot
Another scheduled job to remove snapsots that are more than 6 days old.

Efficient ways to move a database from ORACLE to SQL Server 2005

Build a testing SQL server;
Create the databases, tables, views, indexes, and stored procedures according to ORACLE environment;
Import some data into the testing SQL Server;
After you successfully test it, you could do the following to move the database in ORACLE to the production server.

Run scripts on the production server to build all tables, views, and stored procedures;
Schedule one or more jobs to move all data from ORACLE databases to the new databases on production server;
Build all indexes, and update the statistics on all tables.

Database consolidation

You have three databases: Accounting, HR, and Customer. Each is hosted on a dedicated SQL Server computer. All SQL Servers use Windows Authentication mode and belong to the single Active Directory domain. The Global Group dba_accounting is responsible for managing all databases and SQL Server configuration on the Accounting Computer. The dbaAdmin global group is responsible for managing the HR and Customer databases on the other two SQL Server computers.

How do you consolidate the three SQL Server computers?

Based on the security boundary requirement, one SQL Server 2005 computer with two instances will be designed.

Large Value Types out of Row Option

varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data.

When the sp_tableoption stored procedure 'large value types out of row' option is set to OFF, the in-row storage limit for large-value types is 8,000 bytes.

To support a more than 8,000 bytes data type, run:
USE AdventureWorks
sp_tableoption 'HumanResources.JobCandidate', 'Large Value Types Out Of Row' ,1

Data archiving

Your Customer table has a field Completed and a field Continued. The business requirement states that when a record with Completed set to 1 and Continued set to 0, it must be immediately moved to Customer_archive table.

Solution:

Create a DML trigger to accomplish it.

CREATE ASSEMBLY

PERMISSION_SET: SAFE, External_access, and Unsafe

• EXTERNAL_ACCESS enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.
• Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.
• UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code.

Implicit vs. Explicit http namespace reservation

Implicit namespace is reserved when you run the CREATE HTTP ENDPOINT statement. You must have the local Windows administrator privileges for the HTTP endpoint registration to succeed. However, this namespace can be taken by other applications if the instance of SQL Server is not running.

When you explicitly reserve a namespace, the namespace is reserved specifically for SQL Server, and all HTTP requests to this endpoint are forwarded to the instance of SQL Server. This namespace cannot be taken by other applications if the instance of SQL server is not running. You must have the local Windows Administrator privileges to run the explicit reservation stored procedure: sp_reserve_http_namespace.

For example, you can connect to an instance of SQL Server 2005 by using Windows authentication, a login that has administrator privileges, and execute the following:

sp_reserve_http_namespace N'http://MyServer:80/sql'
GO

A user without any computer administrator privileges can execute the endpoint DDL statement.
CREATE ENDPOINT sql_endpoint
STATE = STARTED
AS HTTP(
PATH = '/sql/AdvWorks',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
SITE = 'MyServer'
)
FOR SOAP (
...
)
GO

SQL Server 2005 backup plan

The business hour is 09:00 to 17:00. You can have only one differential backup during the business hours. You should perform the full backup during the business hours. Business requirements allow for up to one hour of data loss. No more than six backups should be required for any recovery.

Solution:

To cover business hour, you create the following scheme:
Full backup: 8:00, 16:00
Differential backup: 13:00
Transactional Log backup: 9, 10,11,12 and 14, 15, 16, 17

For Non-business hours, you could create Full and Differential and Log backups without restrictions.

Convert a nonpartitioned table to a partitioned table

1. Create a database OYCO
USE MASTER
CREATE DATABASE OYCO
ON PRIMARY
(NAME='OYCO_DAT',FILENAME='C:\DATA\OYCO_DAT.mdf'),
FILEGROUP FG1
(NAME='FG1_DAT',FILENAME='C:\DATA\FG1_DAT.ndf'),
FILEGROUP FG2
(NAME='FG2_DAT',FILENAME='C:\DATA\FG2_DAT.ndf'),
FILEGROUP FG3
(NAME='FG3_DAT',FILENAME='C:\DATA\FG3_DAT.ndf'),
FILEGROUP FG4
(NAME='FG4_DAT',FILENAME='C:\DATA\FG4_DAT.ndf')
LOG ON
(NAME='OYCO_LOG',FILENAME='C:\DATA\OYCO_LOG.ldf');
GO


2. Create a Partition function and scheme
USE OYCO
CREATE PARTITION FUNCTION pf(INT)
AS RANGE LEFT FOR VALUES(100,500,1000)
GO
CREATE PARTITION SCHEME ps AS PARTITION pf
TO (FG1,FG2,FG3,FG4)
GO

3. Create a table without partition

USE OYCO;
CREATE TABLE [Address](
[AddressID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[AddressLine1] [nvarchar](60) NOT NULL,
[City] [nvarchar](30) NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL
) ON FG1

4. Import data from AdventureWorks.Person.Address into Oyco.dbo.Address table

In command prompt:
Export data--

bcp "SELECT AddressID,AddressLine1,City,PostalCode FROM AdventureWorks.Person.Address" queryout c:\myAddress.dat -T -c -t "&"

Import data--
bcp OYCO.dbo.Address in C:\myAddress.dat -T -c -t "&"

5. Re-create the unique clustered index

CREATE UNIQUE CLUSTERED INDEX inx_clustered ON dbo.Address(AddressID)
WITH DROP_EXISTING
ON ps(AddressID);

SELECT * FROM sys.partitions
WHERE object_id=OBJECT_ID('dbo.address')

-----------

Another method needs more steps, as described below:

• CREATE PARTITION FUNCTION pf(INT) AS RANGE LEFT FOR VALUES()
• CREATE PARTITION SCHEME ps AS PARTITION pf TO (PRIMARY)

The above statements create a partition scheme with only one partition.

• Create a temporary table based on the partition scheme.
• ALTER TABLE source_nonpartitionedtable SWITCH TO temporary_partitionedTable Partition 1
  

• ALTER PARTITION SCHEME ps NEXT USED [Filegroup]
• ALTER PARTITION FUNCTION pf SPLIT RANGE (value)

The temporary partitioned table will have more partitions.

• DROP the original table
• EXEC sp_rename 'TemporaryTableName' 'OriginalTableName'

Partition to Partition Switching

The Partition 2 of dbo.Summer is empty.

ALTER TABLE dbo.customer SWITCH PARTITION 2 TO dbo.Summer PARTITION 2

The Partition 1 of dbo.Customer is empty.

ALTER TABLE dbo.Summer SWITCH PARTITION 1 TO dbo.Customer PARTITION 1

To be successful, the following conditions must be met:

A full partition must be switched with an empty partition;

Both tables must be aligned (must use the same partition function and partition scheme);

Both tables must have exactly the same structure for tables as well as indexes;

The range of values in the partition being switched must not exist in the target table.

SWITCH a non-partitioned table to the first empty partition or the last empty partition of a partitioned table
USE MASTER
CREATE DATABASE OYCO
ON PRIMARY
(NAME='OYCO_DAT',FILENAME='C:\DATA\OYCO_DAT.mdf'),
FILEGROUP FG1
(NAME='FG1_DAT',FILENAME='C:\DATA\FG1_DAT.ndf'),
FILEGROUP FG2
(NAME='FG2_DAT',FILENAME='C:\DATA\FG2_DAT.ndf'),
FILEGROUP FG3
(NAME='FG3_DAT',FILENAME='C:\DATA\FG3_DAT.ndf'),
FILEGROUP FG4
(NAME='FG4_DAT',FILENAME='C:\DATA\FG4_DAT.ndf')
LOG ON
(NAME='OYCO_LOG',FILENAME='C:\DATA\OYCO_LOG.ldf');
GO
USE OYCO
CREATE PARTITION FUNCTION pf(INT)
AS RANGE LEFT FOR VALUES(5,10,15)
GO
CREATE PARTITION SCHEME ps AS PARTITION pf
TO (FG1,FG2,FG3,FG4)
GO


CREATE TABLE dbo.customer
(CustomerID INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Address1 VARCHAR(50) NULL,
CONSTRAINT pk PRIMARY KEY CLUSTERED (CustomerID)
) ON ps(CustomerID);
GO
SET NOCOUNT ON
DECLARE @count INT
SET @count=1;
WHILE @count<30
BEGIN
INSERT dbo.Customer (CustomerID,FirstName,LastName)
VALUES(@count,'F'+CAST(@count AS CHAR(5)),'L'+CAST(@count AS CHAR(5)))
SET @count=@count+1;
END
GO

--Make sure that the first partition is empty.
DELETE from dbo.customer
WHERE $PARTITION.pf(CustomerID)=1
--Make sure that the last partition is empty
DELETE from dbo.customer
WHERE $PARTITION.pf(CustomerID)=4

--Create a test table

CREATE TABLE dbo.summer
(CustomerID INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Address1 VARCHAR(50) NULL,
CONSTRAINT pk1 PRIMARY KEY CLUSTERED (CustomerID),
CONSTRAINT chk CHECK (CustomerID<1)
) ON FG1;
GO

SET NOCOUNT ON
DECLARE @count INT
SET @count=0;
WHILE @count>-5
BEGIN
INSERT dbo.Summer (CustomerID,FirstName,LastName)
VALUES(@count,'F'+CAST(@count AS CHAR(5)),'L'+CAST(@count AS CHAR(5)))
SET @count=@count-1;
END
GO


ALTER TABLE dbo.summer SWITCH to dbo.customer partition 1

--Create another test table

CREATE TABLE dbo.Winter
(CustomerID INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Address1 VARCHAR(50) NULL,
CONSTRAINT pk2 PRIMARY KEY CLUSTERED (CustomerID),
CONSTRAINT chk2 CHECK (CustomerID>1000)
) ON FG4;
GO
SET NOCOUNT ON
DECLARE @count INT
SET @count=1001;
WHILE @count<1050
BEGIN
INSERT dbo.Winter (CustomerID,FirstName,LastName)
VALUES(@count,'F'+CAST(@count AS CHAR(5)),'L'+CAST(@count AS CHAR(5)))
SET @count=@count+1;
END
GO


ALTER TABLE dbo.Winter SWITCH to dbo.customer partition 4

You cannot switch a table into the partition 2 or 3.

Pay attention to the CHECK CONSTRAINT in the test table. The check constraint garanttees that the data in the test table will not violate the partitioned table's primary key constraint.

Unsuccessful practice: I tried to use the composite primary key. Even though the value in the test table has no conflict with the partitions definition, the following error shows:

ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'OYCO.dbo.summer' allows values that are not allowed by check constraints or partition function on target table 'OYCO.dbo.customer'.

It is possible that SQL Server could not check the uniqueness of a composite key when swiching operation runs.

ALTER Partition Function pf() MERGE RANGE ()

SPLIT RANGE

Based on boundary_value, the Microsoft SQL Server 2005 Database Engine splits one of the existing ranges into two. Of these two, the one where the new boundary_value resides is considered the new partition.
Assuming you have created databases and its filegroups: FG1 and FG2;


CREATE PARTITION FUNCTION pf(int) AS RANGE LEFT FOR VALUES(6)
CREATE PARTITION SCHEME [ps] AS PARTITION [pf] TO ([FG1], [FG2])


RANGE LEFT means that partition1<=6 and partition2 >6.

CREATE two more filegroups to your database: FG3 and FG4


ALTER PARTITION SCHEME ps NEXT USED [FG3]
GO
ALTER PARTITION FUNCTION pf() SPLIT RANGE(20)
GO

Check the Partition Scheme definition by scripting it to a new query window; you will have the following:
CREATE PARTITION SCHEME [ps] AS PARTITION [pf] TO ([FG1], [FG3], [FG2])

ALTER PARTITION SCHEME ps NEXT USED [FG4]
GO
ALTER PARTITION FUNCTION pf() SPLIT RANGE(2)

GO
Check the Partition Scheme definition by scripting it to a new query window; you will have the following:

CREATE PARTITION SCHEME [ps] AS PARTITION [pf] TO ([FG4], [FG1], [FG3], [FG2])


MAP

For the same boundary value, if the partition function is RANGE RIGHT, you will have the following scheme map:

SWITCH operator -- Archiving data

1. Create a Database OYCO with a Primary filegroup and two additional filegroup: FG1 and FG2.
USE MASTER
CREATE DATABASE Oyco ON PRIMARY
(NAME=Oyco_dat,FILENAME='c:\data\Oyco_dat.mdf',size=3MB),
FILEGROUP FG1
(Name=FG1_dat,FILENAME='c:\data\FG1_dat.ndf',size=3MB),
FILEGROUP FG2
(NAME=FG2_dat,FILENAME='c:\data\FG2_dat.ndf',size=3MB)
LOG ON
(NAME=Oyco_log,FILENAME='c:\data\Oyco_log.ldf',size=3MB,FILEGROWTH=10%);

GO

2. Create a partition function and partition scheme
USE OYCO
GO

CREATE PARTITION FUNCTION pf(INT) AS RANGE LEFT
FOR VALUES(6);
GO
CREATE PARTITION SCHEME ps AS PARTITION pf
TO (FG1,FG2);
GO

3. Create a table based on the partition scheme; the table will be created on FG1 and FG2.
USE OYCO
GO

CREATE TABLE dbo.Customer
(
CustomerID INT Identity(1,1)NOT NULL ,
FirstName NVARCHAR(25) NOT NULL,
LastName NVARCHAR(25) NOT NULL,
Address NVARCHAR(50) NULL,
Months INT NOT NULL,
--You must include the Months column in the primary key
--in order to use the partition scheme ps(Months).
PRIMARY KEY CLUSTERED (CustomerID,Months)
)
ON ps(Months);

All records with Months field <=6 will be stored in FG1 filegroup. All records with Months field between 7 to 12 will be stored in FG2 filegroup. 4. Generate some data USE OYCO
GO
SET NOCOUNT ON
DECLARE @count INT, @m INT
SET @count=1
SET @m=1

WHILE @count<100>
BEGIN
INSERT dbo.customer(FirstName,LastName,Months)
VALUES('F'+CAST(@count AS NVARCHAR(3)),'L'+CAST(@count AS NVARCHAR(3)),@m)
SET @count=@count+1
SET @m=@m+1
IF @m>12
BEGIN
SET @m=1
END
END
GO
5. Check the data in the dbo.customer table
SELECT * FROM dbo.customer
WHERE $PARTITION.pf(Months)=1

--Partition 1 includes the data from Month 1 to 6.

6. Create an archive table
CREATE TABLE dbo.FirstHalf
(
CustomerID INT Identity(1,1)NOT NULL ,
FirstName NVARCHAR(25) NOT NULL,
LastName NVARCHAR(25) NOT NULL,
Address NVARCHAR(50) NULL,
Months INT NOT NULL,
--You must create the same primary key as dbo.customer.
PRIMARY KEY CLUSTERED (CustomerID,Months)
)
ON FG1

As you see, the dbo.FirstHalf table has the same table structure as dbo.customer. This is important. Creating the dbo.FirstHalf table on Filegroup FG1, you can use the SWITCH operator to archive the partition 1 of dbo.customer table. Partition 1 is on FG1.

7. Archive the data
USE OYCO

ALTER TABLE dbo.customer
SWITCH PARTITION 1 TO dbo.FirstHalf

SELECT * FROM dbo.Customer
WHERE $PARTITION.pf(Months)=1
--All data is gone.

8. Archive the partition 2
USE OYCO
GO

CREATE TABLE dbo.SecondHalf
(
CustomerID INT Identity(1,1)NOT NULL ,
FirstName NVARCHAR(25) NOT NULL,
LastName NVARCHAR(25) NOT NULL,
Address NVARCHAR(50) NULL,
Months INT NOT NULL,
--You must create the primary key to match the dbo.customer structure.
PRIMARY KEY CLUSTERED (CustomerID,Months)
)
ON FG2


USE OYCO

ALTER TABLE dbo.customer
SWITCH PARTITION 2 TO dbo.SecondHalf

Observation:
If you want to move partitioned data by SWITCH operator, both the partition and the destination table must reside on the same filegroup. The destination table is empty.

Outlook Web Access (OWA) of Exchange Server 2007

SET-Mailbox


You should read the installation of the Exchange Server 2007 article.


After installation of Exchange Server 2007, the OWA is automatically configured with certificate. To access the OWA, you must use https:// protocol and trust the certificate authority. This is pretty good. You don't need to install and configure an Enterprise Certificate Authority in the network.


Create a user account (TomHichek) with a mailbox from Active Directory Users and Computers;


https://QUEEN/OWA

Input the login name: oyco\tomhicken and its password; the following error pops up:


RequestUrl: https://queen:443/owa/lang.owaUser host address: 10.10.10.10ExceptionException type: Microsoft.Exchange.Data.Storage.StoragePermanentExceptionException message: There was a problem accessing Active Directory.Call stack
Microsoft.Exchange.Data.Storage.ExchangePrincipal.Save()
Microsoft.Exchange.Clients.Owa.Core.RequestDispatcher.DispatchLanguagePostLocally(OwaContext owaContext, OwaIdentity logonIdentity, CultureInfo culture, String timeZoneKeyName, Boolean isOptimized)
Microsoft.Exchange.Clients.Owa.Core.RequestDispatcher.DispatchLanguagePostRequest(OwaContext owaContext)
Microsoft.Exchange.Clients.Owa.Core.RequestDispatcher.PrepareRequestWithoutSession(OwaContext owaContext, UserContextCookie userContextCookie)
Microsoft.Exchange.Clients.Owa.Core.RequestDispatcher.InternalDispatchRequest(OwaContext owaContext)
Microsoft.Exchange.Clients.Owa.Core.RequestDispatcher.DispatchRequest(OwaContext owaContext)
System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)Inner ExceptionException type: Microsoft.Exchange.Data.Directory.InvalidADObjectOperationExceptionException message: Property Languages cannot be set on this object because it requires the object to have version 0.1 (8.0.535.0) or later. Current version of the object is 0.0 (6.5.6500.0). Call stack
Microsoft.Exchange.Data.Directory.PropertyBag.set_Item(PropertyDefinition key, Object value)
Microsoft.Exchange.Data.Directory.ADObject.set_Item(PropertyDefinition propertyDefinition, Object value)

Microsoft.Exchange.Data.Directory.ADObject.StampCachedCaculatedProperties(Boolean retireCachedValue)
Microsoft.Exchange.Data.Directory.ADObject.ValidateWrite(List`1 errors)
Microsoft.Exchange.Data.Directory.Recipient.ADRecipient.ValidateWrite(List`1 errors)
Microsoft.Exchange.Data.Directory.Recipient.ADUser.ValidateWrite(List`1 errors)
Microsoft.Exchange.Data.Directory.ADSession.Save(ADObject instanceToSave, IEnumerable`1 properties) Microsoft.Exchange.Data.Storage.ExchangePrincipal.Save()



Why?


You should not create a mailbox in Exchange Server 2007 store by using Windows Server 2003 Active Directory Users and Computers utility.


From Exchange Management Console, you will see that the TomHicken mailbox is Legacy Mailbox.

Solution:

Work at QUEEN computer

Start Exchange Management Shell;

[PS] C:\>set-Mailbox -identity TomHicken -ApplyMandatoryProperties

HTTPS://QUEEN/OWA

Logon as TomHicken@OYCO.COM. It works. The good feature is that when you create a new message, you could select a recipient from an Address Book just like you could do by using OUTLOOK. In Exchange Server 2003/2000, you can find the recipient from the Address Book but you can not browse it.

Software Publishing of GPO--user intevention

We understand that you can only assign a Software Package to computer. You can assign or publish a software package to users. That is right.

However, if the Software itself needs the user interaction, you can only publish the software to users. I tried to assign the Firefox Firefox-2.0.0.7-en-US.msi package to computers. The software will not apply to the workstations. In the Resultant Set of Policy window, there is an exclamation sign beside the Software publishing category. Similar error occurs when the software package is assigned to users.

Try to publish Microsoft Office 2003 Data.MSI with ASSIGN option; it would install to client computers. Office installation needs the product key and clicking on Next button unless you create an unattended file ( I did not create one).

With user interactive requirement, the package is successfully published to users.