| Author |
Topic |
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
 Posted - 2008-11-10 : 00:55:30
|
| Dear Expert DBA's,my environment is emergency environment, and they don't want a single minute downtime.my environment is working with a production server (server16) and replication server (server17). we are querying from server17 for reports. at the same time, application also using this server17 for searching purpose. if replication stopped, or query taking long time, we are getting problem in the application, and sometimes, replication also stopping. finally i've decided that another server is required for reports usage.now i'm expecting a good setup which can i use? replication? mirroring? log shipping? server 16 is production and server 17 is replication server 18 (the new) is for reports usage. so please suggest me. i want my environment should be working much faster.ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2008-11-10 : 02:02:05
|
| we are using two servers which are dell power edge san drives and 4Gb ram.server 16 is for productionserver 17 we are using for replication.we are getting applications struck some times because of long running queries in reports. because we are searching option in the application is working on replication server. this means server 17 is using in application as well as using for reports.while running some queries, the search option in the application getting slow. to avoid, we have decided to use another server for reports usage.now, which options can i use to make the data available to the users? mirroring or replication or logshipping?server 16 is production, and i want to make server 17 as high available server and then server 18(new) should be for reports usage.in typical words, i'm expecting an emergency environment (call centre 24X7) should not be affected for any reason.(not disastor)if you are the DBA for this environment, which option you will choose? replication, mirroring, logshipping? we want high availability, and reports also. (not even a single second data we dont want to loose). per minute, around 1000 rows are inserting in the database. so please suggest me the best plan......if still need more info, which type of info you are required let me know.....ArnavEven you learn 1%, Learn it with 100% confidence. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Elisabeth Redei
Starting Member
15 Posts |
Posted - 2008-11-10 : 12:30:13
|
| Hi,For your data redundancy, if you don't want to loose "not even a single second" of data, then Database Mirroring in synchronous mode is your only option. The database is not accessible to clients. You can use Database Mirroring together with Database Snapshots to provide reports but then the clients would not have data that is a 100% up-to-date (because you wouldn't create new Snapshots every second).If you choose Log Shipping, you can potentially loose 1 minute worth of data. You can make that database partially available to clients if you use the Standby Mode. However, as soon as you are applying transaction logs, any clients currently connected needs to be disconnected. So if you schedule the restore job to only restore during the night - your database would be available during the day (but of course they would not have the completely up-to-date data.Assuming your reporting needs require up-date-data 24x7, then, as Tara says, Transactional Replication is probably the way to go. So to summarize, spontaneously I would say; mirror for data redundancy (possibly HA as well) and replicate for your reporting needs.You can read more about it here: "Database Mirroring in SQL Server 2005"http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspxHTH!/Elisabethelisabeth@sqlserverland.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2008-11-11 : 00:19:48
|
| Dear Elisabeth and Tara, thank you very much for the time given, presently i'm working with transactional replication, now the third server came into picture.as you said, if first i mirrored the required databases to the third server, will it affect any negatively on the replication process? (presently push i'm using. i've tried to use pull subscription on my machine, but i'm getting the error that "can not create a new folder on the repldata folder" that means i'm failed to provide the required permissions to the user. so i've kept push subscription. then it accepted.)is it ok to put the main database as principle and the third server as mirrored database now?ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Production environment please please pleasehelp