Peer-to-Peer replication

Work at Computer LOU:

USE MASTER
CREATE DATABASE [aPeer]
GO
ALTER DATABASE [aPeer] SET RECOVERY FULL
GO
CREATE LOGIN [VANSTUDENTS\agent] FROM WINDOWS
GO
USE aPeer
GO
CREATE USER [VANSTUDENTS\agent] FOR LOGIN [VANSTUDENTS\agent]
GO
EXEC sp_addrolemember N'db_owner', N'VANSTUDENTS\agent'
GO

CREATE TABLE [dbo].[Contact](
[Name] [nchar](10) NULL ,
[Address] [nvarchar](50) NULL,
[ID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ([ID] ASC)
)

--You must have a primary key for transactional replication.

--Don't use the IDENTITY type. If used, problem will occur when you insert records.


Configuring Local Distributor

USE distribution
GO
CREATE USER [VANSTUDENTS\agent] FOR LOGIN [VANSTUDENTS\agent]
GO
EXEC sp_addrolemember N'db_owner', N'VANSTUDENTS\agent'
GO


Backup the aPeer database

Copy the backup to the computer IMAGE21.

Work at Computer IMAGE21:

Restore database from the backup to create aPeer database.

GO
CREATE LOGIN [VANSTUDENTS\agent] FROM WINDOWS
GO
USE aPeer
GO
CREATE USER [VANSTUDENTS\agent] FOR LOGIN [VANSTUDENTS\agent]
GO
EXEC sp_addrolemember N'db_owner', N'VANSTUDENTS\agent'
GO


Configuring Local Distributor

USE distribution
GO
CREATE USER [VANSTUDENTS\agent] FOR LOGIN [VANSTUDENTS\agent]
GO
EXEC sp_addrolemember N'db_owner', N'VANSTUDENTS\agent'
GO


Create a publication: contact


Contact publication--Properties
Subscription Options: Peer-to-Peer replication --true
Publication Access List: Vanstudents\agent
--OK


Right Click on-Contact --Configuring Peer-to-Peer replication
Add the LOU as the peer.

Work at Computer LOU:
Contact Publication Properties--Publication Access List--Vanstudents\Agent.

Test the replication.

LOU:
INSERT Contact(Name,Address,ID) VALUES('Loudone','burnaby',NEWID());

Image21:

--you must wait for completion of the replication from LOU, and then issue the following statement

INSERT Contact(Name,Address) VALUES('Imagedone','Vancouver',NEWID());

================
You can check the Contact table for the new record.
================
If you want to update the Contact simultaneously, make sure you don't update the same row at same time in the two participating computers.


NOTES:

Pay attention to the following:
Both participating databases should contain identical schema. Object names, object schema, and publication names should be identical among the participating databases. When you create publications, no row and column filtering are used.

To be simplified, I use one security principal VANSTUDENTS\AGENT. SET it as a member of db_owner role of distribution, publisher, and subscriber databases.

If you follow the least privilege guidance and the replication does not happen, set the Distribution Agent and Log Reader Agent to use SQL Server Agent service account. It will work most of the time.

Peer to Peer replication --transactional-- use two agents: distribution and log reader. SNAPSHOT agent is not used.

You can check which agent(s) start from TASK MANAGER --Processes tab.

AS you will see, I did not change the default snapshot folder. The same schema and other objects are warrantied through backup and restore.

NOTE:

I create a scenario: one DC and a member server, both have the SQL Server installed.

I have to configure the Distribution Agent with SQL Server service account in order to replication transactional from Member to DC.

It is possible that DC is Windows Server 2003 with SP2, however Member is Windows Server 2003 with SP1.

Merge Replication

It uses two agents: snapshot and merge agent.

Active Directory Accounts: VIP\SnapshotAgent and VIP\MergeAgent.

Publisher database:

Login: VIP\SnapshotAgent and VIP\MergeAgent
Database user: VIP\SnapshotAgent belongs to db_owner
VIP\MergeAgent must have SELECT, INSERT, DELETE, and UPDATE permissions on the published articles. I assign the VIP\MergeAgent to be a member of db_datareader and db_datawriter.

Distributor database:
Login: VIP\SnapshotAgent and VIP\MergeAgent
Database user: VIP\SnapshotAgent belongs to db_owner
MergeAgent can be mapped to the Guest user of the Distribution database.

Default Snapshot Folder (UNC path): \\Server\Data
VIP\SnapshotAgent --Write permission
VIP\MergeAgent--Read permission

Publication Access List: VIP\MergeAgent must be in the list.

Subscriber:

Pull subscription: VIP\MergeAgent database user belongs db_owner role.

From a subscriber, you create a subscription. It should not have any problem.

In merge replication, the net result instead of log data is used.

Updateable Transactional publishing

Publisher and Distributor: Server (DC)

Subscriber: KingPC (member server)

Configuring MSDTC at both Publisher and Subscriber

RUN -- dcomcnfg

Component Services --Computers -- properties

MSDTC tab -- select "Use local coordinator"

--Security Configuration button --Select "Network Security Access"

Setting up Queue Reader Agent security

Db_owner of distribution database, publishing database, and subscriber databases.

Security for snapshot agent and distribution agent refers to my other blog.

Create a SQL Server account: QueueUpdate in publisher. QueueUpdate must has SELECT, DELETE, INSERT and UPDATE permissions on the publishing objects (tables, etc.). I assign it to be a member of db_datareader and db_datawriter, to simplify the permission assignment.

Publication Access List includes the QueueUpdate account.

Creating the subscription in subscriber

Checking the subscriber SQL server, you should have a Linked Server defined as:

When you are updating the data in subscriber, if you get error: "The subscription is uninitialized or unavailable for immediate updating as it is marked for reinitialization. If using queued failover option, run Queue Reader Agent for subscription initialization. Try again after the (re)initialization completes.Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8Updateable Subscriptions: Rolling back transaction.Msg 3609, Level 16, State 1, Line 2The transaction ended in the trigger. The batch has been aborted.", the agent security is mostly the problem. I launch the Replication Monitor

The above error message occurs because Queue Reader Agent has error. The above graph does not include the Distribution agent because the subscriber is using a pull subscription.

Transactional Replication

Transactional Replication uses 3 agents: snapshot, log reader, and distribution.

snapshot and log reader run at Distributor. Snapshot agent accesses the publisher to create a copy of publication and put it in Default Snapshot Folder. Log Reader Agent accesses the publisher for the logs and put them into the Distributor database.

Distribution Agent accesses the Distributor for the logs. The security of distribution agent must be in PAL and a login in SQL server instance of Distributor database.

Make sure that the Default Snapshot Folder of publisher uses UNC (\\server\data) path.

Execute Operating System commands or executables

XP_CMDSHELL

run Operating Systems commands from SQL Server.

1. Enable the feature:

or, using the following command sequence:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

2. Create a proxy ##xp_cmdshell_proxy_account##

If VIP\NICK account with password 'c@nada1' exists,

EXEC sp_xp_cmdshell_proxy_account 'VIP\NICK','c@nada1'

3. Create SQL Login- aLogin and map it to database user: aLogin in master database

CREATE LOGIN aLogin WITH PASSWORD= 'c@nada1'
GO
CREATE USER aLogin FOR LOGIN aLogin
GO

4. GRANT execute permission

GRANT EXECUTE ON xp_cmdshell TO aLogin

5. Log onto SQL server as aLogin

If VIP\NICK has the required permission on the file system, there is no mistake to run the following commands:

exec master.dbo.xp_cmdshell 'md c:\Data'

exec master.dbo.xp_cmdshell 'del d:\data\test\* /q'

======================================

Create Operating System (CmdExec) job step

You must use full path to reference the command. For example,

C:\WINDOWS\system32\notepad.exe

1. Create a credential and a proxy for the Operating System (CmdExec) subsystem

CREATE CREDENTIAL NICK WITH IDENTITY ='VIP\NICK',SECRET='c@nada1'

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'nickCmd',@credential_name=N'NICK',
@enabled=1

2. Create a job with the job step to run:

C:\WINDOWS\system32\notepad.exe

3. Test the job.

So, you could write a batch file to include all the commands and put it into the job step.

Truncating logs--will not shrink the physical log file size

FULL recovery model

Before backing up the log, the log file size is 51,200 KB.

After backing up the log, the log file size is 51,200 KB.

What does it mean about the truncating log?

Log truncation does not reduce the size of a physical log size. However, it does reduce the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.

To actually shrink the physical log file, you should initiate the one of the following commands:

DBCC SHRINKDATABASE
DBCC SHRINKFILE


Example: DBCC SHRINKDATABASE(AdventureWorks)


To shrink the log file more, issue the following:

DBCC SHRINKFILE (AdventureWorks_log)


Do the Database FULL backup and Differential backup truncate the virtual logs in a physical log file?

Let me do the following exercise:

DECLARE @a int
SET @a=1;
WHILE @a<1000 BEGIN update Person.Address SET AddressLine2='deepcove' + CAST(@a as varchar) WHERE AddressID <10 SET @a=@a+1 ENDNotice the log file size becomes 182,272 KB.

DBCC SHRINKDATABASE(AdventureWorks);

DBCC SHRINKFILE(AdventureWorks_log);

The log file size does not shrink much --from 182,272 KB to 174,080 KB.

Create a log backup
and update some records in the database
RUN DBCCSHRINKFILE(AdventureWorks_log)

It will shrink a lot.

The size of the virtual log file is chosen dynamically by the database engine when log files are created or extended.

Correlated sub-query vs. sub-query

Outer query will be: Select CustomerID, SalesOrderID,OrderDate FROM Sales.SalesOrderHeader

1. Outer query provides a CustomerID to inner query.



2. Inner query retrieves the MININUM OrderDate for the CustomerID.



3. Outer query uses the MINIMUM OrderDate to retrieve the record for that CustomerID.
   

That above process will repeat for each CustomerID.

Sub-query: inner query (red color) will not reference the data from outer query.

SELECT * FROM HumanResources.Employee
WHERE EmployeeID IN
(SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate)

Both outer query and inner query are executed one time.

IN, NOT IN, NULL, IS NULL, IS NOT NULL
====================================
IS NULL and IS NOT NULL are not comparision operators.

expression IS [ NOT ] NULL

The expression will not be compared with NULL.
--------------------------------------------------
expression IN (1,4,6,NULL) means that:
It will return true when expression =1; or
It will return true when expression =4; or
It will return true when expression =6;

expression NOT IN (1,4,6, NULL) will return NULL.


DECLARE @a int;
set @a=33;
SELECT 'TRUE' where @a IN(1,2,3,null)
SELECT 'FALSE' where @a NOT IN (1,2,3,NULL)

No output.

expression<>1 and
expression<>2 and
expression<>3 and
expression<>NULL ==>NULL

Domain, Entity, and Referential Integrity



When you insert a record into a table, the domain integrity applies. It does not have the information of the existing table data.



When you insert a record to a table, the data of whole column(s) are considered.

When you insert a record in the referencing table, the referenced table will be checked against.

When you insert a record the table, the field ManagerID will reference the whole column of EmployeeID.

Example of self-referencing:

CREATE TABLE [dbo].[Emp](
[EmployeeID] [int] NOT NULL,
[ManagerID] [int] NULL,
[Title] [varchar](50) NULL,
CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED
([EmployeeID])
)
ALTER TABLE dbo.Emp
ADD CONSTRAINT FK_EmployeeID_ManagerID Foreign KEY(ManagerID)
REFERENCES Emp(EmployeeID)
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(1,NULL,NULL);
UPDATE dbo.Emp
SET ManagerID=1,Title='Sales Manager'
WHERE EmployeeID=1;
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(2,1,'Sales Manager');
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(3,1,'Sales Manager');
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(4,4,'Production Manager');
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(5,4,'Production Manager');
INSERT dbo.Emp (EmployeeID,ManagerID,Title)
VALUES(6,4,'Production Manager');

Attention:

ManagerID allows NULL.

If ManagerID column does not allow NULL, you must insert a record before you apply the foreign key.

Snapshot Publication

Working at Domain Controller: Server

Local Publishing: Publisher and Distributor
Publishing Database: Toronto
Distributor Database: Distribution

SNAPSHOT file folder: D:\Data and its share name is DATA.

Check or modify the Default Snapshot Folder to be D:\Data -- Distributor's Properties and select the Publisher -- Click the far right ... button

Create Windows Logins
VIP\SnapshotAgent and VIP\DistributionAgent

Map VIP\SnapshotAgent to DB_OWNER database role of Toronto Database.
MAP VIP\SnapshotAgent to DB_OWNER database role of Distribution Database.

Create a publication--Addresses
Confirm or modify its snapshot folder to D:\Data. This D:\Data folder has the permissions set up as shown in the above picture.

Right-click on the Publication of Addresses --Press Start button

Check the D:\Data folder

Configuring the Publication to be accessible for Subscribers

The published files in D:\Data folder must be read by VIP\DistributionAgent.

The Publication:Addresses properties --PAL

--Add VIP\DistributionAgent to Publication Access List.

MAP VIP\DistributionAgent to a database user VIP\DistributionAgent.

Configuring a subscriber--PUSH

Distribution database at DC:Server

Make sure that VIP\DistributionAgent to be a member of db_owner role.

Subscriber database BData at the member server: KingPC

Create a Windows Login: VIP\DistributionAgent

MAP VIP\DistributionAgent to database user VIP\DistributionAgent of BData database with DB_OWNER role

Initiating the subscription wizard--Push type

You will find the Addresses table is created in BData database.

Attach or modify a recurring schedule for a snapshot publication

Work at DC:Server

SQL Server Agent -- Jobs

Locate the Server-Toronto-Addresses-# job --Properties --Schedules

The Distribution Agent knows which one to push onto the subscribers.

NOTE:

Look at relationship between Jobs, Proxies, and Credentials?

• Create a credential that has Windows user account and password.
• Create a proxy associated with a credential
• Create a job that uses the proxy

Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.

A proxy associates a credential with one or many subsystems:

• ActiveX script
• Operating System(CmdExec)

etc.

The question is when to drop the old files in Default Snapshot Folder?

Distribution Clean up: Distribution job will take care of it.

PULL subscription

You must configure the Default Snapshot Folder with UNC path: \\Server\Data.

Exchange server 2003

Sender Filter, Recipient Filter, Connection Filter, Sender ID Filter, and Intelligent Message Filter

You want to filter messages delivered to Study.COM.

First, SMTP virtual server of EXCHANGE SERVER 2003 in Study.com

-->properties --General Tab--Advanced button

Select Apply Sender Filter, Apply Sender ID Filter, Apply Recipient Filter, Apply Intelligent Message Filter, and Apply Connection Filter.


Second, Global Settings -- Message Delivery --Properties

Configuring the corresponding filters.

Connection Filter

For example, the Contoso.com DNS name is in Real-time Block List of a service provider AAA. Connection Filter of Study.Com includes the RBL service provider. When Contoso.com sends messages to Study.com, Study.COM exchange server sends a query to RBL service provider AAA. Because the positive match is found, Study.COM SMTP server rejects the SMTP connection from Contoso.com.

Sender Filter

You configure the sender filter of Study.com with ghost@contoso.com. When ghost@contoso.com sends messages to Study.com, it will be rejected.

Recipient Filter

Jane@study.com does not want any messages delivered from Internet. You can put Jane@study.com in the Recipient Filter. It also filters the messages that are directed to non-exist e-mail.

Sender ID Filter

Sender ID refers to SMTP server.

When Study.Com smtp server receives a message, it checks the sender's SMTP server address. The verification is automatically performed by ISP or recipient's mail server before the e-mail is delivered to user.

When do you modify Internet Message Formats?

"Internet Message Formats" does not affect the communication among MAPI mail clients within the same EXCHANGE ORGANIZATION.

When SMTP virtual server sends a MAPI message to an Internet client, it assigns a content type (MIME) to attachments based on the extension of the attachment file.

Right client the "Internet Message Formats", you can modify the MIME types used in your ORGANIZATION.

Control the "Message Formats" for Internet SMTP domain


We have three Internet SMTP Organizations: Study.COM, Delta.Com, and Contoso.com.

Study.COM and Delta.COM use EXCHANGE SERVER 2003, whereas Contoso.com uses Unix Sendmail.

All the three Organizations are partners. All of them support Message Format of HTML and Plain text.

However, Contoso.com (Unix) will never use the Rich Exchange Format.

Only the communication among three Organizations is required to have the following features:
Allow Out of Office Response
Allow Automatic Reply
Allow Automatic Forward


We create two SMTP domains: Contoso.com and Delta.com and modify the Advanced Message Format accordingly.

The default(*) SMTP domain matches all other domains.

UPDATE a table based on other table's records

CREATE TABLE Grades
(StudentID INT NOT NULL PRIMARY KEY,
StudentName VARCHAR(50) NOT NULL,
Grade CHAR(1)
)
GO
CREATE TABLE Addresses
(StudentID INT NOT NULL PRIMARY KEY,
StreetName VARCHAR(50) NOT NULL,
HomeNO INT NULL
)
GO
INSERT Grades (StudentID,StudentName,Grade)
VALUES(1,'Smith','A');
INSERT Grades (StudentID,StudentName,Grade)
VALUES(2,'Smith','B');
INSERT Grades (StudentID,StudentName,Grade)
VALUES(3,'Smith','B');
INSERT Grades (StudentID,StudentName,Grade)
VALUES(4,'Smith','A');
INSERT Grades (StudentID,StudentName,Grade)
VALUES(5,'Smith','A');
GO
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(1,'Albert',2)
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(2,'Albert',5)
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(3,'Netware',23)
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(4,'Netware',12)
INSERT Addresses (StudentID, StreetName,HomeNo)
VALUES(5,'Albert',26)
GO
SELECT G.*,A.StreetName,A.HomeNo
FROM Grades G INNER JOIN Addresses A
ON G.StudentID=A.StudentID
WHERE A.StreetName='Albert'

StudentID StudentName Grade StreetName HomeNo
----------- ------------ ----- -------- -----------
1 Smith A Albert 2
2 Smith B Albert 5
5 Smith A Albert 26

(3 row(s) affected)

GO
UPDATE G
SET Grade='C'
FROM Grades G INNER JOIN Addresses A
ON G.StudentID=A.StudentID
WHERE A.StreetName='Albert'
GO
SELECT G.*,A.StreetName,A.HomeNo
FROM Grades G INNER JOIN Addresses A
ON G.StudentID=A.StudentID
WHERE A.StreetName='Albert'
StudentID StudentName Grade StreetName HomeNo
----------- ------------ ----- -------- -----------
1 Smith C Albert 2
2 Smith C Albert 5
5 Smith C Albert 26

(3 row(s) affected)


NOTES:
You can build a condition based different tables via JOIN. You can apply the condition to UPDATE statement. Always remember that UPDATE applies only one table.

Creating a Address List only for a group of users, such as supervisors.


assuming you have a user account template -- supervisor--with extensionAttribute1 set to supervisor. --User account's Exchange Advanced tab.



And then, you create several user accounts (for example, Sam, Gordon, and Diana) from the supervisor template in Active Directory Users and Computers tool.


In Exchange System Manager, you create a new address list -- supervisors-- with the filter Custom Attribute 1 = supervisor, as shown.


Last, only allow the supervisors to view Supervisors address list.

Multi-location mail delivery

Delivering Internet e-mail uses the local ISP connection, whereas inter-organization e-mail delivery uses the dedicated T1 connection.

Configuration:

Routing Group Connectors are used to connect the Routing Group Vancouver and Routing Group Toronto. It accomplishes the inter-organization e-mail delivery. The connectors are set up for entire organization.

In each routing group, create a SMTP connector with a dedicated SMTP virtual server and make sure that SMTP connector has an SMTP address space of * and restrict the connector scope to the routing group.

Open all mailboxes in a mailbox store

If you want a user Jessie to open all mailboxes in mailbox store, you grant the RECEIVE AS permission to Jessie on Mailbox Store object in Exchange System Manager.

I actually granted the following permissions to Jessie:

Read
Execute
Read Permissions
List Contents
Read Properties
List Objects
Receive AS

Jessie opens its outlook.

Jessie can open additional mailboxes in its outlook. --Jessie mailbox properties -- Advanced button.

When you grant a user has Receive As permissions to a mailbox database, that user can log in
to all mailboxes within that database, but is not able to send e-mail messages from those
mailboxes. Also, if you grant Receive As permissions at the storage group level, the
specified user can log in to all mailboxes within all databases in the storage group. For
example, you may want to grant access to the mailbox database for mobile access or for legal
review.


open only one mailbox

You grant Jessie the FULL MAILBOX ACCESS permission on the mailbox in AD.

Send on Behalf, Send AS, and DELEGATES



Mary is the sender.






The message shows that it comes from Diana. When you open it, pay attention to FROM line, "Mary on behalf of Diana".


























How to set this up?




In Active Directory Users and Computers, locate the Diana user object -- properties


Find the SEND ON BEHALF permission, grant the permission to user: Mary.





Then, Mary can send messages on Diana's behalf.



Permission "Send on behalf" receiver opens OUTLOOK.

DELEGATES

User Jessie opens its OUTLOOK,
TOOLS--OPTIONS--DELEGATES--Add the user Logan to the list.


User Logan opens its outlook and create a new message with FROM selection of jessie user.

The message receiver will get the similar format of SEND ON BEHALF. The receiver sees the message comes from jessie. When the message opens, "Logan on behalf of Jessie" is shown on FROM line.

DELEGATES are used from client's point of view. There are other permissions that can be delegated.

SEND ON BEHALF is usually granted by administrator because you should use the AD tools.



Send AS permission


You create two users: VIPuser1 and VIPuser2.

VIPuser2 sends messages as VIPuser1. You work on VIPuser1 object in AD.


Permission "SEND AS" receiver opens OUTLOOK.

Step 1:

Active Directory Users and Computers --locates VIPuser1 -- Properties -- Security Tab
Assign the VIPuser2 with SEND AS permission.

Step 2:

VIPuser2 opens its outlook and displays FROM option as shown below. Click the FROM button, VIPuser2 can select the VIPuser1 as the sender.

When a receiver get the message, it sees the message comes from VIPuser1. The SEND AS permission is quite useful in customer support. For example, there are 10 customer support technicians. Each has its own e-mail address. You want all the technicians use only one e-mail address support@vip.com to provide support. The customers only send e-mail to support@vip.com.

How to achieve the goal?

Create a AllSupport security group and add all the 10 technician to the member list;
Create a distribution group: SUPPORT with alias Support@vip.com and add all the 10 technician to the member list;
Assign the AllSupport security group the SEND AS permission on SUPPORT distribution group object;

All customers send the service request messages to Support@vip.com. All technicians receive the messages. When a technician replies the message, it can select the Support@vip.com address in FROM button. The customer will see the support email come from Support@vip.com.













All three permissions' grantee must open its OUTLOOK and initiate messages and select the grantor in FROM line.

Open two or more mailboxes in Outlook

Outlook can access only one primary mailbox. The example will show you how to set up Outlook to open two mailboxes.

First, set up Outlook with VIPuser1.Smarthust@vip.com.
Second, in VIP.com domain, locate the VIPuser2.Newton user account and assign the Full Mailbox Access right to VIPUser1.Smarthust.
Third, in Outlook, right click VIPuser1.Smarthust mailbox and select properties, locate "Open these additional mailboxes". Then, you will get the similar outlook layout shown below.

Exchange server 2003

Limiting the addresses that Outlook can see



Exchange System Manager


First, remove everyone, authenticated users anonymous Logon from the following address list under the recipients:

All Contacts

All Users

All Groups

Default Global Address List



Second, create a customized address list, for example, Toupe Corporation with the following filter:



(&(objectCategory=user)(memberOf=CN=Toupegroup,OU=ToupeCorporation,DC=VIP,DC=COM))



In order for the above filter working perfectly, you must create an ToupeCorporation in VIP.COM domain and a Toupegroup group (distribution type if you include the contact in the group) in ToupeCorporation OU. And then, add all users in Toupe Corporation to the member list of Toupegroup.



Third, set up security for only users in Toupe Corporation access.



You cannot create a filter based on OU, because OU is not a attribute of a user object, contact object and group object. However, a group object has the memberOf attribute.



For the contact object filter:



(&(objectCategory=contact)(memberOf=CN=Toupegroup,OU=ToupeCorporation,DC=VIP,DC=COM))



With both contact and user object filter:



((&(objectCategory=user)(memberOf=CN=Toupegroup,OU=ToupeCorporation,DC=VIP,DC=COM))(&(objectCategory=contact)(memberOf=CN=Toupegroup,OU=ToupeCorporation,DC=VIP,DC=COM)))

-- or
& -- and

Take a look at the screenshot, you should put a pipe sign after the first (. I put it several times. It magically would take it in the blog site.




To input the filter, you must use the custom search.

Based a table A, to delete record(s) from a table B

USE Adata
GO
CREATE TABLE Customers
(CustomerID INT PRIMARY KEY,
Fullname VARCHAR(50) NOT NULL,
AccessDate Datetime DEFAULT Getdate()
)
GO
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
ProductName VARCHAR(50) NOT NULL
)
GO
INSERT INTO Customers VALUES(1,'John Smith','May 1, 2002');
INSERT INTO Customers VALUES(2,'Kevin Smith','May 4, 2002');
INSERT INTO Customers VALUES(3,'Devin Clarke','May 5, 2002');
INSERT INTO Customers VALUES(4,'Inkaran Tharmas','May 7, 2002');
GO
INSERT INTO Products VALUES(1,1,'Coffee Bean');
INSERT INTO Products VALUES(2,1,'Shriek Movie');
INSERT INTO Products VALUES(3,1,'DDR RAM');
INSERT INTO Products VALUES(4,2,'Coffee Bean');
INSERT INTO Products VALUES(5,2,'Tool Kit');
INSERT INTO Products VALUES(6,2,'Hose');
INSERT INTO Products VALUES(7,2,'Soy Bean');
GO
SELECT * FROM Customers

CustomerID Fullname AccessDate
----------- ------------------------- -----------------------
1 John Smith 2002-05-01 00:00:00.000
2 Kevin Smith 2002-05-04 00:00:00.000
3 Devin Clarke 2002-05-05 00:00:00.000
4 Inkaran Tharmas 2002-05-07 00:00:00.000

(4 row(s) affected)

In the Products table, only two customers order products.
You want to delete the records that don't have the order history in Products table.

DELETE FROM Customers
FROM Customers C LEFT JOIN Products P
ON C.CustomerID=P.CustomerID
WHERE P.CustomerID IS NULL;
GO

SELECT * FROM Customers

CustomerID Fullname AccessDate
----------- -------------------------- -----------------------
1 John Smith 2002-05-01 00:00:00.000
2 Kevin Smith 2002-05-04 00:00:00.000

(2 row(s) affected)

A new email address generated and old email address kept

You have a recipient policy for all exchange organization as follows:
%g.%s@van.com.

User

FirstName: Lina
LastName: Smarthurst
DisplayName :Lina Smarthurst
FullName:Lina Smarthurst

Her email address will be Lina.Smarthurst@van.com.

Her lastname is changed to Smiths because of new marriage. She wants the new lastname will be used in her email address and her new friendly name (Lina Smiths) will appear in her outgoing e-mail address. She still receives e-mail targeted to old e-mail address.

How to achieve that?

Modify her account as follows:

LastName: Smiths
DisplayName: Lina Smiths

%g -- FirstName
%s -- LastName

Friendly Name will come from the DisplayName attribute of the account.

When the recipient policy runs, it generates a new email address based on the FirstName and LastName. Any existing email address will be kept.


However, when you open Active Directory Users and Computers, the Lina Smarthurst still shows, because the Active Directory Users and Computers Interface uses the FullName attribute of a user account. To modify the fullname, right click an account -- rename.

universal group and expansion server

a mail-enabled Universal Security Group: allToronto is created and all users of Toronto are members of the group.

The VPN between Toronto and Vancouver is constantly busy.

When users in Vancouver send e-mail to the allToronto group, some recipients receive the messages after several minutes, but other recipients receive it several hours later.

Solution:

Creating a routing group A in Toronto and a routing group in Vancouver.

Placing EXCH1, EXCH2, EXCH3 and EXCH4 in routing group A. Place EXCH5 and EXCH6 in routing group B.

Creating connectors to send messages between the two routing groups.

Reason:

When sending messages to a distribution group in one routing group configuration, the exchange server will send the message directly to individual member of the group. Microsoft recommends to create routing groups configuration between exchange servers when you have slow or unreliable link. When routing groups are created, group membership expansion will occur at the destination exchange server.

Message Tracking Center

Message Tracking Center tracks messages across exchange servers.

EXCH1 is the bridgehead server for a routing group connector to SiteB.

EXCH2 is the bridgehead server for a routing group connector to SiteC.

You use Message Tracking Center on EXCH3 to track the flow of messages sent from EXCH3 to EXCH3 at SiteC. However, you only see the progress to EXCH1. How can you track the messages reaching the EXCH4 at SITEC?

Solution:

Enable the Message Tracking on Exchange Server EXCH2 and EXCH3.

VPN for Local Connection and no VPN for Internet

To achieve the following goals:

Branch Office accesses resources in Head Office the VPN connection is used.

Branch Office accesses other resources such as Internet, no VPN is used.

How to configure the VPN connection to Head Office in Branch Office?

VPN connection properties -- Internet Protocol (TCP/IP) --Advanced button

-- uncheck "Use default gateway on remote network"

If you leave the "Use default gateway on remote network" selected, all communication from Branch Office to outside will go through VPN to Head Office, including the Internet access.

Exchange and Global Catalog

One day, you cannot start the Exchange Service in Exch2 of branch office because the T1 connection is broken.

How can you ensure that Exchange Service in Exch2 starts even T1 connection is broken.

Solution: Configure the DC2 to be Global Catalog server.

If the users in branch office report that the email address resolution to user name is slow, you should configure the DC2 to be GC.

Every Outlook client is configured with the name of an Exchange server. Exchange servers use Active Directory and DNS to locate a global catalog server. When an Outlook client user opens the Address Book, or when a user composes a message and types a name or an address in the To: field, the Outlook client uses the global catalog server that is specified by its Exchange server to search the contents of the GAL or other address lists.

DSaccess --Directory Access


Exchange use DSaccess service to find a set of available directory service servers: GC, Configuration, and DC.


By default, DSAcess is configured to perform "Automatically discover servers".

Scenario: Each office has one exchange server 2003 to host all mailboxes for users in that office. Users in branch office report that sometimes it takes several minutes to send email from EXCH2. You discover that a large amount LDAP queries is passed from the branch office to DC1 of head office. You confirm that DC2 is configured as Global Catalog server.

Solution:

Exchange System Manager-- locates the Exch2 object -- properties -- Directory Access tab -- Specify the DC2 as the dedicated Global Catalog server.

Front-end exchange server located in perimeter network

The ISA firewall is configured to permit Internet users to access their e-mail to back-end exchange server. Internal users can access back-end exchange by OWA. Internet users cannot access their mailboxes by OWA.

Solution:

Configure the default gateway of Front-end network adapter to 192.168.1.254.

configure a static route in Front-end server

Route -p add 172.16.1.0 mask 255.255.0.0 172.16.1.253

Exchange server 2003

Mailbox storage limit

There are three ways to set up the storage limits: mailbox policy, mailbox store, and individual user.

Scenario 1: All users except the CEO have storage limit of 300MB. CEO has unlimited storage.
Solution: set the storage limit at mailbox store level to 300MB. configure the CEO user properties with unlimited storage setting.

Scenario 2: Managers, CEOs, and IT staff have storage limit of 500MB. All other employees have the 100MB storage limit.
Solution: Mailbox store 1 is dedicated to Managers, CEOs and IT staff. Set its storage limit to 500MB. Mailbox store 2 is for all other employees and set its storage limit to 100MB.

Scenario 3: You have 50 mailbox stores in your exchange organization. The first 30 mailbox stores have 100MB storage limit. The remaining 20 mailbox stores have 500MB storage limit.
Solution: create mailbox store policy 1 with storage limit set to 100MB and associate the policy to the first 30 mailbox stores.
create mailbox store policy 2 with storage limit set to 500MB and associate the policy to the remaining 20 mailbox stores.

Exchange server 2003

RPC Endpoint Mapper

The RPC End Point Mapper always listens on port 135.

When an Exchange server starts, each of its services (not HTTP, SMTP, POP3, or IMAP) is dynamically assigned to a port. The port number of Exchange server listens on can change every time that the Exchange server starts. When an Exchange server starts, it registers its port number to RPC End Point Mapper.

When Outlook starts, Outlook contacts the Exchange server's RPC End Point Mapper (port 135) for exchange service registered port number, e.g. 4001. And then, Outlook communicates with Exchange Service on port 4001.

Both Microsoft Outlook and Microsoft Exchange use the remote procedure call (RPC) protocol to communicate.

Exchange server 2003

Restore all the Exchange mailboxes without losing any data

One storage group with one mailbox store.

Mailbox store database file is located in D: drive.
The storage group log files are located in E:drive.

Every night, you perform full backup.

D:drive is crashed. You replace a new one.

How do you restore the database without losing any data?

Solution:

Because all log files are intact, you restore the mailbox store file but not the transaction log files from the backup set. And then, replay the existing transaction log files by deleting the check point file.

Exchange server 2003

Front-End and Back-End, Non-delivery Report

Exchange server: Lou is the front-end server.
Exchange Server: Sam is the back-end server.

Lou supports the OWA connection. Lou is configured as a relay for all incoming and outgoing SMTP messages.

The company uses the data.com for all SMTP addresses.

Users report that when e-mail (destined to Internet) cannot be delivered, they don't receive the Non-delivery reports (NDRs).

The reason is that the NDR generated at Lou is not relayed to Sam.

Solution:

Create a SMTP connector with the namespace of data.com and specify the Sam as a smart host.

Exchange server 2003

Front-End and Back-End

Front-end server will not host mailbox and public folder.

You can disable some services in front-end server.

The Exchange Information Store service supports data storage (mailbox and public folder). Since all mailboxes and public folders are hosted in the back-end servers, you can disable this service.

Microsoft Exchange MTA Stacks service supports message routing to foreign messaging system using X.400 and gateway connectors. It is not a required service on a front-end OWA server.

If you do not want the clients access their email by POP3, you can disable POP3.

Exchange server 2003

Sender Filtering

You have two exchange servers. Both can send email to Internet directly. In Exchange System Manager, you configure the Sender Filtering to reduce the junk mail. You specify a list of known junk mail senders in the blocked-sender list.

However, the users report that they still receive the junk e-mail from the blocked-senders.

solution:

Enable the Sender Filter on both Exchange Server SMTP virtual servers.

Exchange server 2003

When to use ESEUTIL /R

An external event unexpectedly stops an Exchange database, but the database and log files remain intact and in place. For example, the disk controller is broken, the hard drive is intact.

After you replace the disk controller, if you cannot mount the mailbox store, you can run eseutil /R to run soft recovery.

Generally, you should always run Eseutil /R from the folder where the transaction log files to be replayed exist. This is because the default soft recovery process looks in the transaction log files to find the path to the databases.

If you run Eseutil /R from a folder where no log files, you should use syntax similar to the following:

Eseutil /R E00 /Ld:\exchsrvr\logfiles

This command will bring the database into a consistent state. You should be able to mount the database.

If you receive errors when mounting the database, it is possible the database is corrupted. The eseutil /p can be used to repair the database with possible data loss.

Then mount the database.

Exchange server 2003

Last Backup Set or /ESEUTIL /CC

When you restore your mailbox store data from backup, there is the Last Backup Set option. When Last Backup Set option is selected, after the database is copied to their original location, and the transaction log files from the temp directory are replayed into it -- Hard recovery.

Any additional transaction log files that it finds in the transaction log file path specified for the storage group of the restored database will be replayed when the store is remounted -- soft recovery.

Eseutil /cc performs the same function as the Last Restore Set check box.

A single storage group stores its transaction logs in E:\Exchsrvr\Mdbdata

A single mailbox store is in F:\Exchsrvr\Mdbdata.

F:drive fails. You replace the disk and create new mailbox store file in the new disk. You restore the mailbox store from backup and configure C:\temp as the location for the transaction log files.

After restoration, you get error message when trying to mount the store.


When the Last Backup Set is not selected during the restoration, After the databases are copied to their original location, and the transaction log files from the temp directory are not replayed. The database is in inconsistent state. It is unmountable.

solution one: run eseutil /cc against the c:\temp directory

Solution two:

You can mount it with possible data loss after you accomplish the following:
Delete the transaction log files from E:\Exchsrvr\Mdbdata
Delete the checkpoint file from E:\Exchsrvr\mdbdata
run eseutil /d on the database to rebuild the indexes.

Exchange server 2003

Unstanding backup strategies of Exchange Server

Full backup -- backs up the mailbox store and transaction log files. After the backup, backed transaction log files are deleted.

Incremental backup -- backs up the transaction logs and deletes the backed transaction log files.

Differential backup -- backs up the transaction logs and will not delete any transaction log files.

Exchange server 2003

When to use Recovery Storage Group


recover individual user mailbox and use exMerge tool to merge the backup data with the existing mailbox.

To use the feature, you must have a working mailbox store. You mount the mailbox store from backup in the same computer or on another exchange server that is in the same administrative group.

If your mailbox store cannot be mounted because of data corruption, you cannot use the Recovery Storage Group feature. You should restore the mailbox store from backup and mount it.

When you create the Recovery Storage Group, if you cannot mount the mailbox store you add it to the Recovery Storage Group, it is possible that the directories of Recovery Storage Group database file and log files are not empty.

Exchange server 2003

Different delivery schedule for oversize messages

VAN.COM forest has two Exchange Servers. All mailboxes are created Exch1. Exch2 has no mailbox. Exch2 is a bridgehead server. An SMTP connector is created in Exch2 for Internet mail delivery.

You create corresponding contacts for mailbox-enabled users in Contoso.com forest.

Goal:

VAN.COM can send 20MB attachments to Contoso.com.

You don't want these attachments to delay delivery of other messages to Internet.

Solution:

Create a new SMTP virtual server and a new SMTP connector

The new SMTP connector configuration:

• using this new SMTP virtual server
• address space set to *.contoso.com
• delivery restriction set to 20MB
• Forward all mail destined to *.contoso.com to smart host: 10.1.100.50
• Delivery option--select "Use different delivery time for oversize messages" and set oversize to 2000 (kb) and customize your delivery schedule.

Remember that SMTP connector settings will override the settings of smtp virtual server.