Role Autodata: Difference between revisions
| (6 intermediate revisions by the same user not shown) | |||
| Line 52: | Line 52: | ||
Reconfiguration of live web servers [[Skills_Other_Technical#IIS|web.config]] files to point to the new database servers. | Reconfiguration of live web servers [[Skills_Other_Technical#IIS|web.config]] files to point to the new database servers. | ||
Move of live system from Croydon to Milton Keynes. New virtualised machines | Move of live system from Croydon to Milton Keynes. New virtualised machines accessed with [[Skills_Other_Technical#Networking|RDP]]. Overnight go-live in the summer of 2013. | ||
Salesforce upgrade. Running increasingly complicated queries on the following three systems, joining across their datasets: | Salesforce upgrade. Running increasingly complicated [[Skills_SQL_Server_TSQL#|queries]] on the following three systems, joining across their datasets: | ||
Dongle HelpDesk (HelpdeskLive, ThinClientLogon) | Dongle HelpDesk (HelpdeskLive, ThinClientLogon) | ||
| Line 66: | Line 66: | ||
(project got cancelled due to other reasons). | (project got cancelled due to other reasons). | ||
Development of existing Access databases such as: ModelsView.mdb | Development of existing [[Skills_Other_Technical#Microsoft Access|Access]] databases such as: ModelsView.mdb to change functionality. | ||
to change functionality. | Editing the [[Skills_Other_Technical#Microsoft Access|VB scripting]] behind buttons on these databases to change the query functionality. Editing of [[Skills_SQL_Server_TSQL#Stored Procedures|stored procedures]] in SQL Server tables behind. | ||
Creating [[Skills_Business_Intelligence#SSRS|SSRS]] reports for user account details, based on stored procedure output, which I wrote. | |||
Creating SSRS | |||
Loading data into Autodex SQL Server database. | 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. | Wrote sophisticated query to report Illustrations data, based on three queries, results [[Skills_SQL_Server_TSQL#Pivot|pivoted]] on a column, and [[Skills_SQL_Server_TSQL#Pivot|unioned]] together, and all the code to do this being generated as [[Skills_SQL_Server_TSQL#Dynamic SQL|dynamic SQL]] and run within a stored procedure. With [[Skills_SQL_Server_TSQL#Dynamic SQL|SQL injection]] attacks prevented. | ||
Reconfigured SunTemp application to point from DeadSvr to \\cifsvfiler. Involved working out how it ran, which was a little complicated. | Reconfigured SunTemp application to point from DeadSvr to \\cifsvfiler. Involved working out how it ran, which was a little complicated. | ||
| Line 82: | Line 80: | ||
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 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). | Wrote stored procedure using [[Skills_Other_Technical#XML|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. | 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 | MID - intervals. | ||
Latest revision as of 08:27, 3 October 2024
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.