[firebird-support] Re: nbackup strategy advice
Hello Paul. Thanks for your advice. My strategy was based on backup data once approach but this would produce to many files I now realize. But I still want to avoid to backup the entire database (N=0) on regular basis. Whats your opinion about this approach? First backup N=0 Every day N=1 for a month (replace file each time) Increment N next month. This would produce 12 files every year. Thanks, Hugo
Re: [firebird-support] Re: nbackup strategy advice
Den 2014-03-16 07:27 skrev hugo.lar...@yahoo.com såhär: Hello Paul. Thanks for your advice. My strategy was based on backup data once approach but this would produce to many files I now realize. But I still want to avoid to backup the entire database (N=0) on regular basis. Whats your opinion about this approach? First backup N=0 Every day N=1 for a month (replace file each time) Increment N next month. This would produce 12 files every year. That wouldn't work. I don't think you understand how Nbackup works. N=0 backs up everything. N=1 backs up every page that's changed since last N=0. N=2 backs up every page that's changed since last N=1. ... N=k backs up every page that's changed since last N=k-1. So, with your suggested scheme, first month would go like this: Day 1: backup entire database (N=0). Day 2: backup pages changed since day 1 (N=1). Day 3: backup pages changed since day 1 (N=1). Will include all pages copied day 2 + additional pages changed since day 2. Day 4: backup pages changed since day 1 (N=1). Will include all pages copied day 3 + additional pages changed since day 3. ... Day 28/30/31: backup pages changed since day 1 (N=1). Will include all pages copied the day before + additional pages changed the last day. If Month 2 you increment N to 2, you will get this: Day 1: backup pages changed since last day of month 1 (N=2). Day 2: backup pages changed since last day of month 1 (N=2). ... Day 28/30/31: backup pages changed since last day of month 1 (N=2). At the end of the year, your actual final backup sequence will be: N=0: Initial backup first day of the year. N=1: Last backup of month 1. N=2: Last backup of month 2. ... N=12: Last backup of month 12. It would probably make more sense to do it like this: First day of year: N=0, initial complete backup. First day of each month: N=1, will contain all pages changed since first day of year. First day of each week: N=2, will contain all pages changed since first day of month. Each day: N=3, will contain all pages changed since first day of week. If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. This way, you will have a daily backup that's complete, consisting of four parts (N=0, 1, 2 and 3). In general, Nbackup should be run with each value of N at a regular interval, with tighter intervals for higher values of N. Incrementing N over time as you suggested is not suitable. Note that Nbackup has no way of detecting any database corruptions, so if that happens it will go completely undetected. Might be a good idea to combine it with some client local gbak or gfix- v or gfix -v -full, as often as is viable. I might also mention the possibility to simply lock the database with Nbackup and copy the database file with rsync. This will probably have similar or better performance over a slow connection. That's what I do for out 200 Gbyte database, although that's on a single server with a backup volume attached via high bandwidth network. :-) Regards, Kjell
Re: [firebird-support] nbackup strategy advice
Hello, Hello, We have a POS applications with hundreds of clients and need some advice on how to backup. Each application has it's own Firebird database. Read about nbackup and thought that this could be a solution since the clients has low bandwidth. I'm not entirely sure if I understand your scenario correctly. Is the database running at your customer site or are you acting as a service provider and the customers are connecting via e.g. terminal services or similar? How is bandwidth related to backup here? If possible, I would always be in favor for gbak over nbackup due to its logical data processing approach, thus detecting data corruption etc. How large are your databases? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Deadlock
The following procedure causes a deadlock when the user on the web page navigates rapidly through a javascript object on the web page. Please give me some advice on how best to deal with this. I've thought about changing it using DATEDIFF so they update only once a minute, but I would appreciate advice on if there is a better way to fix this CREATE PROCEDURE uspValidateSession ( guid varchar(40) , newsessiontimeout int ) RETURNS ( subscriberid int , testerid int , subscriberloginid int ) AS DECLARE VARIABLE sessionlogid int; DECLARE VARIABLE lastaccessed timestamp; DECLARE VARIABLE currenttimeout int; BEGIN SELECT b.SessionLogId, b.LastAccessed, b.CurrentSessionTimeout, COALESCE(a.SUBSCRIBERID, 0), COALESCE(a.TESTERID, 0), COALESCE(a.SUBSCRIBERLOGINID, 0) FROM tblLoginEvent a JOIN tblSessionLog b ON a.LOGINEVENTID = b.LOGINEVENTID WHERE a.EVENTGUID = :guid INTO :sessionlogid, :lastaccessed, :currenttimeout, :subscriberid, :testerid, :subscriberloginid; IF (DATEADD(:currenttimeout MINUTE TO :lastaccessed) = current_timestamp) THEN BEGIN UPDATE tblSessionLog SET LastAccessed = current_timestamp , CurrentSessionTimeOut = :newsessiontimeout WHERE SessionLogId = :sessionlogid; END ELSE BEGIN subscriberid = 0; testerid = 0; subscriberloginid = 0; END SUSPEND; END^ SET TERM ; ^ COMMIT; Deadlock might be misleading here. Possibly two or more transactions are trying to update the same record in parallel. * What is the semantic of SessionLogId? Is this something highly unique across your web requests? * Are you committing the transaction calling the stored procedure? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
[firebird-support] Re: nbackup strategy advice
Hello Kjell, Thanks for your time analyzing and explaining my backup strategy and proposing yours. You wrote that my strategy wont work. Maybe i'm missing something or I explained wrong. As I understood your strategy is based on annually full backups (N=0) and maybe you understood that my approach is the same. My intention is that after new year N is again incremented to 13 and NOT starting over again from 0. So after two years N=24 with 24 files. First file is the full backup and the other files contains one month of data. Note that I replace the daily N file with the new one. This way (with understanding of how nbackup works), I avoid to ever having to do a full backup more than once. Whats your thoughts on this? BR, Hugo
[firebird-support] Re: nbackup strategy advice
Hello Thomas, Each shop, for example a restaurant has its own local database on the computer the Point Of Sale software is running on. The shops has ADSL connection with 1MBit upload speed and the database is around 50 MB and growing every day. Uploading such file to our server with this bandwidth disturbs other Internet activities and at night they turn of the system. So im trying to figure out an optimal backup solution. Thanks,
Re: [firebird-support] Re: nbackup strategy advice
Each shop, for example a restaurant has its own local database on the computer the Point Of Sale software is running on. The shops has ADSL connection with 1MBit upload speed and the database is around 50 MB and growing every day. Uploading such file to our server with this bandwidth disturbs other Internet activities and at night they turn of the system. * Who is the initiator of the backup? You, remotely? Or a scheduled job at the customer? * I guess there is a business use case why you want to transfer it onto your server once per day? Btw, you can use gbak in a way so that the backup process completely runs on the target server, even if gbak was invoked remotely. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: nbackup strategy advice
Kjell Rilbe wrote: First day of year: N=0, initial complete backup. First day of each month: N=1, will contain all pages changed since first day of year. First day of each week: N=2, will contain all pages changed since first day of month. Each day: N=3, will contain all pages changed since first day of week. If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. That's not necessary, and the higher level backup will add nothing on that moment. Suppose you make a level-2 backup every Sunday. Then it makes sense to schedule the level-3 backups daily from Mon-Sat. If you want to make a point-in-time restore later, it will involve 4 files if that point in time is on Mon-Sat, and 3 files if it is on a Sunday. (And 2 files if it is the first day of the month.) All you have to do is determine the most recent backup before the chosen point in time. If that is a level N, you need N+1 files for the restore (levels 0-N, each one being the most recent file of that level before time 'T'). Cheers, Paul Vinkenoog
Re: [firebird-support] Re: nbackup strategy advice
Den 2014-03-16 11:58 skrev Paul Vinkenoog såhär: Kjell Rilbe wrote: First day of year: N=0, initial complete backup. First day of each month: N=1, will contain all pages changed since first day of year. First day of each week: N=2, will contain all pages changed since first day of month. Each day: N=3, will contain all pages changed since first day of week. If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. That's not necessary, and the higher level backup will add nothing on that moment. Suppose you make a level-2 backup every Sunday. Then it makes sense to schedule the level-3 backups daily from Mon-Sat. If you want to make a point-in-time restore later, it will involve 4 files if that point in time is on Mon-Sat, and 3 files if it is on a Sunday. (And 2 files if it is the first day of the month.) All you have to do is determine the most recent backup before the chosen point in time. If that is a level N, you need N+1 files for the restore (levels 0-N, each one being the most recent file of that level before time 'T'). My idea was to avoid having to keep track of N for last backup. The script scheduling would be a bit simpler. And I do realize the N=1 backup right after the N=0 backup will store nothing. It will, however, make it possible to do the N=2 backup next day and have a complete N-chain. Skipping the N=1 backup and go right for N=2 won't work. or am I missing something? Kjell
Re: [firebird-support] Re: nbackup strategy advice
Hi Kjell, If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. That's not necessary, and the higher level backup will add nothing on that moment. Suppose you make a level-2 backup every Sunday. Then it makes sense to schedule the level-3 backups daily from Mon-Sat. If you want to make a point-in-time restore later, it will involve 4 files if that point in time is on Mon-Sat, and 3 files if it is on a Sunday. (And 2 files if it is the first day of the month.) All you have to do is determine the most recent backup before the chosen point in time. If that is a level N, you need N+1 files for the restore (levels 0-N, each one being the most recent file of that level before time 'T'). My idea was to avoid having to keep track of N for last backup. The script scheduling would be a bit simpler. And I do realize the N=1 backup right after the N=0 backup will store nothing. It will, however, make it possible to do the N=2 backup next day and have a complete N-chain. Skipping the N=1 backup and go right for N=2 won't work. or am I missing something? No, you're right. A level N backup is always based on the most recent level (N-1) backup. So, suppose the first of the month is on a Tuesday, and the level 1 backup is made. Level 2 backups are scheduled every Sunday and level 3 backups every working day. In that case, the level 3 backup of Wednesday the 2nd will be based on the level 2 backup of Sunday the 29th or 30th of the previous month. The most recent level 1 backup won't be in its restore chain. That's not intrinsically bad, but it's confusing. So after the monthly level 1, it makes sense to have it followed immediately by a 'weekly' backup, even if it's the 'wrong' day of the week. But the daily backups can start the next day, *unless* there are also hourly backups scheduled (N=4). I also realize now that I automatically assumed that nothing happens during weekends, but of course that depends entirely on the situation. Kind regards, Paul Vinkenoog
Re: [firebird-support] Deadlock
Session login is is unique between logins, but will be reused by the same user. I've no doubt that there are two attempts to update the same record. That is because session login is used to validate the user has permission to write the record they are attempting to. In the scenario described, the user will be iterating through a javascript object on the client browser answering a series of questions. When they answer, that answer will be persisted to the Db, I'm using FirebirdSql.Data.FirebirdClient, which used the ADO.NET Data Provider. I don't specify the transaction object which according to my understanding I would then be using the implicit transaction and the default isolation level. I do not explicitly commit the transaction in code nor in the procedure, as I was under the impression that the implicit transaction would do that. Dixon Epperson
