# CASJobs Manual Installation

Self-Installer didn't work or, worse yet, currently fictional? This is the place to be. This guide will guide you through setting up CASJobs, armed with nothing but a mere zip file and an agile clicking finger.

### Prereqs

CASJobs requires that the following be active before installation. Unless otherwise noted, these do not all have to be installed on the same machine.

- SQL Server 2k3+ Admin account required on at least one machine with this installed, recommended on others.
- Internet Information Services (IIS) + .Net Framework 2.0+. Admin account required on this machine

### Overview

A brief description of each component of this system follows.

- Website. This is this website proper for CASJobs. It runs in IIS with .Net 2.0 or greater.
- BatchAdmin database. This is the back end database used for tracking queries, users, etc.
- JobsService. This is an always-on windows service that first pulls async jobs from the BatchAdmin database and then executes them appropriately. It should be run on a machine with .Net installed.
- MyDB database server[s]. Where user MyDB's are located.
- Data database server[s]. Where the actual data is. In skyserver this would be the DR4 servers, DR3, etc.

These components may be run on any combination of machines, so long as they are on the same local network. For instance, one might have the website on one machine and BatchAdmin and the JobsService on another.

In the interest of brevity, well-documented database commands or procedures will often be glossed over. Books-Online for SQL server (BOL) is a good reference and installs by default to Microsoft SQL Server -> Documentation and Tutorials -> SQL Server Books Online.

### First Steps

You should have a file called CASJobs_install.zip. Upon extraction you should see, at the root level, a file 'cjemptybackup.bak' and another directory. That directory will be the root of the website; you should now copy that elsewhere. A good place for it is under IIS's root directory, generally at 'C:\Inetpub\wwwroot'. 'cjemptybackup.bak' is temporary and can go anywhere.

### Making an IIS app.

You should now create an IIS web application for CASJobs. This can be done by opening up IIS manager (under administrative tools) and then finding the directory called 'CASJobs' you just copied in the previous step. Right-click that directory, choose properties, then find where it says 'Application Name' and then click the 'Create' button on the same row. That's it; click ok and you're done. You can verify that this worked by opening a web browser pointing at the appropriate, local URL. By default, this is http://localhost/casjobs. Before continuing, you should make sure this page is loading correctly. If it's not loading, verify that

- IIS is running. You may want to restart IIS just to make sure. This can be done in IIS manager by right-clicking the machine name, choosing all-tasks, then Restart IIS.
- The website is running. In IIS manager first go to the 'Web Sites' directory, right-click 'Default Web Site', then choose 'Start'. If 'Start' is grayed out, then the website is already started. It doesn't hurt to Stop/Start it just to make sure.

### Customize Website

A few website files are not checked into cvs directly, but are instead checked in with the name of their organization appended to the file name. This is so fresh checkouts do not overwrite existing changes. Currently there two sets of templates, 'sdss' and 'af'. These are essentially themes. 'sdss' is a dark, night sky theme, while 'af' has a more earthy tone. Upon a new installation you should copy all files from one template to files without the extension. Below is a list of all the template files.

- cjobs.css: Style sheet for the site. Colors, fonts, layout, etc.
- top.ascx: The banner at the top of the site. Includes menu items.
- helppic.jpg: The little image on the help page.
- /samples: The two directories under /samples contain site-specific sample queries. To make these available copy them up to /samples.

### SQL Server Authentication

CASJobs requires SQL authentication for all databases used. If anything is set to exclusively use windows authentication, then it will not work. A database can be configured to use mixed-mode authentication by doing the following in Microsoft SQL Server Management Studio (SSMS) in SS2k5, or Enterprise Manager in older SS versions.

    right-click server -> properties -> security -> Server authentication = 'SQL Server and Windows Authentication mode'

Each and every database server used by CASJobs must be set to use this form of authentication.

### BatchAdmin

'BatchAdmin' is the name of the database CASJobs uses to manage many back-end tasks. To set it up, first use SSMS to restore the file cjemptybackup.bak to a database called 'BatchAdmin'. Some weirdness on using SSMS to restore a database from a file...

- You must create an empty database of the same new (BatchAdmin) BEFORE trying to restore a database.
- You also have to enable the option 'Overwrite Existing Database' in the database restore options.

### Editable Tables in BatchAdmin

Tables not mentioned are only used internally, and should not be modifed by a person.

- Filter: Each row is a regex to run on every query run by CASJobs. Queries that match a given criteria can be prohibited from execution.
- MyDBHosts: Contains a list of MyDB Servers. See 'Setting up a MyDB server' for more details.
- Output: Contains a list of paths for output types (ie csv, fits, etc.). See 'Setting up Output' for more details.

### Setting up the 'batch' user

I had trouble getting this to work properly using just the SSMS GUI hooks. However, the follow SQL commands should work just fine. You should connect as an admin user and run these under the 'BatchAdmin' database, not master.

1. This user should be called 'batch'. This is important, since all of the tables in BatchAdmin are owned by batch, and if a different user tries to access them there will be problems.
2. 'batch' should exists as a user in batchadmin. You can check this in SSMS, under db security. If not then run this...

       use [BatchAdmin] EXEC sp_adduser 'batch'

3. 'batch' must have a login for the server. This can be checked in SSMS, under server security. If not, you can give batch a login with...

       EXEC sp_addlogin 'batch', 'password', 'BatchAdmin'

4. You should give 'batch' a unique password.

       use [BatchAdmin] EXEC sp_password NULL, 'abc@BC123', 'batch'

5. Sometimes a user can get orphaned from his login, esp. when coming from a restore. This usually fixes it...

       EXEC sp_change_users_login 'Update_One', 'batch', 'batch';

6. Make sure you can login as batch! You can do this in SSMS by connection to a server with the batch login.

### ConnectionStrings

ConnectionStrings are a way of referring to a particular database on a particular machine. All databases in CASJobs are described with a connectionString at some point, somewhere. When setting up a database, you should keep track of the following information, since you'll probably have to enter it somewhere else later.

- Initial Catalog: This is the name of database.
- Data Source: This is the name of the machine the database is located on.
- User ID and Password: A login for that database. Note that windows authentication will not work with CASJobs; you must use SQL Server.

Below follows a connection string example...

    Initial Catalog=name_of_db; Data Source=name_of_machine; User ID=batch; Password=pw;

It consists of '=' separated pairs, which are in turn separated by ';'s. Spacing is important, 'DataSource' is not the same as 'Data Source'.

### Linked Servers

It is expected that the database servers that make up the 'Data database[s]' and the 'Mydb database[s]' are all linked together. For example, the user that logins into a MyDB database should be able to query each data database from that MyDB connection. You can setup linked servers in SSMS or use the T-SQL command sp_addlinkedservers. You might find it easier to manage users if you setup all data and mydb databases to use the same one.

### Setting Up a MyDB Server

This is where the 'batch' user will be creating MyDBs (user databases). This server uses two SQL Server users, one for initially creating databases, and one for accessing them after that. This is to prevent users from such mischief as increasing the size of their own MyDB, dropping their own MyDB, etc.

- Admin User: This user should be able to create and drop databases on the desired server.
- Access User: This user should have full control on databases on the desired server, excluding create/drop database privs. If your data databases are set to use a common user, this user is usually that one.

Once you've setup the database users, you should update the MyDBHosts table in BatchAdmin. Below follows an explanation of the columns in MyDBHosts.

- ConnectionString: This is the access user from above.
- Limit: The limit of MyDB that may be created on this machine.
- path: Where MyDB files will be placed, upon creation.
- AdminConnectionString: The create db user from above.
- User: Access user (should be same as from onnection string)
- Pass: Same as user, but password.
- Server: Same as user, but server from that string.

### Setting Up Data Databases

When users refer to a catalog in CASJobs, such as 'DR4', they are referring to a virtual catalog, not a specific machine. The 'Servers' table contains all the mappings from context names (dr4) to actual machines. A machine is usually determined by a queue, target pair. However, queue, target duplicates are allowed; in this case, queries sent to that queue, target will alternate, performing rudimentary load balancing. Each 'target' field (called 'context' elsewhere), should have at least two entries; a short or 'quick' version with a queue value of one, and a longer version, with a queue value greater than one. Even if a context has just one machine for both quick and long jobs, there should still be two entries for this context, which the same machine listed in each.

- ConnectionString: How to connect to this database.
- Queue: The maximum execution time in minutes allowed for this server. See above.
- Target: The virtual name for this machine. It's called 'Context' now; target is the old name.
- Owner: The user to connect with, from connection string.
- Host: The machine name, from connection string.
- Cat: The initial catalog, from connection string.
- ExecLimit: Maximum number of concurrent query executions allowed for this server.
- Privs: Add a privilege here to allow only users with that privilege to view/query this server. This field is optional.
- Rank: Affects the order in which servers are returned. Higher numbers come first. Non-critical.

### Setting up the JobsService

JobsService is a service that constantly runs in the background, picking up and executing asynchronous queries and output jobs. To run JobsService, it must first be installed with a program called 'installutil.exe' that comes installed with .Net. By default, this program is not included in the path. It's located wherever .Net installed itself, which, on this particular machine is...

    C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe

The JobsService executable is located under the CASJobs directory here:

    \JobsService\bin\Release\JobsService.exe

To install this service, simply pass JobsService as a parameter, ie

    installutil.exe Jobsservice.exe

If you don't have installutil.exe in your path, you're probably going to have to give an absolute path to one of those. You'll have to tell a name to call the service; remember this name, since this is how you'll find it later. To uninstall the jobsservice, simply do the same thing, and pass the \u parameter. You'll also have to give it the credentials for the user you want to run it as. This can be a pain, since this dialog always pops up in the background (it should pop up after you enter the service name) and makes it seems like the installer is 'stalled' if you don't see it. Also, the user you give it should have the domain in it. So if your domain is 'DOMAIN' you'll have to give it a login like user@domain, etc...

Once installed, the service may be stopped/started from the services management panel, found under Administrative Tools -> Services

You should see the service here, as the name you gave it earlier. To start it, right-click it and choose start. This service must be running for asynchronous queries and output jobs to function.

### Setting up Output

The 'Output' table determines the format in which MyDB table contents may be downloaded and where these resources will be stored. The backup should have all available types of output already in it. You will have to set at least the path and url of each output type to something appropriate for your setup. Below follows the column descriptions...

- OutputType: What's this output called?
- Description: What's this output?
- Path: Where should these output files be created? Should be an absolute path. Network paths are supported. The user that the JobsService runs as must be able to write to this directory. This directory should also be available under IIS.
- URL: This url should map to the 'path' specified in the other column. Users will download an output file with the url here + the file name.
- visible: If this value is 0, then this output type is not available to users. Otherwise it is.

### Web.config/JobsService.exe.config

blah blah blah explain

- CASJobs System Parameters
  - SqlConnection.String: specifies the connection to batchadmin database
  - ServiceID: WebservicesID (wsid) used to perform system tasks.
  - AnonID: wsid used to perform anonymous tasks
  - QueryMemCap: Maximum memory (in megabytes) a synchronous (quick) query is allowed to consume before it is cancelled, regardless of whether the quick time limit has been reached or not. This is to prevent queries with quick, massive IO, such as 'select * from photoobj', from crushing the server
  - SMTP: Mail server used by casjobs to send out auto mails, (like your query is done, etc)
  - MAIL_FROM: The return address on those mails.
  - MAIL_SUBJECT: The base subject on those mails.
- QueryPlan Parameters
  - bg_color: While the pages showing query plans generally get their formatting/color info from css, this doesn't work to well for the generated diagrams. This parameter is so you can match the background color of the plan diagrams to match the rest of your site.
  - fg_color: Same as above, but foreground.
  - supress: Fields listed here will not be displayed.
  - first2show: Fields listed here are displayed in the order shown. Other displayed fields, not listed here, are displayed in a somewhat random listing after these.
- ZTest Parameters (see ZTest)
  - def_testid: Sets the default wsid used by the test page.
  - def_context: Sets the default context used by the test page.

### Dolling it Up

blah blah css

Contact SDSS Help