This document covers how to install a full SkyNode on a Windows machine
running Microsoft's database and web-server applications, SQL Server and IIS
respectively. This document was born out of my own frustration at the lack
of good documentation for setting up SkyNodes. I hope that you find it
helpful.

This document assumes that you are at least reasonably familiar with SQL
Server, especially Enterprise Manager and Query Analyzer.

Installation Overview:

Included with this document should be a batch file named
create_db.bat, a folder named sql containing a number
of SQL scripts, a folder named dll containing a single DLL, and a
folder named test containing some test SQL scripts. In its
simplest form, the installation should involve 4 steps:

Creating a new database and a primary table to hold the data you want
to make available via a SkyNode

Importing your data into this database and table

Running the script create_db.bat in order to set up all of
the necessary database configuration

Unzipping the supplied web-service libraries and applications to a
directory and making this directory available to the web-server IIS

Almost all of the work in setting up a SkyNode is accomplished by the batch
file in step 3. The other steps will be discussed in the next few
sections.

1: Creating a Database and Table

Open the script createDB.sql in the sql directory
in Query Analyzer. This script is set by default to create a database named
SkynodeData. Edit the two lines at the top of the file that look
like this:

You can name the database anything that you want, so be sure to give it a
descriptive name. You must change both occurrences of
SkyNodeData, though.

Now you need to customize the schema for the primary table in this new
database. By default the name of the primary table is PhotoObjAll.
The install scripts assume this, so you must change all occurrences of
PhotoObjAll in the install scripts if you wish to use another name.
Scroll down to the bottom of the script and add in any additional rows that you
need for your database where the comments indicate. For example, if you have
redshift data you could add a line like this:

redshift real not null

Note that you need to put commas after every line except the last one. The
data types you will most likely need are int (32 bit),
bigint (64 bit), real (32 bit, single precision), and
float (64 bit, double precision).

Every SkyNode must contain rows for right ascension, declination, and a
unique object id (ra, dec, and objid respectively) as well as variables
related to the hierarchical triangular mesh (HTM) functions that are used in
querying the database (cx, cy, cz, and htmid). The
HTM related variables will be filled in by the install script; you don't need
to supply any data for them.

Before running the script, use the check syntax button (the blue check mark)
to make sure you haven't made any typos. The only problem is that Query
Analyzer will always complain that the database you want to create doesn't
exists. In order to check the syntax of the rest of the script, you need to
temporarily comment out the database creation lines, then check the syntax,
then uncomment the database creation lines. Then run the script (the green play
button). For most SQL scripts, it's very important that you run the script on
the right database. In this case, it doesn't matter because the script
will automatically use whatever database it creates.

If you make a mistake, you can rerun the script if you comment out the lines
that create the database at the top ("--" without the quotes will
comment out a line). The script checks whether the table PhotoObjAll exists in
the database and drops (deletes) it if it does. Note, however, that if you
rerun the script you will have to run it under the right database name
(SkynodeData by default). Make sure that the proper database name is selected
in the drop down box in Query Analyzer. If you mess up and accidentally add
PhotoObjAll to another database (most likely master),
you can delete it with the command drop table PhotoObjAll, which
of course, you must run from the proper database. Enterprise Manager can also
delete tables if you prefer.

Once you have customized and run this script, you should have a new database
and table. Open Enterprise Manager, navigate to the database you created, and
check under Tables that there is a table named PhotoObjAll.

Finally, it would probably be a good idea to save this modified SQL script
under a different name so that you have it for future reference. If something
goes wrong, you won't have to reedit anything.

2: Importing Your Data

If you're like most astrophysicists, the data you want to import is
contained in a plain text file with a column for each item (or at least in a
format that can be easily converted to this format). Luckily for you, SQL
Server has a simple wizard that can import your data from such a file. There
are a couple of things that will probably make things a little simpler for you
when importing your data.

First, it's probably a good idea if you write out only the data that you
want to import into the SkyNode. You can tell the import wizard to ignore
certain columns, but it's definitely much easier just to leave out any data
that you don't need. Simple Unix tools like awk can extract out
what columns you need simply and quickly. Second, it will make things much
easier if you output the columns in the order they appeared in the schema you
created above. For example, in the default schema, the rows are created in the
order ra, dec, objid. You can make importing the data much easier
by ordering the columns in your text file as ra, dec, objid.
Third, the import wizard claims to be able to understand header lines and fixed
width columns, but in my experience these two things cause a lot of problems.
Stick to using columns separated by a delimiter such as a single space or
comma without any data headers. If you follow these three guidelines,
importing your data should be extremely easy.

Note that I wrote this guide using Windows Server 2000 and SQL Server
2000, so the descriptions here might vary slightly from other versions.

OK, so now it's time to actually import your data. Open Enterprise Manager,
navigate to your database, then right click on it and select
Import Data... from the All Tasks menu.

This will bring up the import wizard. Click Next on the intial
window, then a second window named Choose a Data Source should
appear. Click the Data Source drop down selector, then select
Text File at the very bottom. A File Name dialog box
appears. Click the button next to it and select the file of data you want
to import. Note that Windows only displays files ending in .txt
and .csv by default. Click Next to go to the next
screen.

The next window should be Select file format. If you followed
the instructions at the beginning of this section, then you should select a
Delimited file. If you created your file on Unix rather than
Windows, then you will need to change the Row delimiter to
{LR} instead of {CR}{LF}. You can also just open
your file in Wordpad and resave it, which will convert it to the standard
Windows line endings.

The next window is titled Specify Column Delimiter. Select the
delimiter you used when creating your data. If you used a space, you need to
enter a space in the box marked Other. Your data should appear
in the preview window below once you have entered the delimiter.

The next window is Choose a destination. Here you want to make
sure that your database is selected from the drop down box marked
Database. It should be by default.

The next window is Select Source Tables and Views. This screen
is very important because here you must specify where to put the data to import
in your database. Click on the text in the Destination column.
This should bring up (previously hidden) drop down box of tables. Select
PhotoObjAll from the list. It will look something like
[SkyNodeData].[dbo].[PhotoObjAll]. Now click the button with
three dots in the Transform column. This will bring up a window
titled Column Mappings and Transformations. Here you must map
the source columns of the input file to the rows created in your primary table.
Because you won't have data for cx, cy, cz and htmid,
you will need to set them to ignore. If you followed the advice at the
beginning of this section, then the mapping should be correct by default.
Once you have everything set properly, click OK. Before clicking
Next, you can click Preview to do a final check that
everything is set properly before you finally import everything.

As a side-note, it is very important that when you created your schema that
the HTM variables be set to Nullable. Otherwise the importer will
complain when you actually run it. Unless you changed the default schema, this
shouldn't be a problem.

Finally, click Next twice and the importer should run. If
something goes wrong, the importer should give you an error message. If the
data is imported successfully, you can test whether the data was imported
properly by running a test SQL command. Open up Query Analyzer and run the
following command on your database (make sure not to run it on
master):

select top 10 * from PhotoObjAll

This should print out the first 10 entries from the file that you imported.
Make sure that the data is associated with the proper rows. If not, you can
delete the database from Enterprise Manager, rerun the script
skynode-createDB.sql and import your data again.

3: Running the Script

Before you run the install script, you need to modify one of the SQL scripts
if you modified the default schema in step 1 (i.e. if you're using more than
just ra, dec, and objid in your database). Open
skynode-fillMetadataTables.sql in the sql directory
in an editor and scroll down to the section that looks like this:

---------------------------------------------------------------------------
--
-- Add in descriptions of all of the columns that you added to the main
-- table PhotoObjAll here
--
---------------------------------------------------------------------------

You need to add descriptions of any of the additional data that you want to
store in the database here. For example, if you included photometric redshift
data with a column named photo_z, then you would add a line that
looks like this:

Note that in SQL, strings are enclosed in single quotes, not double quotes.
The 3 blank entries (unit, ucd, enum) are optional. You will need
to add one insert command for every additional row that you added to the schema
in step 1. This is absolutely crucial for the web-service to be able to read
the data in the database because the web-service code reads the table
DBColumns to determine what data is contained in the database.
Leaving out a row that you added to the schema will make this row invisible to
the web-service. Once you have finished modifying the script, check its syntax
in Query Analyzer to make sure that there are no typos, but don't run the
script (it's run automatically by the install script).

Once the metadata tables SQL script is modified, the rest of the process
should (theoretically) be trivial. Open a command prompt, go to the directory
containing the file create_db.bat, and run it. The batch file
takes two arguments. The first is the name of the machine the database is
stored on and the second is the name of the database (SkynodeData by default).
To run the script on a database on the local machine (probably the most common
situation), input (local) for the machine name.
Example usage:

create_db.bat (local) SkynodeData

As the script runs, it should print out a short message about what it's
doing at every step. It also outputs a log to create_db.log that
you can review for errors.

Hopefully you won't encounter any problems running the batch file, but if
you do, then it's helpful to know exactly what the batch file is doing. The
batch file simply runs a series of SQL scripts contained in the sql
directory. Below is a description of the SQL scripts in the order that they
are run by the batch file and what they actually do. Because these are SQL
scripts, you can always open them up in a text editor or Query Analyzer and
take a look at what they're actually doing. Even better, most of the scripts
are designed so that they can be rerun multiple times without causing problems,
so if something fails you can try running a given script in Query Analyzer and
look at the error messages.

skynode-HTMmaster.sql

Installs the main HTM procedures and library and grants public access to
them. Copies the file htm_v2.dll to
C:\Program Files\Microsoft SQL Server\MSSQL\Binn\, which is the
default install location of SQL Server. If you have installed SQL Server in
another directory, then you will need to copy over the DLL manually to the
Binn directory in the install directory of SQL Server. The
following extended procedures will be added to that master database (not the
SkyNode database):

xp_HTM2_Lookup

xp_HTM2_Cover

xp_HTM2_Cover

xp_HTM2_toNormalForm

xp_HTM2_toPoint

xp_HTM2_toVertices

xp_HTM2_Version

skynode-htmInstall.sql

Installs more HTM related functions into the SkyNode database. The
following functions and stored procedures will be created:

fHtmLookup

fHtmLookupError

fHtmCover

fHtmCoverError

fHtmToString

fHtmToNormalForm

fHtmToNormalFormError

fHtmToVertices

fHtmToPoint

fHtmVersion

fHtmLookupXyz

fHtmLookupEq

skynode-nearFunctions.sql

Creates the following functions / stored procedures in the SkyNode
database:

fGetNearbyObjEq

spNearestObjEq

fGetNearestObjEq

fGetNearestObjIdEq

fGetNearbyObjXYZ

fGetNearestObjXYZ

fGetObjFromRect

fDistanceArcMinEq

fDistanceArcMinXYZ

skynode-calcHTM.sql

Calculates the HTM related variables in the primary table
PhotoObjAll (cx, cy, cz, htmid) using the HTM
functions which were installed above.

skynode-webSupport.sql

Installs functions / stored procedures related to interacting with the web
service in the SkyNode database. The following will be installed:

replacei

spExecuteSQL

fIsNumbers

spSkyServerColumns

spSkyServerConstraints

spSkyServerDatabases

spSkyServerFormattedQuery

spSkyServerFreeFormQuery

spSkyServerFunctionParams

spSkyServerFunctions

spSkyServerIndices

spSkyServerTables

fDocColumns

fDocFunctionParams

fEnum

spDocEnum

spDocKeySearch

skynode-getMatch.sql

Installs the procedure for performing cross matches. Again, the procedure
in installed in the SkyNode database.

spGetMatch

skynode-createViews.sql

Creates all of the necessary views. In order for the SkyNode to function,
there are several views of the primary table and HTM variables that must be
created, which are:

PhotoPrimary

PhotoObj

Htm

skynode-fillMetadataTables.sql

Creates and populates the metadata tables DBObjects, DBColumns,
and DBViewCols. These tables contain strings describing all of
the tables, views, functions, and procedures contained in the database.
Inaddition to being used by the web-service when querying the database, these
tables are used by the script skynode-initUser.sql below in order
to set the permissions for the public user.

skynode-initUser.sql

Creates a public user through which the web-service will access SQL Server.
By default this user is named webaccess with the same password,
although you can modify it in the script to anything you like. Also, the
web-service uses SQL authentication to login, so this needs to be enabled for
the login to function. See the Odds and Ends section
below.

4: Installing the web-service

Unzip the file template.zip to the directory where
you want to store the actual webservice. With IIS you can either use virtual
directories or the central IIS directory, which is
C:\Inetpub\wwwroot\. This guide does not cover how to set up a
virtual directory. Unzipping the file should create a directory called
SkyNode containing several files and a bin
subdirectory. The actual webservice is called nodeb.asmx. The
other webpage in the directory (default.asmx) is a simple test
page that you will use to check that everything is set up properly.

Once you have unpacked the zip file, you need to tell IIS that this
directory contains a webservice (or application as IIS refers to
it). Right click on My Computer and choose Manage
from the list. This will bring up a window titled
Computer Management. Choose
Services and Applications, then
Internet Information Services, and finally
Default Web Site.

Here you will see a list of all the files and folders contained in
C:\Inetpub\wwwroot\. If you used a virtual directory, you will
have to modify it instead of the Default Web Site. You should
see a folder entitled SkyNode (or whatever name you may have
renamed your web-service to). Right click on the name of your web-service
directory and choose Properties from the menu. About halfway
down the page, there is a section titled Application Settings.
Click the Create button next to the greyed out
Application Name box. Leave all of the other settings the
same. Click OK and you're done with this this part.

Next we need to edit the configuration file for the web-service so that it
knows which database in SQL Server to read. Go to your SkyNode directory
and open the file web.config using any editor. Look for the
section at the bottom entitled <appSettings>. It should
look something like:

You need to modify the following keys in this section: IVOA_ID,
LOG_LOCATION, cstring, node_id, and sigma. If for some
reason you changed any of the other names, you will have to modify other keys
as well. Below is a description of each key:

IVOA_ID

A unique identification string, usually taken to be
ivo:service-url. Be sure to choose a good descriptor that will
be unique.

LOG_LOCATION

The local folder where logs for the webservice will be created. Note
that this folder must have permissions set so that webaccess
can write to it.

cstring

The value of this key contains several important things.
Initial Catalog should be set to the name of the database
that you created (SkynodeData by default).
Data Source should be set to the name of the machine that
the database is stored on, which is localhost if it is on
the same machine as the webserver. Finally, User ID and
Password should be set to the user name and password for
SQL Server.

node_id

This is the shortname for the web-service that will be used by the
VO registry. A good shortname would be something like SDSS or GALEX. It
is very important that the shortname match the name you give when
registering the web-service (more on this later).

sigma

Average positional error of objects in the database in arcseconds. In
order to get a successful cross-match, this value must be somewhere
around 10 - 20 arcseconds, even if the error is much smaller.

Note that the one key that you should not change is
def_portal_url. This must be set to the primary SkyPortal so
that it can coordinate how to query each SkyNode. When you run a query
on http://openskyquery.net, the
main portal contacts each individual SkyNode, which must know where to send
its response. This is what this key tells your SkyNode.

Once you have edited web.config properly, the web-service
is almost ready for testing. There are still some small details to check
before actually testing the web-service. The next section deals with the
remaining things to sort out.

5: Odds and Ends

Well, you're almost done but there are still a few things that you might need
to fix up before your SkyNode will work.

First, the web-service uses SQL authentication (not Windows authentication)
in order to authenticate the public user. In order for the public user to be
login, you need to be sure to set SQL Server up so that this is possible.

Open up Enterprise Manager and navigate to the local database, then right
click on the name of the machine with your database and select
Properties from the list. Click on the Security tab
and make sure that Authentication is set to SQL Server and
Windows.

6: Testing the SkyNode

The SkyNode includes two simple web pages that can be used to test the
installation of the SkyNode. These two pages are named default.asmx
and nodeb.asmx. You can access these pages by going to the
following URLs:

The first page is much more useful for testing. There are 4 buttons at the
bottom of the page, Tables, Columns,
Sql2ADQL, and Run SQL. The first two test the
metadata tables in your database. If they are set up properly, they should
return lists of all of the tables and columns in your database respectively.
The next button, Sql2ADQL, should always work. The last button,
Run SQL, should perform the query listed in the box (which can't
actually be changed) and return the first 10 entries in the database in XML
format. If all of these buttons function, then your SkyNode is working
properly.