Tuesday 30 January 2007

How to install Subversion 1.4.x with Apache 2.2.x and mod_ssl on Microsoft Windows

General guidelines to install Subversion 1.4.x with Apache 2.2.x and mod_ssl on Microsoft Windows. Use the following instructions at your own risk.

Subversion

The home page of the Subversion project is http://subversion.tigris.org.
The files we need to install Subversion 1.4.x with Apache 2.2.x are currently on this folder, and as for the 25th January 2007, the needed binary package is svn-win32-1.4.3.zip (this file has to be the one builded for Apache 2.2.x).
To install Subversion 1.4.x take the following steps:
  • Unzip the svn-win32-1.4.3.zip file then move its content on a directory as %ProgramFiles%\Subversion (or %ProgramFiles(x86)%\Subversion on X64 systems);
  • Add %ProgramFiles%\Subversion\bin to the environment variable path.
  • Optionally, look for the hot-backup.py script on the source code of Subversion (ideally, get it from the same SVN branch/tag from which the previous binary package was builded) and save it in the %ProgramFiles%\Subversion\tools\backup directory (create it if needed).
  • Create the repository container directory (i.e. e:\repositories);
  • Create the configuration directory (i.e. e:\repositories\etc);
  • Create the backup directory (i.e. e:\repositories\backup);
  • Create a svnserver.conf file inside the configuration directory (i.e. e:\repositories\etc\svserver.conf);
The svnserver.conf file may look like this:
<location>
DAV svn
SVNPath e:/repositories/myProject
SVNIndexXSLT /svnindex.xsl
AuthName "Subversion Authentication"
AuthType SSPI
SSPIAuth On
SSPIAuthoritative On
SSPIDomain INTERNAL
SSPIOfferBasic On
Require valid-user
AuthzSVNAccessFile "e:/repositories/myProject/authorization.conf"
</location>
Note that is just an Apache directive, that obviously you may use more than one such directive if you plan to manage more than one SVN repository, and that this is how it would look like in the case of Active Directory authentication. To get an introduction to the Authentication, read the Authentication Options section of the SVN Book.

Apache

The home page of the Apache HTTP Server project is http://httpd.apache.org, but because we wish to use mod_ssl, and because for various reasons the Apache community doesn't provide a binary package for Microsoft Windows with such a module, another place to look for those binaries is Steffen Land's Apache Lounge.
The files we need to install Apache 2.2.4 are currently on this folder, and as for the 25th January 2007, the needed binary package is httpd-2.2.4-win32-x86-ssl.zip. Given that this binary is build with Microsoft Visual C++ 2005 SP 1, you may need to install the latest Visual C++ 2005 Redistributable Package (i.e. from here).
Another quite useful resource that provides binary packages for Apache is the Unofficial Apache webserver binaries / module binaries, where we can find a graciously packaged mod_auth_sspi. To install Apache 2.2.x take the following steps:
  • If you have IIS installed on same machine, stop it now. If you wish to use Apache and IIS on same machine using port 80 on different IP addresses, you should understand what Socket Pooling is, and how to disable it (in IIS 5 and IIS 6);
  • Unzip the httpd-2.2.4-win32-x86-ssl.zip file then move its content on a directory as c:/Apache2;
  • Copy the unzipped mod_auth_sspi.so in the c:/Apache2/modules directory;
  • Copy the mod_dav_svn.so and the mod_authz_svn.so modules from the %ProgramFiles%\Subversion\bin directory in the c:/Apache2/modules directory;
  • Tweak with the c:/Apache2/conf/httpd.conf configuration file until you will not get Apache working. How to tweak that configuration file to get Apache working is out of the scope of this document, but usually it involves fixing a few file system paths, IP numbers, DNS names and port numbers, and testing these fixes invoking Apache with the -t switch (i.e. as in C:\Apache2\bin\httpd.exe -w -t -f "C:\Apache2\conf\httpd.conf" -d "C:\Apache2\.") until there are not configuration error reported. Once Apache is properly configured, backup the httpd.conf file, and then start editing that:
    • In the section entitled # Dynamic Shared Object (DSO) Support, after the line starting with #LoadModule auth_digest_module modules/mod_auth_digest.so, add the LoadModule directive to load the module to support the Active Directory:
      LoadModule sspi_auth_module modules/mod_auth_sspi.so
      To get more informations about this module, check its home page;
    • In the same section, uncomment the lines to load the modules to support WebDAV:
      LoadModule dav_module modules/mod_dav.so
      LoadModule dav_fs_module modules/mod_dav_fs.so
      To read more about those modules, read the Apache documentation about mod_dav and mod_dav_fs;
    • After the latter lines, add the following lines to load the modules to integrate Subversion:
      LoadModule dav_svn_module modules/mod_dav_svn.so
      LoadModule authz_svn_module modules/mod_authz_svn.so
      To read more about those modules, read the sections of the SVN Book about mod_dav_svn and mod_authz_svn;
    • Uncomment the module to support ssl:
      LoadModule ssl_module modules/mod_ssl.so
      To read more about this module, check its home page;
    • Uncomment the following Include directive:
      Include conf/extra/httpd-ssl.conf
  • Start editing the httpd-ssl.conf file:
    • Set the SSLCertificateFile Directive, as in:
      SSLCertificateFile c:/Apache2/conf/ssl/yourwebsitecertificate.crt
    • Set the SSLCertificateKeyFile, as in:
      SSLCertificateKeyFile c:/Apache2/conf/ssl/yourprivatekey.key
    • Add the following line, before closing the VirtualHost directive:
      Include c:/repositories/etc/svnserver.conf
    Obviously, how to generate the private key, its correspondent .csr and the web certificate its widely outside of the scope of the present document, anyway documents as The Apache + SSL on Win32 HOWTO, the Generating an SSL Certificate with Apache+mod_ssl, the mod_ssl FAQ List (especially this entry) should be quite useful, as well the CAcert documents and services.

Friday 26 January 2007

TSQL Script to get the space used by every table in a SQL Server 2005 database

Here is a TSQL script (developed, tested and used only on Microsoft SQL Server 2005) to get the space used by the tables of a database.
declare @sql nvarchar(MAX)
create table #usedSpace (
name nvarchar
(128),
rows varchar(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18))

declare @table nvarchar(MAX)
declare tabcur cursor for
select s.[name] +'.'+ t.[name] as [name]
from
sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id
order by
s.[name], t.[name];

open tabcur;
fetch next from tabcur into @table;
while @@FETCH_STATUS = 0
begin
fetch
next from tabcur into @table;

select @sql = 'sp_executesql N''insert #usedSpace exec sp_spaceused ''''' + @table + ''''''''
-- print @sql -- uncomment if you wish to read the sql statements
exec (@sql)
end;
close tabcur;
deallocate tabcur;
-- select * from #usedSpace order by cast((left(Data,len(Data)-3)) as int) desc -- uncomment if you wish to get a recordset
drop table #usedSpace


Use it at your own risk, uncomment the select (or substitute that with your more appropriate log strategy) and read about sp_spaceused and above all dbcc updateusage before bitching about the quality of the results.