Role Autodata

Autodata contract from 15th May 2013 to 30th April 2015

In more detail

**************

Web applications:

ThinClient (EU, EA (East Asia), US, AU, NZ) - the main client web application.

WebServices (Web application with access to the database that outputs XML to its clients)

Malachite API (direct connection to SQL database from API client)

Dongle Helpdesk

Non-dongle Helpdesk

CRM

Salesforce

ROCC (Oracle)

Database servers: SQLPROD2, SQLPROD1, UKADSQL001

Web servers: WEBPROD2, WEBPROD1

Amazon servers in the US

Idea of the Amazon webserver users being authenticated by a direct connection to SQLPROD2 (ThinClientLogon). The webserver connects to a single external IP address that maps to the load balancer  and this is then NATted to SQLPROD2.

For the upgrade this will have to be re-natted to the IP address of the new database server.

CV evaluation, interview, and selection of permanent and contract DBA resources.

Web server on Amazon connecting directly to Milton Keynes servers load balancer.

Database version updates. Restoring new database onto live server, configuring mirroring, editing synonyms to point AutoMain to MID databases.

Re-setting Login permissions, recovering orphaned users. Removal of test "Zetor" data, reconfiguring backup jobs for new databases, removing old database,

Copying user data over at go-live, documentation of required steps

Downgrading SQL 2008 database to 2005 by exporting data and schema, and loading into the new database.

SQL 2005 to 2008 upgrade planning: Running SQL 2008 Best Practices Analyzer, planning disk space required, SQL licences, plan and run application tests on test SQL 2008 servers (ThinClient, webservices, API). Performance testing.

Cloning of existing live servers (virtual machines), change of name and IP address, to use as new live servers.

Planned go-live for the upgrade: Back up databases, restore to new servers, permissions, reconfigure router to point Amazon web logins to new database server.

Reconfiguration of live web servers web.config files to point to the new database servers.

Move of live system from Croydon to Milton Keynes. New virtualised machines running on VNC??. Overnight go-live in the summer of 2013.

Salesforce upgrade. Running increasingly complicated queries on the following three systems, joining across their datasets:

Dongle HelpDesk (HelpdeskLive, ThinClientLogon)

CRM (AutoData_CRM I think)

ROCC (a SQL database of the same name extracted from the Oracle ROCC database)

These queries were to generate the data sets to be imported into the Salesforce cloud system.

(project got cancelled due to other reasons).

Development of existing Access databases such as: ModelsView.mdb

to change functionality.

Editing the VB script behind buttons on these databases to change the query functionality. Editing of stored procedures in SQL Server tables behind.

Creating SSRS (SQL Server Reporting Services) reports for user account details, based on stored procedure output, which I wrote.

Loading data into Autodex SQL Server database.

Wrote sophisticated query to report Illustrations data, based on three queries, results pivoted on a column, and unioned together, and all the code to do this being generated as dynamic SQL and run within a stored procedure. With SQL injection attacks prevented.

Reconfigured SunTemp application to point from DeadSvr to \\cifsvfiler. Involved working out how it ran, which was a little complicated.

Wrote a stored procedure sp_check_synonyms to go through all of the AutoMain databases on the server, and all of the synonyms in them, and detect if any were pointing to the wrong corresponding MID database, and flag an error in that event.

Wrote stored procedure using FOR XML EXPLICIT to output an XML result from a table (earthpoint).

ServiceGuide: Worked out how the ServiceGuide application pulled information from the SQL Server database, and wrote a query based on this to update notes text, and also change the assignment of service descriptions to intervals.

MID - intervals - jobs to do (can't remember the name).