Friday, January 6, 2012

Database Systems Lecture 1 & 2 (MAJU Spring 2012)

Topics of the Lesson:
• Introduction of Database
• File-based systems
• Database management systems
• Comparison between File-based system and Database management systems

Functions of DBMS
Introducing Database:
The simplest definition is given by Date (1999, p. 2) according to which Database is a
collection of computerized data files. In simple words it is computerized
record keeping.

Examples of Database Applications
• Purchases from the supermarket
• Purchases using your credit card
• Booking a holiday at the travel agents
• Using the local library
• Taking out insurance
• Using the Internet
• Studying at university

File-based Systems
• Collection of application programs that perform services for the end users (e.g. reports).
• Each program defines and manages its own data
• E.g. is a C++ system that accepts and stores data. In such case the sequence in which the fields are recorded is coded in the program, not in the file.

Limitations of File-Based Approach

• Separation and isolation of data
– Each program maintains its own set of data.
– Users of one program may be unaware of potentially useful data held by other programs.
– Duplication of data
– Same data is held by different programs.
– Wasted space and potentially different values and/or different formats for the same item.

• Duplication of data
– Same data is held by different programs.
– Wasted space and potentially different values and/or different formats for the same item.

Database Approach

• Arose because:
– Definition of data was embedded in application programs, rather than being stored separately and independently.
– No control over access and manipulation of data beyond that imposed by application programs.
•
Result:
– the database and Database Management System (DBMS).

Formal definition of Database
• Shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization.
• System catalog (metadata) provides description of data to enable program–data independence.
• Logically related data comprises entities, attributes, and relationships of an organization’s
information.

Database Management System (DBMS)
• A software system that enables users to define, create, and maintain the database and that provides controlled access to this database.
Views
• Allows each user to have his or her own view of the database.
• A view is essentially some subset of the database.

Functions of Database Management System
· Provide Security
· Data Integrity
· Provide controlled concurrency
· Reduced data redundancy
· Crash Recovery
· Backup and Recovery

Friday, August 12, 2011

Introduction to WIX (Windows Installer XML)

Recently I have got an opportunity to work on creating installers for some of our products. In past I never got involved in post development activities but during my current job I spent some time in development related to deployment.
I have discovered there is a whole world out there to explore. Even the tip of the ice berg is quite vast.
Initially I created installers based on MSI technology using Visual Studio. However, I experienced that the approach was not friendly when it come to incorporate some product changes in installer. This is because we need to recompile the entire project and subsequently do some changes to resultant installer via orca. Addition/updation is a very common requirement in the web-based products we sell. For example, sometimes there is a small change like css update of an image/icon changes. This approach, which I was using required someone to rebuild the Visual Studio project which implies that visual studio is present on the machine. In addition, another dependency exists in the form of a person who knows visual studio and has some experience of creating installers.
Eventually a need of such an approach was felt which could free us from such dependencies.
The research resulted in the discovery of Microsoft WIX (Windows Installer Xml). WIX which is based on MSI technology allows us to descriptively create installers using XML files. There are tools through which people only specify the folders that need to be part of the installer and facilities are available to almost do whatever that is necessary while creating installer. Plug-ins are also available to integrate WIX with visual studio.
However, it must be kept in mind that the learning curve is quite steep. To facilitate people who are interested in learning WIX, I am listing some of the resources that I found useful. I will be adding some more resources to the list after filtering the collection I have

Creating WIX Installer for ASP.NET Web Application
http://www.codeproject.com/KB/install/asp_wix.aspx

From MSI to WiX
http://blogs.technet.com/b/alexshev/archive/2008/02/10/from-msi-to-wix.aspx

WIX Tutorial
http://wix.tramontana.co.hu/tutorial

Logging in IIS 7

Location of Log file:
%SystemDrive%\inetpub\logs\LogFiles

Pre-Requisite of Logging:
On Windows Server 2008 R2, following role service must be installed (In other Windows OS the following module must be enabled)
• Http Logging

Enabling and Disabling Logging:

If the pre requisite is installed the “Logging Icon” will appear in the “IIS” category of IIS Manager ([Server] Home Screen)
By default it is enabled, however it can be disabled or other configuration can be changed

References:
http://www.itsolutionskb.com/2009/10/how-to-find-iis-log-files/
http://technet.microsoft.com/en-us/library/cc754631(WS.10).aspx

Monday, August 1, 2011

AnjLab Profiler and “master.mdf…trc” files

SQL Profiler is a very useful tool to trace the execution of instructions over a database, however , it is not available for express edition of SQL Server databases
An alternate for this is a profiler from AnjLab which provides features quite similar to that of SQL Profiler.
Both SQL Profiler and AnjLab Profiler make use of the “Trace” facility available in the database engine of SQL Server.

Overall this profiler is harmless, however, there are two issues that must be considered when using the profiler

1. The profiler creates “master.mdf..yyyy.mm.dd.hh.min.sec.trc” file but (at least in our case it) does not delete the file when tracing is finished. These files are created in the default “DATA” folder of SQL Server instance (in our case). Since the files are not deleted automatically therefore a number of trace executions consume a lot of valuable space

2. In our scenario, if the profiler executes for a long time then it starts going into “Not Responding” states. Eventually the user is forced to close the application. In this case the “Trace” in SQL Server is not deleted thus it continues to execute and keeps on creating files unless explicitly closed using “sp_trace_setstatus”. (For more information on finding, stopping and closing traces see the article in this blog “SQL Server Trace Files Consuming Lot of Space”).

Wednesday, July 6, 2011

SQL Server Database becoming unavailable

Recently one of our Client went through a situation in which databases were becoming unavailable. We investigated the problem and searched for possible solutions.
Here is the summary of the activity

Findings

The error found in the client's database server log is
“FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.)”
Other messages do not seem to be suspicious
There are three popular causes of this error
Abnormal Shutdowns:
In case system abnormally shuts down due to power failure or was forced to shutdown due to a “Not responding state”, operating system processes like “Write caching on device” are not completed properly and some files are not released.
Due to this some databases are shown as suspect on restart
Solution:
Uncheck the “Enable write caching on device”, which can be found in settings of partition/drive. The settings can be opened via the Hardware Tab which gets displayed when properties are of the drive are accessed
MDF Files Used by other Processes:
There may be antivirus or backup routines scheduled on the system. SQL Server starts a particular database when it is accessed by users. In case when MDF or LDF file is already in use by an anti-virus scan or backup schedule, then the operating system does not allow SQL Server to use the particular database file
Solution:
Exempt Database files from anti-virus scans or schedule scans and backups at a time when Database is not in use
Note: Process Explorer is an off-the-shelf tool that can be used to investigate which processes are actually using/locking particular file.

Limited privileges to Logon Account:
Some technical resources suggest that the problem may be due to limited privileges to log-on account
Solution
Grant privileges to the Logon account so that it is able to access LOG folder of MS SQL Server

Saturday, June 11, 2011

SQL Server Trace Files Consuming Lot of Space

Recently we faced a situation in which Hard Drive of one of our server machines was getting filled up. Investigation resulted in the discovery that a large number of trace files were being created
Initial study developed an opinion that it was due to default trace, however a formal study resulted in the finding which have been listed in the form of following Q/A.
Note: Information Applies to SQL Server 2005 and later versions

WHY DEFAULT TRACING IS DONE?
Supply auditing and performance data to reports available through the Management Studio Object Browser. E.g. Schema change history report [1]
HOW CRITICAL IT IS?
It depends upon the scenario. The best approach is to see the information being recorded in the trace to determine whether the information is suitable for you or not [2]
HOW RESOURCE GREEDY IT IS?
“The default trace is composed of five 20 megabyte trace files that are accessed in a round robin fashion, When the first file is full, the trace moves to the next file in the sequence. When the fifth file is full, the first file is deleted and a new file is created” [1]
In case a lot of space is consumed and a lot of files are being created then there is a possibility that other traces are running as well.
HOW TO FIND OUT WHAT TRACES ARE RUNNING?
The following command can be used [3]
SELECT * FROM fn_trace_getinfo(default);
GO
HOW TO STOP A TRACE?
Information regarding stopping a trace can be found in [2],[4],[5].



REFERENCES
[1] Audit SQL Server with the Default Trace, July 2007, Online: http://www.sqlconsulting.com/news1007.htm, Accessed: 07 June 2011
[2] Kadlec, J. Default Trace in SQL Server 2005, MSSQL Tips, November 2006, Online: http://www.mssqltips.com/tip.asp?tip=1111, Accessed: 07 June 2011
[3] fn_trace_getinfo (Transact-SQL), MSDN, http://msdn.microsoft.com/en-us/library/ms173875.aspx Accessed: 07 June 2011
[4] sp_trace_setstatus (Transact-SQL), MSDN, http://msdn.microsoft.com/en-us/library/ms176034.aspx, Accessed: 07 June 2011

Saturday, May 28, 2011

List and Definitions of Each UML Diagram

Use Case Diagrams
The Use case diagram is used to identify the primary elements and processes that form the system. The primary elements are termed as "actors" and the processes are called "use cases." The Use case diagram shows which actors interact with each use case.
A use case diagram captures the functional aspects of a system. More specifically, it captures the business processes carried out in the system. As you discuss the functionality and processes of the system, you discover significant characteristics of the system that you model in the use case diagram. Due to the simplicity of use case diagrams, and more importantly, because they are shorn of all technical jargon, use case diagrams are a great storyboard tool for user meetings. Use case diagrams have another important use. Use case diagrams define the requirements of the system being modeled and hence are used to write test scripts for the modeled system
http://www.developer.com/design/article.php/2109801/Creating-Use-Case-Diagrams.htm

Activity Diagram
Activity diagrams are graphical representations of workflows of stepwise activities and actions with support for choice, iteration and concurrency.[1] In the Unified Modeling Language, activity diagrams can be used to describe the business and operational step-by-step workflows of components in a system. An activity diagram shows the overall flow of control.

http://www.answers.com/topic/activity-diagram

Class Diagrams:
A UML class diagram is similar to a family tree. A class diagram consists of a group of classes and interfaces reflecting important entities of the business domain of the system being modeled, and the relationships between these classes and interfaces. The classes and interfaces in the diagram represent the members of a family tree and the relationships between the classes are analogous to relationships between members in a family tree. Interestingly, classes in a class diagram are interconnected in a hierarchical fashion, like a set of parent classes (the grand patriarch or matriarch of the family, as the case may be) and related child classes under the parent classes.
http://www.developer.com/design/article.php/2206791/The-UML-Class-Diagram-Part-1.htm

State Diagrams
The name of the diagram itself clarifies the purpose of the diagram and other details. It describes different states of a component in a system. The states are specific to a component/object of a system.
A Statechart diagram describes a state machine. Now to clarify it state machine can be defined as a machine which defines different states of an object and these states are controlled by external or internal events.
http://www.tutorialspoint.com/uml/uml_statechart_diagram.htm

Interaction Diagrams
Interaction diagrams are models that describe how a group of objects collaborate in some behavior - typically a single use-case. The diagrams show a number of example objects and the messages that are passed between these objects within the use-case.
Interaction diagrams come in two forms, both present in the UML. The first form is the sequence diagram. In this form objects are shown as vertical lines with the messages as horizontal lines between them. This form was first popularized by Jacobson.
The second form of the interaction diagram is the collaboration diagram. Here the example objects are shown as icons. Again arrows indicate the messages sent in the use case. This time the sequence is indicated by a numbering scheme. Simple collaboration diagrams simply number the messages in sequence. More complex schemes use a decimal numbering approach to indicate if messages are sent as part of the implementation of another message. In addition a letter can be used to show concurrent threads.
http://www.cs.unc.edu/~stotts/145/CRC/Interactions.html

Sequence Diagram
A Sequence diagram depicts the sequence of actions that occur in a system. The invocation of methods in each object, and the order in which the invocation occurs is captured in a Sequence diagram. This makes the Sequence diagram a very useful tool to easily represent the dynamic behavior of a system.
A Sequence diagram is two-dimensional in nature. On the horizontal axis, it shows the life of the object that it represents, while on the vertical axis, it shows the sequence of the creation or invocation of these objects.
http://www.developer.com/article.php/3080941

Collaboration diagram
A Collaboration diagram is easily represented by modeling objects in a system and representing the associations between the objects as links. The interaction between the objects is denoted by arrows. To identify the sequence of invocation of these objects, a number is placed next to each of these arrows.
http://www.developer.com/article.php/3102981

Package diagram
A package provides the ability to group together classes and/or interfaces that are either similar in nature or related.
http://www.developer.com/design/article.php/2206791/The-UML-Class-Diagram-Part-1.htm Package diagram shows the arrangement and organization of model elements in middle to large scale project. Package diagram can show both structure and dependencies between sub-systems or modules.
http://www.visual-paradigm.com/VPGallery/diagrams/Package.html

Component Diagram
The component diagram's main purpose is to show the structural relationships between the components of a system. In UML 1.1, a component represented implementation items, such as files and executables. Unfortunately, this conflicted with the more common use of the term component," which refers to things such as COM components. Over time and across successive releases of UML, the original UML meaning of components was mostly lost. UML 2 officially changes the essential meaning of the component concept; in UML 2, components are considered autonomous, encapsulated units within a system or subsystem that provide one or more interfaces. Although the UML 2 specification does not strictly state it, components are larger design units that represent things that will typically be implemented using replaceable" modules. But, unlike UML 1.x, components are now strictly logical, design-time constructs.
http://www.ibm.com/developerworks/rational/library/dec04/bell/

Deployment Diagram
The Deployment Diagram also helps to model the physical aspect of an Object-Oriented software system. It models the run-time configuration in a static view and visualizes the distribution of components in an application. In most cases, it involves modeling the hardware configurations together with the software components that lived on.
http://www.visual-paradigm.com/VPGallery/diagrams/Deployment.html
Deployment diagrams are used to represent the physical architecture of a system. They present the distribution of the software components on the set of execution units (nodes).
Nodes and artifacts are the main concepts in a deployment diagram.
http://support.objecteering.com/objecteering6.1/help/us/objecteering_uml_modeler/diagrams/deployment_diagrams.htm