In any SharePoint project, sometimes there is a need to create an external custom RDBMS database to host non-related SharePoint information outside SharePoint's Content Database for example user credentials for form-based authentication, look up data, etc. To do this, the obvious solution is to use either one of following techniques:
- provide customer with .SQL file and installation steps
- provide customer with .MSI file and installation steps
None of the above technique is wrong!
However, I found technique no. 1 (install custom RDBMS database using .SQL file) is time-consuming and more prone to error - especially if customer who doing installation is not technical at all or the .SQL file doesn't obey the rules. For technique no. 2 - you need strong knowledge in Microsoft Windows Installer packaging tools and you need to download/configure MSI Redistributable package if you don't have one. So, the best solution is to utilize the SharePoint Feature Framework.
Features are the backbone of SharePoint development because every custom development project can and really should be deployed as a feature. Features give tremendous control over SharePoint configurations and capabilities at the administrator level. This means that developers can create features and then turn them over to SharePoint administrators without having to get involved repeatedly in small configuration changes.
I have developed a REUSEABLE FEATURE(SharePointMalaya.Feature.CustomDBInstaller.dll) that able to automate the installation of custom RDBMS database to any SharePoint’s database server. All you need to do is to generate SQL scripts using MS Server Management Studio, save the SQL scripts to SharePointMalaya.Feature.CustomDBInstaller feature folder, build WSP, install and activate the feature at SharePoint site.
SharePointMalaya.Feature.CustomDBInstaller feature comprises of SharePoint Event Handler which inherits from base abstract class SPFeatureReceiver to trap the activation, deactivation, installation, or uninstallation of a Feature. See codes below:
Get source code here:
The following are four (4) simple steps to update the SQL scripts to SharePointMalaya.Feature.CustomDBInstaller feature folder, build WSP, install and activate the feature:
Step 1: Generate SQL Scripts using MS Server Management Studio
- See http://technet.microsoft.com/en-us/library/ms178078.aspx on how to Generate a Script using SQL Server Management Studio
Step 2: Drop the SQL Scripts to either 'Install' or 'UnInstall' folder
- For SQL Scripts that install database objects, add script files to \12\TEMPLATE\FEATURES\ Customware.CustomDBInstaller\T-SQL\Install feature folder
- For SQL Scripts that uninstall database objects, add script files \12\TEMPLATE\FEATURES\ Customware.CustomDBInstaller\T-SQL\UnInstall feature folder
- Use <<SEQUENCENUMBER>>-<<FILENAME>>.sql as script filename to control the execution of SQL Scripts, see below:
Step 3: Build WSP
- Right- click on the Project, select WSPBuilder > Build WSP from the menu (if you don't have WSPBuilder, download and install from CodePlex)
Step 4: Install and Activate SharePoint Feature
- Install the .WSP file to SharePoint site, at command prompt execute the following commands:
- stsadm -o addsolution -filename SharePointMalaya.Feature.CustomDBInstaller.wsp
- stsadm -o deploysolution -name SharePointMalaya.Feature.CustomDBInstaller.wsp -url http://<<SHAREPOINTURL>> -local –allowgacdeployment
- Go to SharePoint Site Collection Features page (i.e. http://<<SHAREPOINTURL>>:<<PORTNUMBER/_layouts/ManageFeatures.aspx?Scope=Site), activate the SharePointMalaya.Feature.CustomDBInstaller feature: