Omarsoft For IT Solutions (Java Codes ,C#.NET Codes , ASP.NET Codes ,VB.NET Codes ,Oracle Database Administration, Real Application Cluster , Remote Support, Cloud Services , Networks ,Virtualization....
  • الأنظــمــة المكتبية        Windows App Programming
  • أنظــمـةالويــب        Web based systems programming
  • تطبيقات الهواتف الذكية     smartphones programming
  • إدارة قواعــــــد البيــــــــــــــــانات        Database Administration
  • إدارة الشبكـــــــــــــــــــــــــــــــــات        Networks Administration
  • إدارة الســـيــرفرات (ويب - محلية)  Servers Administration
  • إدارة مخـــــــــــــــــازن البيــــــــــــانات     Storage Administration
  •             N Computing & 2X Application services

    Social Icons

Loading...

Creating a Disaster Recovery Plan




Creating a Disaster Recovery Plan 

Last week we decided on how often to backup our database; now we must decide where to backup our database. The first option available is to backup directly to tape from SQL Server. On the plus side, a backup to tape allows for off-site storage of backups. On the down side, tape backups are slow and therefore can impact the server for a longer amount of time than a backup to a file. The other common place to backup to is a file. File backups are much faster than a tape backup, for both backup and restore operations; however, backups to files don't allow for quick off-site storage (unless you happen to have a high speed remote link).
A third option is to use a combination of making backups to a file and then using another backup utility, such as NT Backup, to copy the file backups to tape. By making backups to files on another server nearby and then copying the files to tape, you can minimize the time a backup will impact your SQL Server while still allowing for off-site storage of tapes. Also, if you need to make multiple copies of tape backups, using another computer for copying backups to multiple tapes can help even more.
Another thing to think about when you are choosing where to make a backup is the time it takes to restore a backup. For example, a backup that is stored on another computer could be restored much faster over a high speed network than it could be from a tape drive. To take advantage of this faster recovery you may consider saving file backups for the week on another computer (in addition to your tape backups). In the event your SQL Server crashes you have a current backup on hand and available at a faster speed than tape.
File and tape backups do provide for lots of flexibility in designing your disaster recovery plan, but there are still many options available from third party venders. For example, you can find utilities that make the process of making backups of multiple SQL Servers very simple. You may also consider a Storage Area Network for large mission-critical systems. Although I would love to cover every option available, other backup utilities and hardware options are outside the scope of this series.
Before we can move on, there is a second half to deciding where to backup...choosing a tape, or file for that matter, rotation. If you used a new tape for each day's backup you would probably eat up a good part of your budget on nothing but tapes. In order to save money a few popular tape rotation schemes are in use. The rotation we are going to look at is known as the Grandfather-Father-Son rotation. Let's look at our example from last week to see how this rotation works (note: I have rearranged the order of the days from the last article):

MONTUEWEDTHUFRISATSUN
12:00 AMFULL
1:00 AM
2:00 AMDIFDIFDIFDIF DIFDIF
3:00 AM
In a Grandfather-Father-Son rotation you start out by using a new tape for each day of the week for the first week. For each week following the first week you reuse the same tapes except for the last tape of the week. By using a new tape at the end of each week you can keep an archive of data. In the event you need to restore data that was deleted or lost, the archive from past weeks is available. Once a month has gone by you keep the tape for the last week of that month and then reuse the end-of-week tapes. Here is what a Grandfather-Father-Son rotation would look like for our example over a two month period:
11 Tapes are used: M, T, W, TH, F, S, W1, W2, W3, Month1, Month2...

MONTUEWEDTHUFRISATSUN
Week 1
MTWTHFSW1
Week 2MTWTHFSW2
Week 3MTWTHFSW3
Week 4MTWTHFSMonth1

MONTUEWEDTHUFRISATSUN
Week 5
MTWTHFSW1
Week 6MTWTHFSW2
Week 7MTWTHFSW3
Week 8MTWTHFSMonth2
In our example we must take the monthly backup on Sunday because that is the only day we make a full backup. However, if you make a full backup of your database every day of the week you can use the monthly tape on the last day of the month no matter what day of the week it ends on. To illustrate, this is what a Grandfather-Father-Son rotation would look like if we took a full backup every night for the next two months:
Note that a new tape is substituted for the last day of each month. Also note that once the last day of the month has passed, the end-of-week tapes can then be reused.
May 2002, Wednesday 1st - Friday 31st:
MONTUEWEDTHUFRISATSUN
Week 1
WTHFSW1
Week 2MTWTHFSW2
Week 3MTWTHFSW3
Week 4MTWTHFSW4
Week 5MTWTHMonth1
June 2002, Saturday 1st - Sunday 30th:
MONTUEWEDTHUFRISATSUN
Week 1
SW1
Week 2MTWTHFSW2
Week 3MTWTHFSW3
Week 4MTWTHFSW4
Week 5MTWTHFSMonth2


-------------
Having decided on how often to backup and where to backup, you now must choose a location to store your tapes. Deciding on a location is going to depend greatly on your situation, but there are some general rules you should keep in mind. First of all, the location, either on or off-site, should be secure! If someone has access to your tapes...you might as well give them access to your server. Second, you need to find a balance between keeping the most recent tapes nearby (in case you need to restore a database) vs. the need to store tapes off-site (in case of a disaster).
One approach that we talked about earlier is storing a file backup on a second computer from your SQL Server. By doing this, not only can you recover from a crash faster, but it also allows you to store tapes off site without having to worry about going to get them to restore a database. You can also accomplish this same effect, minus the faster restore time, by making two sets of tape backups; one off-site for storage and one on-site for quick access. You may even consider making a third copy of your monthly backup for storage in a third off-site location. Also keep in mind that you don't have to keep all your tapes off-site. Depending on your needs, you may find that keeping the weekly backups, or even the monthly backups, off-site is adequate.
Before I move on, there is one last issue with backups that I would like to cover -- choosing someone to swap tapes. Once you have your plan in place you should designate a single person responsible for checking that backups took place and that tapes are swapped out as needed. It is important to have one person do this because when multiple people share the responsibility you end up with: "I thought you swapped the tapes last night?!?" When a single person is responsible for backups it becomes a routine for them. Now, if that one person is unable to swap tapes (ex: they get sick) it is their responsibility to find another person to swap tapes for them. Although another person may do the job of swapping tapes now and then, you still have the accountability of the single person who normally does the tape swapping, not a group of people. If the job is too big for one person, consider giving the responsibility for half the servers to one person and half to another person (or however you would like to split them up). Additionally, if you can't have the same person swapping tapes every day (ex: you take backups on the weekend), make it clear who is responsible for what days and keep the days the same from week to week.
So, having decided how often to backup, where to backup, and where to store our backups...what's left in a disaster recovery plan? Well quite a lot, but most of it is highly dependent on your environment. The first step is to document, document, and document. Get a folder and dedicate it to your disaster recovery plan. Here are some things that you should include in your plan:
- Server Hardware Specifications
- Network Layout
- Server Software Configurations
- Database File Layout (i.e. log files and data files)
- Label your tapes and include a backup and rotation description
The next step is to start thinking about, and write down, what should happen if a failure occurs. Keep in mind when you start to write out the plan, you should assume that you are not on-site and are unable to come to the rescue. You should also assume that the person restoring the server does have technical knowledge about SQL Server, but knows nothing about your particular setup. Think about things like:
- Who should be contacted if something goes wrong?
- Where are the backups stored?
- Where are the software and driver disks stored?
- Are there any tech support numbers available?
- If new hardware is required, what should be done?
- Is there any other information that may be useful?
Once you have completed documenting what should happen if a disaster occurs, there is one final step that you must complete...testing your plan. Having a plan is not enough, you have to test to see if your plan has all the necessary information, if your backups work correctly, and if everyone knows what to do. In order to do this you should setup a fake disaster. Now, don't go lighting your servers on fire (we all know how tempting that can be sometimes!), but use some extra hardware to test your plan. Don't worry about getting exactly the same setup (hardware wise), you will need just enough to run the services and any client applications. When testing, you should follow your disaster recovery plan and see if all the information is available in the plan. If you left anything out, or something was wrong, now is the time to make corrections and additions. By using the test hardware you not only get a feel for what information needs to be in your plan, but you are also able to test your backups by restoring the server from tape.

SQLite Tutorial I



 SQLite Tutorial

This Post helps you understand what is SQLite, how it differs from SQL, why it is needed and the way in which it handles the applications Database.
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that's growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain.

What is SQLite?

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a database, which is zero-configured, which means like other databases you do not need to configure it in your system.
SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly.

Why SQLite?

  • SQLite does not require a separate server process or system to operate (serverless).
  • SQLite comes with zero-configuration, which means no setup or administration needed.
  • A complete SQLite database is stored in a single cross-platform disk file.
  • SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.
  • SQLite is self-contained, which means no external dependencies.
  • SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.
  • SQLite supports most of the query language features found in SQL92 (SQL2) standard.
  • SQLite is written in ANSI-C and provides simple and easy-to-use API.
  • SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).

SQLite A Brief History

  • 2000 - D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program.
  • 2000 - In August, SQLite 1.0 released with GNU Database Manager.
  • 2011 - Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).

SQLite Limitations

There are few unsupported features of SQL92 in SQLite which are listed in the following table.
Sr.No.Feature & Description
1
RIGHT OUTER JOIN
Only LEFT OUTER JOIN is implemented.
2
FULL OUTER JOIN
Only LEFT OUTER JOIN is implemented.
3
ALTER TABLE
The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported.
4
Trigger support
FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
5
VIEWs
VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view.
6
GRANT and REVOKE
The only access permissions that can be applied are the normal file access permissions of the underlying operating system.

SQLite Commands

The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature −

DDL - Data Definition Language

Sr.No.Command & Description
1
CREATE
Creates a new table, a view of a table, or other object in database.
2
ALTER
Modifies an existing database object, such as a table.
3
DROP
Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language

CommandDescription
INSERTCreates a record
UPDATEModifies records
DELETEDeletes records

DQL - Data Query Language

Sr.No.Command & Description
1
SELECT
Retrieves certain records from one or more tables


SQLite is famous for its great feature zero-configuration, which means no complex setup or administration is needed. This chapter will take you through the process of setting up SQLite on Windows, Linux and Mac OS X.

Install SQLite on Windows

  • Step 1 − Go to SQLite download page, and download precompiled binaries from Windows section.
  • Step 2 − Download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files.
  • Step 3 − Create a folder C:\>sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files.
  • Step 4 − Add C:\>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display the following result.
C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Install SQLite on Linux

Today, almost all the flavours of Linux OS are being shipped with SQLite. So you just issue the following command to check if you already have SQLite installed on your machine.
$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
If you do not see the above result, then it means you do not have SQLite installed on your Linux machine. Following are the following steps to install SQLite −
  • Step 1 − Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section.
  • Step 2 − Run the following command −
$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix = /usr/local
$make
$make install
The above command will end with SQLite installation on your Linux machine. Which you can verify as explained above.

Install SQLite on Mac OS X

Though the latest version of Mac OS X comes pre-installed with SQLite but if you do not have installation available then just follow these following steps −
  • Step 1 − Go to SQLite download page, and download sqlite-autoconf-*.tar.gz from source code section.
  • Step 2 − Run the following command −
$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local
$make
$make install
The above procedure will end with SQLite installation on your Mac OS X machine. Which you can verify by issuing the following command −
$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
Finally, you have SQLite command prompt where you can issue SQLite commands for your exercises.

Sana'a Yemen - 50th st.

+967 738166685

omar.soft2000@gmail.com

للاتصال بنا CONTACT US

الاسم Name

بريد إلكتروني Email *

رسالة Message *

2015-2023 © All Rights Reserved Omarsoft
Back To Top