
----------------------------------------------------------
-- Before running the scripts below
--
-- Be sure that you have .NET 2.0 installed, and IIS is using that
-- and it can run ASP scripts
-- (see the installnotes for more details)
--
-- Edit Web.config
-- Edit cjobs.css , if needed
--
-- Start the CasJobs webpage, add as a new webpage or virtual directory
-- Use IIS/CasJobs/Properties/Application/Add to enable the page
-- the welcome page should appear in a browser, do not click yet
--
-- Edit JobService\bin\Release\JobService.exe.config
-- Install JobService.exe as an admin user with
-- C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe  JobsService.exeInstallService.exe JobService.exe
-- (or similar)
-- Start JobService with the Computer Management/Services tool
--
-- Be sure that your SQL Server is in mixed (windows + SQL ) authentication mode
-- (see the installnotes for more details)
----------------------------------------------------------


----------------------------------------------------------
-- Batch admin server setup
-- In these examples BatchAdmin, Mydb and the databases are on 
-- the same server (AMD1), but they can be on separate ones
----------------------------------------------------------

-- Create the empty BatchAdmin DB
-- You can create it with the wizard tools too, and modify 
-- file location, etc, to match your local requirements
use [master]
create database BatchAdmin
GO

-- detach BatchAdmin
use [master]
EXEC sp_detach_db 'BatchAdmin', 'true';
GO

-- Now manually delete the .mdf and .ldf files, it's a hack, 
-- since REPLACE did not work for me

-- Restore the provided backup copy of the BatchAdmin DB
use [master]
RESTORE FILELISTONLY 
   FROM DISK = 'G:\Users\csabai\2006\casjobs\BatchAdmin.bak'
RESTORE DATABASE BatchAdmin
   FROM DISK = 'G:\Users\csabai\2006\casjobs\BatchAdmin.bak'
   WITH MOVE 'BatchAdmin_Data' TO 'g:\SQLDBs\Data\BatchAdmin.mdf',
   MOVE 'BatchAdmin_Log' TO 'g:\SQLDBs\Data\BatchAdmin_log.mdf'
GO
-- You can study this database, it contains examples. 
-- They can be modified to match your local system. 

-- Add the batch user, with owner privileges
use [BatchAdmin] 
EXEC sp_addlogin 'batch', 'Cas2Jobs', 'BatchAdmin'
EXEC sp_adduser 'batch'
EXEC sp_password NULL, 'Cas2Jobs', 'batch'
EXEC sp_change_users_login 'Update_One', 'batch', 'batch'
EXEC sp_addrolemember 'db_owner','batch'
GO

-- Update the MyDBHosts table, according to your system
-- The MYDB server should have two users, mydbAdmin with owner and 
-- mydbAcces with read/write permissions (see the MyDB setup section)
use [BatchAdmin]
INSERT INTO [MyDBHosts]
           ([MyDBHost]
           ,[ConnectionString]
           ,[Limit]
           ,[path]
           ,[AdminConnectionString]
           ,[User]
           ,[Pass]
           ,[Server])
     VALUES
           ('AMD1'
           ,'Initial Catalog=master; Data Source=AMD1;User ID=mydbAccess; Password=Get4Db;pooling=false;'
           ,10
           ,'G:\SQLDBs\Data\mydb\'
           ,'Initial Catalog=master; Data Source=AMD1;User ID=mydbAdmin; Password=CrYhA1M;'
           ,'mydbAccess'
           ,'Get4Db'
           ,'AMD1')
GO

-- The Users table will contain the CasJobs users
-- We need two special users, admin and anonymous 
-- (note they are not real windows or SQL users, and not the 
--  same as the admin and acces users of the MYDB server)
-- They are created in the sample, you should modify them.
-- The only tricky part is the encrypted passwd, if you cannot get 
-- it in some other way, create fake users with the needed passwd with the
-- Casjobs web interface, copy the passwords and then delete the fake users

use [BatchAdmin]
UPDATE [batch].[Users]
   SET Email = 'csabai@elte.hu'       -- fake email will do here
      ,MyDbHost = 'AMD1'              -- the name of the MYDB DB server 
      ,pwencrypted = 'UIcjERZLd36o8msiarpDdg=='
WHERE Userid='anonymous'

use [BatchAdmin]
UPDATE [batch].[Users]
   SET Email = 'csabai@complex.elte.hu'       -- different (and real) email than above
      ,MyDbHost = 'AMD1'              -- the name of the MYDB DB server 
      ,pwencrypted = 'zdA82cneFWfQGH3SgYAtqA=='
WHERE Userid='admin'
GO

-- Now we can delete the fake example from MyDbHosts
use [BatchAdmin]
delete from MyDbHosts where MyDbHost = 'Skyserver'
GO 

-- We will need to fake mydb servers, they are already in the sample
-- due to a bug one of the owner shuould be the same name as 
-- the mydbAccess user
use [BatchAdmin]
insert batch.servers ([Name],ConnectionString,Queue,Target,Owner,Host,Cat,ExecLimit,[privs],rank)
values ('Mydb Fake','no need to connect',500,'MYDB','mydbAccess','fakeHost','fakeCat',2,NULL,1)
insert batch.servers ([Name],ConnectionString,Queue,Target,Owner,Host,Cat,ExecLimit,[privs],rank)
values ('Mydb Fake2','no need to connect',1,'MYDB','fake','fakeHost','fakeCat',2,NULL,1)
GO

-- and we need real databases, the two version is for the quick and long queues
-- Adjust to your needs. We use the same batch user, to access, but remember
-- these can be a different servers with different users
use [BatchAdmin]
insert batch.servers ([Name],ConnectionString,Queue,Target,Owner,Host,Cat,ExecLimit,[privs],rank)
values ('Northwind 500','Initial Catalog=Northwind; Data Source=AMD1;User ID=batch; Password=Cas2Jobs;pooling=false;',500,'Northwind','batch','AMD1','Northwind',2,'Query',1)
insert batch.servers ([Name],ConnectionString,Queue,Target,Owner,Host,Cat,ExecLimit,[privs],rank)
values ('Northwind 1','Initial Catalog=Northwind; Data Source=AMD1;User ID=batch; Password=Cas2Jobs;pooling=false;',1,'Northwind','batch','AMD1','Northwind',0,'Query',1)
GO



----------------------------------------------------------
-- Mydb server tasks
-- In these examples BatchAdmin, Mydb and the databases are on 
-- the same server (AMD1), but they can be on separate ones
----------------------------------------------------------

-- The MYDB server should have two users, mydbAdmin with owner and 
-- mydbAcces with read/write permissions (it should match the MyDbHosts table
-- in BatchAdmin) But they are not the anonymous and admin users in the Users table

use [master] 
EXEC sp_addlogin 'mydbAccess', 'Get4Db', 'master'
EXEC sp_adduser 'mydbAccess'
EXEC sp_password NULL, 'Get4Db', 'mydbAccess'
EXEC sp_change_users_login 'Update_One', 'mydbAccess', 'mydbAccess'

EXEC sp_addlogin 'mydbAdmin', 'CrYhA1M', 'master'
EXEC sp_adduser 'mydbAdmin'
EXEC sp_password NULL, 'CrYhA1M', 'mydbAdmin'
EXEC sp_change_users_login 'Update_One', 'mydbAdmin', 'mydbAdmin'
EXEC sp_addrolemember 'db_owner','mydbAdmin'
grant create database to mydbAdmin
grant create role to mydbAdmin
GO

------- CasJobs should work now --------------------------
----------------- THE END --------------------------------

-----------------------------------------------
-- A handy script to clean up your batchadmin DB after trials
-----------------------------------------------
delete from batch.jobs
delete from tables
delete from batch.users where name = 't1'
delete from batch.users where name = 't2'
delete from batch.users where name = 't3'
delete from batch.users where name = 't4'