SharePoint world of ECM and Information Management

SQL Encryption Overview

In these days I’m working on SQL encryption, and would like to post best guidelines for this

1. Choose from Symmetric vs Asymmetric encryption, based on the security of key distribution.

Symmetric encryption algorithms are historically computationally fast, which makes them a good choice when encrypting large amounts of data or when key distribution is not a concern

2. Protect “Data-in-motion” – transferred data by selecting the right protocol and “Data-at-rest” – stored data.

3. Choose from encryption in Database Level vs Row/Collumn Level.

Database level encrypt the whole data file and each db manipulations request data decryption, which hinders performance for big db.

4. Prefer to create Certificates rather then use pass-phrases or keys

It simplifies key management and you don’t need to use your pass or pass-phrase in each stored proc

5. Generate you keys with the most advanced algorithms (AES for Symmetric for example, but it request Win2k3 and above)

Resources:

1. http://aspnet.4guysfromrolla.com/articles/021407-1.aspx
2. http://aspnet.4guysfromrolla.com/articles/022107-1.aspx
3. http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/sql2005symmetricencryption/2291/

Mirror: SQL Encryption Overview

Best Practices to plan and configure your SharePoint Farm across corporate infrastructure

I my previous post I described list of documents you need to manage for your SharePoint engagement.

In this post I’d like to publish my “best practices” for planning and configuring your SharePoint Farm across organizations. There are number of documents, describing such requirements for SharePoint Farm, but those documents are usually writing without taking into account your infrastructure scope. You never install SharePoint in isolated environment, and what you setup is usually interfere with the whole organization strategy and infrastructure design.

Infrastructure

1. Inspect your organization network topology to know where all application servers are installed (AD, File Server, Mail Servers and etc) and location of networ services  - firewalls, proxies and etc;
2. Find out all hardware devices - accelerators (WAN, XML), routers and etc. across your network. This information affects your selected SharePoint Farm topology.
3. Find out the person who administrate corporate firewalls. Firewalls affect the SharePoint farm design from the very beginning and you need to open number of ports during configuring farm.
4. Plan your farm topology - intranet and extranet, based on the info gathered earlier. I usually prefer to achieve “6+ server farm with Back-to-back perimeter topology with content publishing”, with some variations, depending on the client’s requirements. I like this topology, because it provides good availability, reliability and security.
5. Measure the latency to different SharePoint Server Role boxes (Query, Index, and etc servers) according your design to reorganize your boxes and Server Roles to have the fastest response time and design your SharePoint farm properly.
6. Install Microsoft Office 2007.
7. Install all Windows Updates and Service Packs.
8. Start SharePoint installation with WSS/MOSS + SP1 integrated. Don’t go with WSS/MOSS installation and then applying SP1, because this way has some issues  - small bugs with installing future updates
9. Check if SSL works (open SharePoint Office Web Service via https://), if not – don’t go further till you fix it. This is very critical stuff, because otherwise your can’t set SharePoint roles on other boxes in your farm. I had some negative experience in this area, when you can't use other boxes in your farm and only Application boxes are available for Index and Query roles, because SSL was broken.
10. Install the latest Infrastructure Upgrade after you setup your farm (August at the current moment, not July), But be careful and refer to documentation how to do it correctly (start with WSS Upgrade and only after install MOSS upgrade). Follow the right sequence of installing all updates Service Pack->The latest Infrastructure Update –> All the latest Cumulative  Updates. Refer to this detailed instruction
11. Don’t install different SharePoint hotfixes, if they are not published in Infrastructure Updates.  Do it only if you have very specific problems which really cause you troubles. Those hotfixes are temporary solution before the next Infrastructure Update, and they are not properly tested.
12. Check if SSL for SharePoint Office Web Service works again.
13. Plan how much site collections and sub sites you need. I prefer to have one site collections with number of sub sites rather than create several site collections. The reason for this is that each new site collection server like a new web application with isolated scope to features, templates and search. You hardly need this. So, start with only one site collections, and try to avoid number of new site collection if you don’t have solid reasons for this.
14. Plan your site collection and sites location among content databases. Don’t end up with one big content database, because you can’t optimize your data usage in this case. Refer to my recent post, where I described why you need to plan your content databases: http://msmvps.com/blogs/laflour/archive/2008/10/14/tips-to-create-a-site-collection-in-new-content-database.aspx
15. Setup SQL mirroring when you have empty content databases. Your DR box usually locates outside your organization with limited connections, and setting mirroring for big content database is very slow process.
16. Save all your command and prefer to script everything you do during installing and configuring SharePoint Farm, because it saves your time when something going wrong and you need to reinstall your box.

Development

1. Use separate SharePoint installation (different from Production) for development, because production boxes should be clean, without all those development tools you are going to use. I  usually use virtualized environment
2. I prefer to have .NET 3.5 and VS 2008  with SQL 2008 + TFS 2008 servers for development environment
3. “Visual Studio WSS Extensions” simplifies your work, but only if you are working on “proof-of-concept”. I prefer not to use it in real development, because there are some bugs, and you can use several alternative tools  http://laflour.spaces.live.com/blog/cns!7575E2FFC19135B4!1047.entry
4. Setup Visual Studio SharePoint solution via the following template, http://blogs.msdn.com/gderun/archive/2008/01/11/sharepoint-visual-studio-project-and-deployment-templates.aspx . It simplify moving you package builds to TFS

Post-Installation Tasks

1. Maintain your database to improve performance. Follow this guideline

That's all. I welcome to hear some of yours "best practices", if you have any

Mirror: Best Practices to plan and configure your SharePoint Farm across corporate infrastructure

Tips to create a Site Collection in new Content Database

Intro

Ken Zheng posted a good overview of how to create a Site Collection in new Content Database http://littletalk.wordpress.com/2008/10/10/creating-a-site-collection-in-a-new-content-database/

I’d like to add some notes to Ken’s post, regarding planning site collections and sub-sites among different content databases.

There are some reasons, why you could have several content databases:

• MS recommends to have content database not more the 100Gb, otherwise it could hinder performance.
• Data usage optimization
• It simplifies farm backup and restoration.
• It provides flexibility for Disaster Recovery (DR) strategy.

Scenario - Data Usage

The major advance of several content databases is splitting your site collection and sub-sited among several content databases based on the content type and content usage. For example, you can have media sites and media data stored in separate content database and separate SQL server, which is tuned, indexed and partitioned to work with BLOB data; or sites which are used for document management, in separate SQL server with content database optimized for textual information.

If you will store different type of content information inside one big single content database in won’t help you to use all advantages of SQL Server to optimize your data usage.

Scenario – Backup/Restore

Having several content database makes you data restoration faster, because you need to restore only that content database, where origin site/data located. 

Scenario – DR

Usually when you implement DR strategy your DR box locates outside current network and outside the current organization, and you could have slow network connection there (reasonably slower then inside your network). So, setting SQL mirroring takes a lot of time if you have very big database. Moreover, with the different content databases you can leverage the importance of your data and mirroring to different locations, for example mirroring your vital financial data in one content database to the box outside your enterprise, and other content databases inside your network. 

Tips and Tools

Ken describes how to create new sites in new content databases, but what if you already have 200Gb content database and what to split and reorganize your data across several new content databases?! SharePoint doesn’t provide OOTB UI features for this. The standard way to reorganized you data for the sub-sites is to use STSADM tool (I will discuss moving site collections separately).

The way to move your sub-sites to new content database is to follow the next steps

1. Go to Central Administration > Application Management > Content Databases and set the existed content database to “Offline” mode
2. Add new content database in Central Administration > Application Management > Content Databases. (you can use different SQL server). Now all new sites will be created in this content database
3. Use the following command to export your site to the disk “stsadm -o export -url http://<path_to_site> -filename c:\<exportedSite>.exp -overwrite -nofilecompression -includeusersecurity -cabsize 1024”.
4. Delete your exported site collection in Central Administration > Application Management > Delete Site Collection
5. Create new empty location with the same path, via stsadm –o createweb/createsite;
6. Import you site to the same url, and site will be created in the new content database. Use the following command for this “stsadm -o import –url http://<path_to_site> -filename c:\<location_of_your_exorted_package>.exp -nofilecompression -haltonfatalerror –includeusersecurity”

Now you will have you sub-site in new content database. It’s not very user friendly approach, and is an error proned a bit.

But there is one small tool - SharePoint Administration Tookit, which helps you to to reorganise the whole site collections. This feature will install a new section inside “Applications”and you can move your site collection via Central Administration interface. 

Resume

Plan you site collections / sub-sites content with you database administration guys

Take into account that you can’t split site and site content between content databases, for example you can’t specify storing all site’s *.avi files in separate content db. I hope it will be changed in the next version of SharePoint, but now site and site content are stored together.

Mirror: Tips to create a Site Collection in new Content Database