Role Autodata
Introduction
Autodata contract from 15th May 2013 to 30th April 2015
Details
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.
Web server on Amazon connecting directly to Milton Keynes servers load balancer.
Database version upgrades. Restoring new database onto live server, configuring mirroring, editing synonyms to point AutoMain to MID databases.
Tasks
Re-setting Login permissions, recovering orphaned users. Removal of test "Zetor" data, reconfiguring backup jobs for new databases, removing old databases.
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.
CV evaluation, interview, and selection of permanent and contract DBA resources.
SQL 2005 to 2008 upgrade planning: Running SQL 2008 Best Practices Analyzer, planning disk space required, SQL licencing, 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 accessed with RDP. 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 scripting behind buttons on these databases to change the query functionality. Editing of stored procedures in SQL Server tables behind.
Creating SSRS 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.