Moving database into another server

• miércoles, 18 de abril de 2012 7:23

   

   
  

  0

  Hi everyone

  i have SQL server 2005 installed in 20 different countries used for the ERP system we are using (JDE) . In each Server i have the same below points

  - two databases with the same name in all installations (ps_dev and ps_reports)

  - 3 replications from the database ps_dev to ps_reports

  - in the database ps_dev i have more than 600 stored procedures (same across all installations)

  - these procedures are under frequent changes and i use a custom developed query analyzer to through the same code to all the installations at the same time

  - from each database (ps_reports) there are 4 replications that copies the data to on consolidated warehouse (collect data from all countries)

  Now my problem is that we have reached a point where the databases have grown and the load on the servers has increased, causing a performance problem on the ERP systems. So we are planning to move the reporting database (ps_reports) to another server.

  doing that may cause the below problems :

  - i have to redo the replications in each installation ( the data is around  800 GB)

  - i have to redo the replications from each country to the consolidated site ( over the VPN )

  - generate script for all the stored procedures and replace the "PS_REPORTS.SCHEMA.TABLE" with "ServerName.PS_REPORTS.SCHEMA.TABLE"

  but then i should redo the same 20 different times because in each country/installation i would have a different servername.

  - this will take me several months to do

  Does anyone has a better idea that may help me solve the performance issue and at the same time less effort and time consumming??

  thanks ;

  ---

  Fadel Daher

  • Tipo cambiado Fadel Daher miércoles, 18 de abril de 2012 7:25
  •  
  • Responder
  • Citar