Scott Lowe takes a look at a recently completed project he managed as CIO at the college where he works. Here are his tips for synchronizing Exchange with a corporate or organizational database.
In my day job, I'm the CIO for a small, private college in the Midwest. My team and I recently completed an Exchange-related project that I thought was interesting and useful, so I thought I'd share it here.
The college where I work has just under 1,000 students, and our primary communications mechanism is e-mail. To announce an event, a campus group e-mails one or more main distribution lists we have created. There is a list for faculty, one for staff, and one for students. We've put policies and limits in place so that only authorized users can e-mail these lists, but you can probably imagine that students tend to get a lot of mail.
Recently, we were asked if there was a way to create additional distribution lists that are subsets of the primary lists. That way, for example, the college (or other approved students) could e-mail just the students in a particular dorm or in a particular fraternity and keep some of the traffic off the main lists. We have worked up what looks to be an easily maintainable system.
The challenge: Students move all the time! Between roommate disputes, moving off campus, whatever the case, students move around a lot, making a manual system unworkable.
As you would probably expect, we have a database that has all of our student information that we need for this purpose, including where the student lives on campus and a list of the organizations they are involved in. In this database, we also have the student's campus login ID, which conveniently matches the Active Directory login name.
Here are the basic steps we've taken to accomplish our goal of synchronizing the databases:
- Downloaded and installed AD Bulk Users tool, an insanely cheap utility from Dovestones Software (www.dovestones.com)
- Created a view in SQL Server that has the information we want to synchronize with each Active Directory account. Each column in the view is named after a particular Active Directory field. For example, the users account in the view is named sAMAccountName. This is how AD Bulk Users works its magic
- Ran AD Bulk Users against the view to update all of the AD user records with the necessary information. We loaded the student's housing information, Greek affiliation and class year into AD's user-defined fields.
- Created Dynamic Distribution Lists with parameters that base each list on the specific criteria. For example, we created a list for each dorm on campus.
Our next steps will be to set up a nightly batch to update student's AD information so that the new distribution lists are accurate to within a day. Eventually, we would like more frequent updates but for now, daily will suffice.