Role Autodata: Difference between revisions
No edit summary |
|||
| (25 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
== Introduction == | |||
Autodata contract from 15th May 2013 to 30th April 2015 | Autodata contract from 15th May 2013 to 30th April 2015 | ||
== Details == | |||
Web applications: | Web applications: | ||
ThinClient (EU, EA (East Asia), US, AU, NZ) - the main client web application. | 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) | WebServices (Web application with access to the [[Skills_SQL_Server_Development#SQL Server|database]] that outputs [[Skills_Other_Technical#XML|XML]] to its clients) | ||
Malachite API (direct connection to SQL database from API client) | Malachite API (direct connection to SQL database from API client) | ||
| Line 25: | Line 23: | ||
Database servers: SQLPROD2, SQLPROD1, UKADSQL001 | Database servers: SQLPROD2, SQLPROD1, UKADSQL001 | ||
Web servers: WEBPROD2, WEBPROD1 | [[Skills_Other_Technical#IIS|Web]] servers: WEBPROD2, WEBPROD1 | ||
Amazon servers in the US | [[Skills_Other_Technical#Amazon AWS EC2|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. | 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. | ||
| Line 33: | Line 31: | ||
For the upgrade this will have to be re-natted to the IP address of the new database server. | 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 [[Skills_SQL_Server_DBA_Database_Administration#SQL Server upgrades|version upgrades]]. Restoring new database onto live server, configuring [[Skills_SQL_Server_DBA_Database_Administration#Database mirroring and log shipping|mirroring]], editing [[Skills_SQL_Server_DBA_Database_Administration#Synonyms|synonyms]] to point AutoMain to MID databases. | |||
Database | == Tasks == | ||
Re-setting [[Skills_SQL Server DBA Database Administration#Security|Login]] permissions, recovering [[Skills_SQL Server DBA Database Administration#Security|orphaned users]]. Removal of test "Zetor" data, reconfiguring [[Skills_SQL Server DBA Database Administration#Backups and restores|backup]] [[Skills_SQL Server DBA Database Administration#SQL Agent|jobs]] for new databases, removing old databases. | |||
Copying user data over at go-live, [[Skills_Non_Technical#Documentation|documentation]] of required steps | |||
[[Skills_SQL Server DBA Database Administration#SQL Server upgrades|Downgrading]] SQL 2008 database to 2005 by exporting data and schema, and loading into the new database. | |||
CV evaluation, [[Skills_Non_Technical#Other|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 | SQL 2005 to 2008 [[Skills_SQL Server DBA Database Administration#SQL Server upgrades|upgrade]] planning: Running SQL 2008 [[Skills_SQL Server DBA Database Administration#Performance tuning|Best Practices Analyzer]], planning [[Skills_SQL Server DBA Database Administration#Capacity planning|disk space required]], SQL [[Skills_SQL Server DBA Database Administration#Capacity planning|licencing]], plan and run application [[Skills_SQL Server DBA Database Administration#Capacity planning|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. | Cloning of existing live servers (virtual machines), change of name and [[Skills_Other_Technical#Networking|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. | 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. | 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 67: | 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 VB | 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 SSRS | Creating [[Skills_Business_Intelligence#SSRS|SSRS]] reports for user account details, based on stored procedure output, which I wrote. | ||
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 83: | 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.