miércoles, 18 de abril de 2012 7:23
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??
- Tipo cambiado Fadel Daher miércoles, 18 de abril de 2012 7:25
Todas las respuestas
martes, 01 de mayo de 2012 18:36
what type of replication are you using ?
if you're using transactional replication it is not always necessary to redo the replication and there for the data size 800GB is probably not a limiting factor.
Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.