How To Set Up An Open SkyNode.

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.

Required Downloads

You will require this ZIP file which contains the SQL and creat-db.bat.
Unzip this you will need it bellow.

You also need the latest OpenSkyNode Template.zip this is refered to below as template.zip just put it somewhere safe for now.

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:

1. Creating a new database and a primary table to hold the data you want to make available via a SkyNode
2. Importing your data into this database and table
3. Running the script create_db.bat in order to set up all of the necessary database configuration
4. 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:

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):

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:

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:

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:

<appSettings>
  <add key="IVOA_ID" value="ivo:anshar.phyast.pitt.edu/skynode/"/>
  <add key="def_portal_url" value="http://openskyquery.net/Sky/SkyPortal/PortalJobs.asmx"/>
  <add key="LOG_LOCATION" value="C:\Logs\OpenSkyQuery\"/>
  <add key="cstring" 
       value="Initial Catalog=MosaicGrothStrip; Data Source=localhost; User ID=webaccess; Password=webaccess;"/>
  <add key="node_id" value="grothstrip"/>
  <add key="primary_table" value="photoprimary"/>
  <add key="primary_table_key" value="objid"/>
  <add key="sigma" value="1.0"/>
</appSettings>

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.

7: Registering the SkyNode

If you are felling brave just go to The STSCI/JHU Registry and copy another skynode entry.

There is a good guide to registering servives with the VirtualObservatory on us-vo.org.
How to publish ot the VO

8: Troubleshooting

The following errors will show up when trying out various tests on the default.asmx page included with the web-service.