SQL Keys

In SQL, keys are used to maintain referential integrity among relations. Put simply, this means keys allow tables to reference each other, and each reference will be “correct” every time. Referential integrity also prevents records from being “dangled” or “orphaned” by another record that has been deleted. 

Primary Keys and Foreign Keys: The Basics

As an example, let’s consider an office employee roster with two tables.

The first table contains a list of departments and their IDs. Perhaps “billing” has ID #1, “marketing” has ID #2, and “accounting” has ID #3. The column for these unique IDs would be a PRIMARY KEY.


The second table, then, is a list of employees, with several columns: one each for the employee’s first and last names, one for their employee ID, and another for the ID of the employee’s department. The employee ID is another PRIMARY KEY. The column representing the employee’s department ID is called a FOREIGN KEY and links the values in that column to the department IDs in the department table. Let’s say Betty Smith works in billing, John Brown and Samantha Jones work inmarketing, and David Parker works in accounting. The creation of these tables is shown in Figure 1.

 

 Please note PostgreSQL does not use the FOREIGN KEY constraint in the CREATE TABLE command, as shown in Figure 1, presumably because the developers consider mentioning both FOREIGN KEY and REFERENCES to be being redundant. However, many other SQL databases require the explicit use of the FOREIGN KEY keyword.

A simple JOIN operation could be used, then, to determine in which department each employee works, without having to manually interpret the department ID number. This is shown in Figure 2.

 

This is easy enough to do, but there are some problems that arise from having one table reference another table. The most immediate problem, in this case, would be to determine what would happen if, all of a sudden, the billing department were to be deleted? Would Betty Smith just vanish? Fortunately, the SQL:2003 standard defined five different ways by which this situation can be handled. These are called referential actions.

SQL Perl/PL Overview

Procedural languages provide the fundamental method for programming within an SQL database. In other articles in this series, we have covered the SQL-based procedural languages, but those are generally not the only procedural languages found in an SQL database. Procedural extensions for Perl and Java tend to be the most popular additions, but some database systems implement many more additional languages.

The following examples make use of PostgreSQL’s implementation of PL/Perl, but the examples should be relevant and similar to other procedural language extensions in most databases that support such additions.

Installing and Enabling PL/Perl

In an operating system that is bundled with numerous packages, like Red Hat Linux, the PL/Perl language should be available for immediate use. In other operating systems, it may need to be downloaded and installed as a package from that operating system’s package repository.

Debian Linux, for example, does not include PL/Perl in the base PostgreSQL package, and it must be downloaded and installed separately. In Debian and other APT-based Linux distributions, like Ubuntu, the command to do this for PostgreSQL 8.1 is apt-get install postgresql-plperl-8.1. The postgresql-plperl-8.1 package may also be installed via a package manager like dselect or aptitude. As of this writing, November, 2007, PL/Perl is available in Debian’s APT package repositories for the testing (etch) distribution for PostgreSQL versions 8.1 and 8.2.

Once PL/Perl is installed on the computer, it must be enabled for each database that will use PL/Perl functions. This may be accomplished by executing the CREATE LANGUAGE plperl command in the psql SQL shell.

“Hello, World!”

Naturally, the first task one should complete is the creation of the simple “Hello, World!” function. Figure 1 shows the creation and execution of this function.

The only real difference between this function and a procedural SQL “Hello, World!” function is that it is written in Perl, instead of SQL. As long as the developer knows how to program in Perl, writing PL/Perl code is quite simple. Also note the LANGUAGE plperl clause, which lets PostgreSQL know the function is written in PL/Perl. In Figure 1, we simply returned the “Hello, World!” string, and it was displayed in the result set of the SELECT hello_world() query.

Concatenation

Another simple example, the concatenate() function, is shown in Figure 2.

Figure 2 shows the creation of concatenate(), its use as a function, and its use as both a function and as a parameter passed to itself. In the function, the two TEXT parameters are shifted out of the parameter list, and Perl’s . operator is used to concatenate them. As Perl developers are wont to say, “there is more than one way to do it,” and that is certainly the case with a function like this. Another popular way of writing this function is shown in Figure 3.

What’s New in SQL Server 2005—Enhancements for Data Base Administrators.

SQL Server 2005 has a single management console that enables data base Administrators monitor, manage and tune all databases and services. The SQL Management Object(SMO) is an extensible management infrastructure can be easily programmed. It exposes all the management functionalities of the SQL Server and is implemented as a Microsoft .NET Framework assembly. The primary purpose of the SMO is to automate administrative tasks such as retrieving configuration settings, creating new databases, applying T-SQL scripts, Creating SQL Server Agent jobs and so on. The users can customize or extend the management environment and build additional tools and functions to extend the capabilities that come packaged in the box. It is in short more reliable, scalable than Distributed Management Objects(DMO).

The SQL Server Management Studio is a one point access to a number of services – the relational database, the Integration services, Analysis services, Reporting services, Notification Services and SQL Mobile. Using this interface DBAs can author or execute a query, view server objects, manage an object, monitor system activity or even seek online help. As it is integrated with source control, scheduling of SQL Server Agentjobs also becomes possible. Daily maintenance and operation tasks can be monitored.

Administrators can now proactively monitor and tune the server using the Dynamic Management Views (DMVs). There are more than 70 new measures of internal database performance and resource usage.

One of the major concerns of the database administrator is to ensure continuous availability of data. Database mirroring, failover clustering, snapshots or fast recovery would be areas he would be concerned with. SQL Server 2005 allows continuous streaming of the transaction log from a source server to a destination server which takes over seamlessly in the case of failure of the primary server. Support for server clustering has been extended to Analysis services, Notification Services and SQL Server replication and the number of nodes has also been increased to eight. Instant, read only views of the database can be created using snapshots. These provide a stable view without the time or storage overhead normally required in these instances. The snapshot pages are added automatically as and when the pages are modified. Hence quick recovery becomes possible. This ability is further enhanced with the fast recovery option which enables users connect with a recovering database after a transaction has been rolled forward.

Running server connections can be accessed using the dedicated administrator connection even when the server refuses to respond. As a result diagnostic functions or T-SQL statements can be executed to troubleshoot problems on a server. The sysadmin fixed server role is activated by the members and can be accessed using the SQLCMD command prompt utility remotely or locally.

The indexing capabilities of the SQL Server 2005 have been greatly enhanced. Indexes can be created, rebuilt or dropped online without disturbing existing indexes. This online indexing capability allows parallel processing, concurrent modifications to the table in the database or clustered index data or any other associated indexes. Additionally the online restore option improves the availability of data even when restore operations are being performed.

The peer to peer model of replication enables synchronization of transactions with an identical peer database. This further improves availability.

Enhancements that ensure scalability include table partitioning, snapshot isolation, and 64 bit support. This improves query performance.

Table and index partitioning eases the management of large databases by dividing the whole into manageable chunks. The concept is not new to the SQL Server, but the partitioning of tables horizontally across file groups in the database is new. The partitioning can be made for gigabytes and terabytes and more.

The snapshot isolation feature allows users access the last row that was committed by providing a transactional and consistent view of the database. It makes for increased data availability for read only applications; it allows non blocking read only operations in OLTP environment; it automatically detects conflicts in write transactions and makes for simplified migration of applications from Oracle to SQL Server.

The Replication monitor tool defines a new standard for managing complex data replication operations. Its interface is intuitive and has a number of data metrics that are useful.

The new SQL Server 2005 is optimized for the Intel Itanium processor and takes advantage of the advanced memory capabilities for essential resources such as buffer pools, caches and sort heaps. This reduces the need to perform multiple I/O operations and makes for greater processing capacity without the disadvantage of I/O latency. The support for 32 bid applications is retained while 64 bit capabilities have been introduced to make the migration smooth and efficient.

The security model of the database platform now provides more precise and flexible control for ensuring security of data. It enforces passwords for authentication, provides granularity in terms of specifying permissions in the authorization space and separates owners and schemas for the manager.

The Encryption capabilities of the database have been integrated with the management infrastructure for centralization of security assurance and server policy.

Secure computing measures have been put in place to enable deployment of a secure environment. Confidentiality, integrity and availability of data and systems is the primary focus at every stage of the software life cycle—from design to delivery and maintenance.

A new application framework with Service Broker, Notification Services, Server Mobile and Server Express has been introduced. The Service Broker is a distributed application that provides reliable asynchronous messaging at the database to data base level. Notification services helps in development and deployment of applications that generate and send personalized notifications to a wide variety of devices based on preferences specified by the application user. SQL Server Mobile edition enables the creation of a mobile edition database on the desktop or device directly from SQL Server Management Studio. The schema can be manipulated locally or remotely.

SQL Server Express Manager (XM) is a free Query Editor tool that is available for download and allows for easy database management and query analysis capabilities.

Business Intelligence in SQL Server 2005 is scalable, comprehensive and comes with a number of reporting capabilities. Both basic and innovative kinds of analytical applications can be built from end to end.

The Integration services are a redesigned enterprise ETL platform that enables users integrate and analyze data from multiple heterogeneous sources. Significantly, SQL Server 2005 goes beyond traditional services and supports Web services and XML and out of the box services through SSIS to bring analytics to the data without persisting data, Data mining and text mining in data flow for data quality and data cleansing.

Analysis Services provides a unified and integrated view of the business data by using the Unified Dimensional Model which is mapped to a host of heterogeneous back end data sources. User friendly descriptions and navigation hierarchies make it a pleasure to use.

The Data mining and Intelligence technology is designed to build complex analytical models and integrate such models to the business operations. The rich set of tools, API’s and algorithms provides customized data driven solutions to a broad range of business data mining requirements.

Reporting services is a server based BI platform managed via Web Services. Reports can be delivered in multiple formats interactively. Relational and OLAP Reports comes with an inbuilt query editors—SQL Query Editor and MDX Query Editor. The reports can be built together or separately. The Report builder feature has been newly introduced to enable BI users to create customized reports that rides on the Microsoft Office paradigms of Excel and PowerPoint and has a clickonce feature that enables it to be deployed via a browser.


Sponsored Links

SQL Server 2005 Tutorials

* SQL Server Integration Services
* SQL Server 2005 - Configuring Replication
* SQL Server 2005 Replication Enhancements
* SQL Server 2005 - Mirror Server
* SQL Server 2005 - Introduction to Data Availability
* SQL Server 2005 - Backing up a Database
* SQL Server 2005 - Using Database Snapshots
* SQL Server 2005 - Disaster Recovery
* SQL Server 2005 - Managing Certificates
* SQL Server 2005 - Managing Permissions
* Managing SQL Server 2005 Security
* SQL Server 2005 - Using the Database Tuning Advisor
* SQL Server 2005 - Tuning a Database
* Maintain indexes in a SQL Server 2005 database
* SQL Server 2005 - Defining Indexes
* SQL Server 2005 - Database Backup
* SQL Server 2005 - Populating the Database
* SQL Server 2005 Configuration Manager
* SQL Server 2005 - Using the Sqlcmd Utility
* Using the SQL Management Objects


Home Tutorials SQL Server 2005

SQL Server 2005 Architecture Overview

Category: SQL Server 2005
Comments (0)

Overview of SQL Server 2005 Architecture

In this tutorial you will learn about SQL Server 2005 Architecture, What’s New in SQL Server 2005—Enhancements for Data Base Administrators. indexing capabilities, peer to peer model of replication, Table and index partitioning, snapshot isolation, Replication monitor tool, security model, Encryption capabilities, Secure computing, A new application framework,SQL Server Express Manager (XM), Business Intelligence in SQL Server 2005, Integration services, Analysis Services, Data mining, Reporting services, Windows Server System Common Engineering Roadmap,


Introduction

The innumerable data challenges faced by modern day organizations have leveraged the need for faster and more data driven decisions. The drive is to increase productivity, flexibility of human resources, to reduce overall investments in technology while scaling the infrastructure to meet the growing demand for information that enable informed mission critical decisions.

The release of SQL Server 2000 was one of the corner stones of Microsoft’s strategy for the back office. Its integration with the .NET family of server applications has gone a long way in establishingSQL server as one of the most robust servers for enterprise database management.

MSSQL Server 2005 is truly the next generation data management and analysis solution that is built for scalability, availability, analysis and security of data. The increasing ease with which database applications can be built has reduced the complexities of deploying and managing database applications. Data can now be shared across platforms, applications and devices making it possible to network internal and external systems seamlessly. Performance, availability, scalability and security are now available for lower costs. It is now a secure, reliable and productive platform for enterprise data andbusiness intelligence tools.

SQL Server 2005 has a number of tools to help the Database Administrator and the Developer. The relational database engine has been improved to give better performance and support for both structured and unstructured (XML) data. The Replication services include services for distributed or mobile data processing applications. It provides for high systems availability, scalable concurrency with secondary data stores, enterprise reporting solutions and integration with heterogeneous systems such as Oracle databases. The deployment of scalable, personalized, timely information updates through web based applications has been made possible with the advanced Notification capabilities of the SQL Server 2005. The extraction, transformation and load process has been further enhanced and online analytical processing render rapid, sophisticated analysis of large and complex data sets using multidimensional storage. The Reporting services features have been honed up to create comprehensive solutions for managing, creating and delivering traditional and paper oriented reports or interactive, web based reports. Management tools for database management and tuning have been fine tuned to integrate with Microsoft operations manager and Microsoft System Management Server. The data access protocols reduce the time taken for integrating data in theSQL server with existing systems. A number of development tools have been provided and integrated with Microsoft Visual Studio to provide an end to end application development capability.

With SQL Server 2005 customers will now be able to leverage data assets to get more value from their data by using the reporting, analysis and data mining functionalities embedded in the software. The Business Intelligence capabilities are integrated to the Microsoft Office System, to enable transmission of mission critical business information across the organization. The complexity of developing, deploying and managing line of business and analytical applications has been greatly reduced by use of a flexible development environment and automated tools for database management. Finally, the cost of ownership has been reduced by a focus on ease of use and integrated approach.

What’s New in SQL Server 2005—Enhancements for Data Base Administrators.

SQL Server 2005 has a single management console that enables data base Administrators monitor, manage and tune all databases and services. The SQL Management Object(SMO) is an extensible management infrastructure can be easily programmed. It exposes all the management functionalities of theSQL Server and is implemented as a Microsoft .NET Framework assembly. The primary purpose of the SMO is to automate administrative tasks such as retrieving configuration settings, creating new databases, applying T-SQL scripts, CreatingSQL Server Agent jobs and so on. The users can customize or extend the management environment and build additional tools and functions to extend the capabilities that come packaged in the box. It is in short more reliable, scalable than Distributed Management Objects(DMO).

The SQL Server Management Studio is a one point access to a number of services – the relational database, the Integration services, Analysis services, Reporting services, Notification Services and SQL Mobile. Using this interface DBAs can author or execute a query, view server objects, manage an object, monitor system activity or even seek online help. As it is integrated with source control, scheduling ofSQL Server Agent jobs also becomes possible. Daily maintenance and operation tasks can be monitored.

Administrators can now proactively monitor and tune the server using the Dynamic Management Views (DMVs). There are more than 70 new measures of internal database performance and resource usage.

One of the major concerns of the database administrator is to ensure continuous availability of data. Database mirroring, failover clustering, snapshots or fast recovery would be areas he would be concerned with. SQL Server 2005 allows continuous streaming of the transaction log from a source server to a destination server which takes over seamlessly in the case of failure of the primary server. Support for server clustering has been extended to Analysis services, Notification Services andSQL Server replication and the number of nodes has also been increased to eight. Instant, read only views of the database can be created using snapshots. These provide a stable view without the time or storage overhead normally required in these instances. The snapshot pages are added automatically as and when the pages are modified. Hence quick recovery becomes possible. This ability is further enhanced with the fast recovery option which enables users connect with a recovering database after a transaction has been rolled forward.

Running server connections can be accessed using the dedicated administrator connection even when the server refuses to respond. As a result diagnostic functions or T-SQL statements can be executed to troubleshoot problems on a server. The sysadmin fixed server role is activated by the members and can be accessed using the SQLCMD command prompt utility remotely or locally.

The indexing capabilities of the SQL Server 2005 have been greatly enhanced. Indexes can be created, rebuilt or dropped online without disturbing existing indexes. This online indexing capability allows parallel processing, concurrent modifications to the table in the database or clustered index data or any other associated indexes. Additionally the online restore option improves the availability of data even when restore operations are being performed.

The peer to peer model of replication enables synchronization of transactions with an identical peer database. This further improves availability.

Enhancements that ensure scalability include table partitioning, snapshot isolation, and 64 bit support. This improves query performance.

Table and index partitioning eases the management of large databases by dividing the whole into manageable chunks. The concept is not new to the SQL Server, but the partitioning of tables horizontally across file groups in the database is new. The partitioning can be made for gigabytes and terabytes and more.

The snapshot isolation feature allows users access the last row that was committed by providing a transactional and consistent view of the database. It makes for increased data availability for read only applications; it allows non blocking read only operations in OLTP environment; it automatically detects conflicts in write transactions and makes for simplified migration of applications from Oracle to SQL Server.

The Replication monitor tool defines a new standard for managing complex data replication operations. Its interface is intuitive and has a number of data metrics that are useful.

The new SQL Server 2005 is optimized for the Intel Itanium processor and takes advantage of the advanced memory capabilities for essential resources such as buffer pools, caches and sort heaps. This reduces the need to perform multiple I/O operations and makes for greater processing capacity without the disadvantage of I/O latency. The support for 32 bid applications is retained while 64 bit capabilities have been introduced to make the migration smooth and efficient.

The security model of the database platform now provides more precise and flexible control for ensuring security of data. It enforces passwords for authentication, provides granularity in terms of specifying permissions in the authorization space and separates owners and schemas for the manager.

The Encryption capabilities of the database have been integrated with the management infrastructure for centralization of security assurance and server policy.

Secure computing measures have been put in place to enable deployment of a secure environment. Confidentiality, integrity and availability of data and systems is the primary focus at every stage of the software life cycle—from design to delivery and maintenance.

A new application framework with Service Broker, Notification Services, Server Mobile and Server Express has been introduced. The Service Broker is a distributed application that provides reliable asynchronous messaging at the database to data base level. Notification services helps in development and deployment of applications that generate and send personalized notifications to a wide variety of devices based on preferences specified by the application user. SQL Server Mobile edition enables the creation of a mobile edition database on the desktop or device directly from SQL Server Management Studio. The schema can be manipulated locally or remotely.

SQL Server Express Manager (XM) is a free Query Editor tool that is available for download and allows for easy database management and query analysis capabilities.

Business Intelligence in SQL Server 2005 is scalable, comprehensive and comes with a number of reporting capabilities. Both basic and innovative kinds of analytical applications can be built from end to end.

The Integration services are a redesigned enterprise ETL platform that enables users integrate and analyze data from multiple heterogeneous sources. Significantly, SQL Server 2005 goes beyond traditional services and supports Web services and XML and out of the box services through SSIS to bring analytics to the data without persisting data, Data mining and text mining in data flow for data quality and data cleansing.

Analysis Services provides a unified and integrated view of the business data by using the Unified Dimensional Model which is mapped to a host of heterogeneous back end data sources. User friendly descriptions and navigation hierarchies make it a pleasure to use.

The Data mining and Intelligence technology is designed to build complex analytical models and integrate such models to the business operations. The rich set of tools, API’s and algorithms provides customized data driven solutions to a broad range of business data mining requirements.

Reporting services is a server based BI platform managed via Web Services. Reports can be delivered in multiple formats interactively. Relational and OLAP Reports comes with an inbuilt query editors—SQL Query Editor and MDX Query Editor. The reports can be built together or separately. The Report builder feature has been newly introduced to enable BI users to create customized reports that rides on the Microsoft Office paradigms of Excel and PowerPoint and has a clickonce feature that enables it to be deployed via a browser.


Windows Server System Common Engineering Roadmap defines a standard set of capabilities of the server system such as common patch management, Watson Support and tools such as Microsoft Baseline Security Analyzer for delivery of a consistent and predictable experience for the Administrator. It creates a set of services that can be implemented across all Windows platforms and raises the bar on server infrastructure by ensuring that security, reliability, manageability and flexibility are taken into consideration. It adopts services oriented architecture and integrates with .NET to connect people, systems and devices through software. It focuses on delivering systems that are focused on dynamic operations building and monitoring. Its corner stone is trustworthy computing. 16 specifications have been defined for application throughout the Windows Server System. Microsoft proposes to add further specifications at regular intervals to address customer requirements.

SQL Server 2005 as described above comes with significant advances in the areas of enterprise data management, developer productivity and business intelligence. It helps leverage data assets, increase productivity, reduces information technology complexity and reduces the overall cost of ownership.

SQL Server 2005 - Service Broker

The process of sending and receiving asynchronous messages by different processes in SQL Server 2005 is enabled by the Service Broker. The Transact SQL Data Manipulation Language is used to the send messages to a queue in the database of the sender or to another database in the SQL Server instance, or another server instance or remote server. Conceptually the process involves queues, dialogs, conversations groups and activation. In this tutorial we will briefly examines these concepts before having a practical look at what happens when messages are sent or received bythe service broker.

Service Broker objects and architecture

The Service Broker consists of a number of SQL Server objects that represent the various components of the asynchronous messaging system. A service is a set of tasks that is available, while message types represent the messages exchanged to complete a task. The messages are held in a queue and these messages are processed at predefined intervals and sent. Once the message has been sent, the application updates the data to indicate that the message has been sent.

To facilitate this process, a number of additions have been made to SQL Server database. Message types, queues, services, contracts and conversations have been added to create the various capacities of the Service Broker.

The Message type object determines the structure of the messages that can be sent from one service to another. The message type is created using the CREATE MESSAGE TYPE statement. This statement allows developers specify the format of the messages and constrain messages in varying degrees. This can be a simple varbinary message type that will allow only messages of the varbinary type or xml Message type which will perform validation via XML and needs to include the VALIDATION clause and reference existing XML schema collections.

The Contract object is an agreement between two services that helps prevent various parts of the application from sending bad messages. Message types are used to establish this agreement between services. All messages bound by this contract must use the message type whatever its structure. The SENT BY clause indicates the sender of the message and mandates that the message can only be sent by the initiator of the conversation. INITIATOR defines the initiator of the conversation and the TARGET indicates the recipient of the conversation. ANY indicates that either service can send the message.

Queues

A queue is an object that holds the messages for the service. It is defined in the CREATE QUEUE statement. These queues can be defined to be accessed at the option of the user or can be set to respond as and when messages are received. The syntax would be as under:

CREATE QUEUE studentmsgqueue

If the messages have to be processed as and when received, a stored procedure will have to be defined as under:

CREATE PROCEDURE NewStudentlocator
AS
DECLARE @hConversation uidentifier,
.....@msgTypeName nvarchar(256),
.....@msg varbinary(max)
WHILE(1-1)
BEGIN
.....BEGIN TRANSACTION
.....WAITFOR
(
.....RECEIVE TOP(1)
..........@hConversation=conversation_handle,
..........@msgTypeName=message_type_name,
..........@msg=message_body
.....FROM dbo.Studentmsgqueue
), TIMEOUT=500
.....
IF @@ROWCOUNT=0
BEGIN
.....ROLLBACK TRANSACTION
.....BREAK
END
IF @msgTypeName=’Example/StudentID’
BEGIN
.....--Process incoming messages of type EXFORExample/StudentID here
.....COMMIT TRANSACTION
.....CONTINUE
END
IF @msgTypeName=’http://schemas.xxx.com/SQL/ServiceBroker/EndDialog’
BEGIN
.....--Process end of dialog messages here
...............END CONVERSATION @hConversation
...............COMMIT TRANSACTION
...............CONTINUE
..........END
..........ROLLBACK TRANSACTION
.....END
GO
CREATE QUEUE dbo.STUDENTMSGQUEUE
.....WITH ACTIVATION
.....(PROCDURE_NAME=NewStudentLocator,
.....STATUS=ON,
.....MAX_QUEUE_READERS=1,
.....EXECUTE AS SELF)

Microsoft recommends that developers should check for ‘Error’ and ‘DialogTimer’ also in addition to the above check for ‘ÉndDialog’.

Dialogs

Dialogs are implemented as bidirectional streams of messages between two endpoints. The messages in a dialog are ordered and delivered in the order of dispatch. This order is not disturbed across transactions, inputs, threads, or crashes and restarts. This is a unique feature ofthe Service Broker. Each message is given a unique identifier in form of a conversation handle. This helps identify the application that sent the message.

Conversation groups

Dialogs are grouped in accordance with the task they have to perform. This grouping is provided by conversation groups implemented bythe conversation group identifier which is included in the messages. The conversation group is locked when a message is received from any of the dialogs in the group and is held by the receiving transaction for the duration of the transaction. This makes theapplication more scalable as a number of threads can be written and are processed only one at a time and the application need not be made resilient to problems caused by simultaneous processing of multiple threads.

The conversation group identifier labels the state associated with a process and can be used as the primary key in the state tables to enable quick retrieval of the state associated with the message.

Activation

The activation feature of the Service Broker can be used to specify a stored procedure and handle messages destined for a particular service. On receipt of the message the service broker checks whether there is a stored procedure running for the message and hands over the message to the procedure to process. If there is no procedure running a procedure is started. This procedure processes the messages until the queue is empty. In instances where messages are queued faster than they are processed,the Service Broker starts additional instances of the procedure to keep the processing of messages optimal. The maximum number of procedures to be run has to be configured for this purpose.

Use of Asynchronous, Queued Messaging

Scheduling of work is enabled by queues and this, results in scalability and enhanced performance. Some parts of the message such as the header will have to be processed before the message can be committed. The other parts of the message can be processed in an asynchronous manner if the core is processed in the beginning and it can provide opportunities for increased parallel processing for improved response time. Queuing also enable systems distribute processing evenly across the server time and reducing the peak capacity required by the server.

Use of Transactional Messaging

Transactional messaging is supported by the Service Broker. When a transaction fails the sends and receives are rolled back and will not take effect until the transaction is processed and the messages are successfully committed. An advantage of this messagingapplication is that sends and receives can be scattered and nothing happens till the transaction is committed. If the transaction is rolled back both sends and receives are relegated to the queue for reprocessing.

Solutions to the Hard Problems in Messaging

Messaging issues include message ordering, coordination, multithreading and receiver management. Each of these areas presents their own problems.

Message Ordering is the process of placing the messages in the order they are sent or received for processing. In traditional scenarios, message ordering was cumbersome and it was easier to deliver messages out of order. Programmers undertook elaborate coding to ensure that message 2 was received before message 3 was processed and so on by caching the messages and processing them in that order. ServiceBroker makes the whole process transparent by sending out the messages in sequence and processing them in the sequence sent.

Another associated problem of traditional messaging, namely the acknowledgement of messages received, is also resolved by ServiceBroker. In traditional applications if there is a power failure during a message send operation, the application assumes that the message was not sent and sends it again. There was always a possibility that the message would be received twice or acknowledged twice. ServiceBroker ensures that messages are not sent twice or acknowledged twice even if power is lost in the middle of a transaction.

Coordination is an essential feature as messages are viewed as stand alone entities and are processed individually. To identify the conversation from which the message is received, is sometimes rendered difficult as the processing of the request and the response may differ from one message in a conversation to another. When a number of conversations are initiated and responses are awaited the problem becomes further complicated. The Service Broker provides each message with a dialog handle and a unique conversation group identifier which enables the identification of the message with the conversation group.

Multithreading is the processing of multiple threads simultaneously. In traditional application multithreading often resulted in message threads being processed out of order. This problem has been solved by putting a lock on a conversation group as soon as the message is read, so that associated message threads are not received until the transaction is committed. This methodology is simple yet reliable.

Receiver management

Developers had to take care of two very important issues in a messaging system. They had to ensure that the receiving application is started before the sending application and they had to decide the number of threads or application instances must run in each queue. However, fixing these numbers presented their own problems. There may be too many or too few applications running as against the determined need. Service Broker simply activates the queue readers as required when the messages arrive. If the queue reader crashes or the system is rebooted the readers are automatically restarted to read the messages in the queue.

Messaging and the Database.

The advantages of building messaging into the database are many. The first of these is the conversation group locking mechanism. This a new type of database lock that is deciphered by the Service Broker commands. Secondly it enables the support for remote transactional receives from servers that can connect to the database. Thirdly, it synchronizes the message store and the database store by placing both the messages and the data in the same database. Backup and security issues also become easier to handle. Fourthly, since Service Broker is integrated into the database engine, messages addressed to another queue in any database on the same SQL Server instance can be directly put into the receive queue without routing it through the send queue. This improves performance.

Microsoft Navision DB Selection: C/SIDE or MS SQL Server - overview for IT Specialist

Microsoft Business Solutions Navision serves both European and American megamarkets. It was originally written by Denmark-based Navision Software in its own proprietary language C/SIDE (Client/Server Integrated Development Environment). You should consider several factors in deciding on the database selection. Currently Navision is supported on two platforms - Navision native (C/SIDE) database and on Microsoft SQL Server. There are certain pluses and minuses in both cases and you should make your decision, based on your company needs. We'll give you some hints in this subject and you should know then where to look further.

• Legendary Strength of C/SIDE database - the history of Navision design reveals the primary goals to have its own proprietary database with the built-in features of database transaction integrity. As far as database is proprietary - Navision can manipulate its and tune it to serve its application. You can have users use the system heavily and suddenly shut down the power - when power is back on - Navision will be in the same good shap. The second goal was to provide robust graphical interface.

• What is the volume of your data? - Navision was originally targeting mid-size and large corporations. Now, however, Microsoft is trying to target it to large corporations with sophisticated manufacturing. In this case you could expect huge volume of data - Microsoft SQL Server is better positioned to deal with that.

• Do you have MS SQL Server Administrator? - Microsoft announces that MS SQL Server is moving to "zero-maintenance needed", but we would like to warn you - if you use Navision - you are more likely mid-size or large company. In this case you should have somebody to secure your database - in the case if you would like to deploy MSSQL Server

• Do you plan on Crystal Reports? - you can always deploy C/ODBC to enable your C/SIDE database for Crystal Report, but MS SQL Server has such nice features, as SQL Views and Stored Procedures, which make SQL Server preferable platform

• Do you need OLAP/Datawarehousing? - then SQL Server is the way to go. It has built in Analytical Server with OLAP cube creation and Excel data pivoting

• Do you plan to expose Navision data to other applications? - Navision has tools to expose the data from C/SIDE database (C/ODBC, C/FRONT), but in the case of SQL Server you certainly have standard Microsoft technologies in your disposition
3EZ2T3Y85QAM

Crystal Reports - Geterogeneus databases: SQL Server, Oracle, Unidata, Btrieve – tips for developer

Crystal Reports is the most flexible tool on the market and it has market recognition. Multiple software vendors offer Crystal Reports as the main reporting tool: Microsoft, Best Software, Oracle to name a few.

Sometimes developers try to use Crystal directly to pull data from multiple database platforms - we understand the need to give you additional tips to increase Crystal report flexibility. Also common case when Crystal report should pull data from multiple database platforms. Assuming that you are creating Crystal Reports on Windows platform - we recommend you to use Microsoft SQL Server as a database access tool.

1. Use Crystal when you have your query tuned – regardless how good Crystal Reports is - it is reporting tool - not querying environment. Tune your query first and be sure that you are pulling the right data.

2. Use SQL Server Linked Servers. Use professional tool, such as Microsoft SQL Server, where you can create linked servers to Oracle, Ctree, Btrieve, MS Access, Unidata, DB2 and other platforms.

3. Use Distributed Queries – in the case of SQL Server - research the possibilities with OPENQUERY, OPENROWSET clauses to connect to non-SQL Server linked servers.

4. Create Stored Procedures - when you need parameters to be transferred to the SQL statement - you need stored procedure. You need stored proc if temp tables are required to do intermediate job in your SQL query.

5. Report Flexibility – when you have report based on the SQL view or stored proc - if you need to modify the data query - you don't have to change the report - you change your SQL view/ stored procedure.

6. Create SQL Query to probe the data – again - we always recommend tuning your query and see that you are getting adequate results – in any case – Crystal Report is just a nice tool to show the results of your query.

Where can I get support PostgreSQL ?

The main mailing list is: pgsql-general@PostgreSQL.org. It is available for discussion of matters pertaining to PostgreSQL. To subscribe, send mail with the following lines in the body (not the subject line):

subscribe
end

to pgsql-general-request@PostgreSQL.org.

There is also a digest list available. To subscribe to this list, send email to: pgsql-general-digest-request@PostgreSQL.org with a body of:

subscribe
end

Digests are sent out to members of this list whenever the main list has received around 30k of messages.

The bugs mailing list is available. To subscribe to this list, send email to pgsql-bugs-request@PostgreSQL.org with a body of:

subscribe
end

There is also a developers discussion mailing list available. To subscribe to this list, send email to pgsql-hackers-request@PostgreSQL.org with a body of:

subscribe
end

Additional mailing lists and information about PostgreSQL can be found via the PostgreSQL WWW home page at:

http://www.PostgreSQL.org

There is also an IRC channel on EFNet and OpenProjects, channel #PostgreSQL. I use the Unix command irc -c '#PostgreSQL' "$USER" irc.phoenix.net.

A list of commercial support companies is available at http://techdocs.postgresql.org/companies.php.

Where can I get PostgreSQL?

The primary anonymous ftp site for PostgreSQL is ftp://ftp.PostgreSQL.org/pub. For mirror sites, see our main web site.

What non-Unix ports are available?

Client

It is possible to compile the libpq C library, psql, and other interfaces and client applications to run on MS Windows platforms. In this case, the client is running on MS Windows, and communicates via TCP/IP to a server running on one of our supported Unix platforms. A file win32.mak is included in the distribution for making a Win32 libpq library and psql. PostgreSQL also communicates with ODBC clients.


Server

The database server can run on Windows NT and Win2k using Cygwin, the Cygnus Unix/NT porting library. See pgsql/doc/FAQ_MSWIN in the distribution or the MS Windows FAQ at http://www.PostgreSQL.org/docs/faqs/text/FAQ_MSWIN.

A native port to MS Win NT/2000/XP is currently being worked on. For more details on the current status of PostgreSQL on Windows see http://techdocs.postgresql.org/guides/Windows.

There is also a Novell Netware 6 port at http://forge.novell.com.

What Unix platforms does PostgreSQL run on?

In general, a modern Unix-compatible platform should be able to run PostgreSQL. The platforms that had received explicit testing at the time of release are listed in the installation instructions.

What is the copyright on PostgreSQL?

PostgreSQL is subject to the following COPYRIGHT:

PostgreSQL Data Base Management System

Portions copyright (c) 1996-2002, PostgreSQL Global Development Group Portions Copyright (c) 1994-6 Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

The above is the BSD license, the classic open-source license. It has no restrictions on how the source code may be used. We like it and have no intention of changing it.

What is PostgreSQL? How is it pronounced?

PostgreSQL is pronounced Post-Gres-Q-L.

PostgreSQL is an enhancement of the POSTGRES database management system, a next-generation DBMS research prototype. While PostgreSQL retains the powerful data model and rich data types of POSTGRES, it replaces the PostQuel query language with an extended subset of SQL. PostgreSQL is free and the complete source is available.

PostgreSQL development is performed by a team of developers who all subscribe to the PostgreSQL development mailing list. The current coordinator is Marc G. Fournier (scrappy@PostgreSQL.org). (See section 1.6 on how to join). This team is now responsible for all development of PostgreSQL.

The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. Many others have contributed to the porting, testing, debugging, and enhancement of the code. The original Postgres code, from which PostgreSQL is derived, was the effort of many graduate students, undergraduate students, and staff programmers working under the direction of Professor Michael Stonebraker at the University of California, Berkeley.

The original name of the software at Berkeley was Postgres. When SQL functionality was added in 1995, its name was changed to Postgres95. The name was changed at the end of 1996 to PostgreSQL.

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

* UCASE() - Converts a field to upper case
* LCASE() - Converts a field to lower case
* MID() - Extract characters from a text field
* LEN() - Returns the length of a text field
* ROUND() - Rounds a numeric field to the number of decimals specified
* NOW() - Returns the current system date and time
* FORMAT() - Formats how a field is to be displayed

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

* AVG() - Returns the average value
* COUNT() - Returns the number of rows
* FIRST() - Returns the first value
* LAST() - Returns the last value
* MAX() - Returns the largest value
* MIN() - Returns the smallest value
* SUM() - Returns the sum

SQL Replication with Online Backup

It is vitally important that every business have a disaster recovery plan for its electronic data stores. Your company's data includes more than simple Word documents and e-mail messages; it also includes you databases and web site information.

One of the worst things that can happen to your business, whether it is large or small, is to lose any of your electronic data due to hardware failure, software corruption, or even to a natural disaster.

Unfortunately each of these three things happens more than you might want to think about. But, by being prepared you can be one step ahead of the game.

If you company uses an SQL database, it is vitally important that you have regular backups of the data. The best practice for any company is to have a local copy of the data as well as offsite backup in case of any major disaster to your physical building.

One of the best things you can do with your SQL data is to replicate the data to a backup server as well as an offsite backup location. This will ensure that your data is available should you ever need to restore it and there is some form of damage to your servers.

By having a local copy you can do simple restorations, and by having an offsite copy, you are covered in case something happens to your building.

Microsoft SQL Server allows you the ability to replicate your data to other servers running SQL Server, in order to backup your database. This process allows you to replicate your data to another server at your company location, or via the Internet to a SQL server hosted by an online data backup provider.

If your local server should fail, you will have the ability to restore it from one of your replicated copies and quickly get your database back up and running.

Microsoft SQL Server offers three types of SQL replication. They are: merge replication, snapshot replication and transactional replication.

Snapshot replication simply copies all database objects exactly as they are at any given moment in time.

Transactional replication copies changes to the database as they happen in real-time.

Merge replication first completes a snapshot replication and then follows it with transactional replication as data changes.

SQL Server allows you to easily configure it to perform each of the three SQL replication methods over the internet. This allows for you to be able to backup your data online just incase you ever need to restore it. The minimum acceptable backup would be a snapshot replication each night.

One of the most important things for your business success is its electronic data. The data your company generates each day has already cost your company both time and money. Trying to recreate data is not what you, as a business owner, ever want to have to do.

As long as you plan for the unexpected and backup your data regularly, you will not have to ever go back and try and recreate your data from scratch.

You can never be too prepared when disaster threatens your business. Protect your data with backup solutions such as SQL replication from Global Data Vault.

SQL Best Practices

Are you looking for SQL best practises, hints and tips to assist you in optimising your queries? A fast SQL Server is something that should be taken for granted, but, the vast majority of systems do not perform well.

The reasons for this can vary, and include database design, hardware bottlenecks, poor coding, table size, poor indexing, an overloaded Server, inefficient configuration, insufficient space, and many more.

If you search the web, you will find articles, forums, expert advice, hints and tips that may assist you in your optimisation and troubleshooting efforts. It can also help to prevent mistakes from being made in the first place.

You will find Transact-SQL optimisation hints and tips to assist you in ensuring that your code is optimised on the web. You will also find articles, FAQs, scripts, and tips on table design, indexes, cursors and stored procedures.

Visit this source for SQL best practises. Reduce backup times by 50%, reduce storage requirements by 90%, automate backup management across the enterprise, and perform backups across servers at the same time.

PL/SQL is the Oracle's extension to SQL with design features of programming languages. The Oracle server pre compiles PL/SQL code prior to the actual code execution, thus improving the database's performance.

Oracle, like SQL Server, has a wide range of built in functions that you can utilise when manipulating data, such as finding today's date, determining the length of a field and converting a field from one format to another.

This site is a resource for Oracle database developers. You will find a collection of articles, tutorials, and utilities. Most of the content on the site is original, and the contributions of others is referenced and credited.

It is important to follow SQL best practises to ensure that you optimise your queries and improve the database's performance. The most important thing to consider when creating tables, is to index tables efficiently.

Have you ever been stuck looking for answers to your Oracle questions? If so, then this site is for you. We've got loads of tips, tricks, examples, short cuts and tutorials to help you learn Oracle and expand your knowledge.

Tech on the Net is dedicated to providing helpful advice, how-to's and FAQs. The focus is on technologies such as Microsoft Access, Microsoft Excel, Microsoft Word, MS SQL, Oracle PL/SQL, UNIX, and Linux.

If there is still a question you couldn't find an answer for, why not ask the Oracle experts? This is the place to get your toughest strategic Oracle questions answered by some of the top gurus in the Oracle community.

Oracle is the world's largest enterprise software company. Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and much more.

Visit the official public Oracle Wiki, where members of the Oracle community (employees and non-employees) collaboratively create and share content about Oracle-related subjects they're passionate about.

If you have specific questions about Oracle products and associated technology, however, forums.oracle.com is still your best resource in your quest to learn the language and to follow PL/SQL best practises.

SQL Server Database Security

A few years back, a deadly virus called the Slammer worm attacked a lot of SQL severs gaining access to millions of credit cards. Microsoft has been on the guard since then making efforts to create and develop software in better protection and security of our systems. The effort is bold and continuing in being dedicated to enhancing and preserving SQL server database security.

SQL means Structured Query Language is actually an essential element of modern database systems. It is a definition by which the ways to create, organize and manage databases on most platforms. SQL might sound complicated at first but its purpose is really quite simple but since it involves important data which needs to be private and protected, there are great efforts made to ensure better SQL server database security.

SQL has many different forms as well. The databases which make Oracle as foundation make use of PL/SQL while Microsoft SQL utilizes Transact-SQL. Though they are different in some ways, they are still based on the standard ANSI SQL. The ANSI compliant SQL commands usually works on almost all database systems of today and as with other data that needs protection, SQL server database security must be incorporated for safety against probable intrusions.

Communications and exchange of data from one computer utilizes and is made possible by the SQL system. Negligence to provide adequate protection has been proven to result in destructive outcomes like the virus earlier said which hit the world a few years back. Now at present date, companies from all over are giving more attention to their SQL server database security.

The havoc that spread and caused severe damage before serves as a reminder of what can happen when a system becomes vulnerable to intrusions. A little know how on SQL might come in handy. It is also best to seek professional consultation about the SQL server database security of the system you are running for safety of your data and investments.

How To Use Structured Query Language (SQL)

Structured Query Language (SQL) is a computer language for creating database and manipulating data. SQL is an ANSI (American National Standard Institute) standard and is supported by almost all Relational Data Base Management Systems (RDBMS) like Oracle, MySQL, SQLServer, MS Access, PostGreSQL etc. SQL has two parts:

* Data Definition Language (DDL): to create, alter, or drop tables and indexes.
* Data Manipulation Language (DML): to insert, update, retrieve or delete the data in the tables.

Here's how to use SQL.

1.Install an RDBMS package. You can download MySQL from http://www.mysql.org for your operating system (OS) and install it using the given instructions. For Windows OS, it can be installed by double-clicking the installer and choosing the default values on each stage.

2.Start Mysql service. In command prompt window, change directory to C:\mysql\bin (if you have installed MySQL under C:) and issue following command to start MySQL service:NET START mysql

3.Start Mysql client. In command prompt window, change directory to C:\mysql\bin and issue the command mysql to get mysql prompt.
4.Create a database. On MySQL prompt, enter command 'create database' followed by any database name. Remember to put semi-colon at the end of command:

create database emp;

5.Set the created database as active one. To do this, issue the 'USE' command followed by database name on mysql prompt:

use emp;

6.Create a table. To do this, use 'CREATE TABLE' command with name and data-type of each table field. You can also specify PRIMARY KEY and any other constraint like NOT NULL. For example:

CREATE TABLE person
(NAME VARCHAR(80) PRIMARY NOT NULL,
DSGN VARCHAR(5),
AGE INTEGER,
PAY INTEGER
);

7.Insert some data into the created table. This is accomplished through 'INSERT INTO' command followed by table name and values to be inserted.

* If you need to insert a row with values for all columns, then use the following command:



INSERT INTO person VALUES ('Kakul','MGR',26,35000);

* If you need to insert values for selected columns only, then you need to specify those column names also in the command as shown:

INSERT INTO person (NAME) VALUES ('Feroz');

Notice that a character value is enclosed within single quotes and each command is terminated with a semi-colon.

8.Update the table.

* Use UPDATE command with SET and name-value pairs like:



UPDATE person SET pay=50000;

* For updating a particular row, use WHERE clause in UPDATE command like:

UPDATE person SET pay = 20000 WHERE NAME = 'Kakul';

9.Retrieve the stored data. Use SELECT command to retrieve the data. For conditional retrieval, you may use WHERE clause. Try following queries:

* To retrieve all COLUMNS and all rows:



SELECT * FROM person;
* To get the sorted list, use ORDER BY clause:

SELECT * FROM person ORDER BY name;
* To retrieve few columns of all rows:

SELECT name FROM person;
* To retrieve all columns of a particular row:

SELECT * FROM person where name = 'Feroz';
* To retrieve selected columns of a particular row:

SELECT pay FROM person WHERE name = 'Kakul';
* To retrieve a row with columns having a particular pattern (i.e. pay of all those employees whose name starts with K):

SELECT pay FROM person WHERE name like 'K%';
* To count number of records in the table (say you want to know number of employees):

SELECT COUNT(*) FROM person;
* To get the sum of a column (say you need to know total pay to be paid):

SELECT SUM(PAY) FROM person;
* Use AND/OR in WHERE clause to retrieve data based on multiple condition:

SELECT * FROM person WHERE name LIKE 'K%' AND pay > 5000;
* To group the results, use GROUP BY as in following:

SELECT * FROM person GROUP BY dsgn;
* To show groups satisfying a criteria, use HAVING as illustrated below:

SELECT * FROM person GROUP BY dsgn HAVING pay > 12000;
* To get results if a field has any of the given value, use IN clause:

SELECT * FROM person where name IN ('Feroz','Kakul');

You may try querying with other functions also like AVG, DISTINCT, BETWEEN etc.

10.Add a column to the table. This is done through ALTER command like:

ALTER TABLE person ADD experience INTEGER;

11.Set an alias for person table using few columns only. To do this, use AS as illustrated below:

SELECT NAME,DSGN FROM person AS employees;

12.Delete records from the table.

* To delete a particular record, use DELETE command with WHERE clause like:



DELETE * FROM person WHERE name='Feroz';

* To delete all records, use:

DELETE FROM person;



All records can also be deleted using TRUNCATE command such as:

TRUNCATE TABLE person;

13.Drop the column added in step 10 above. You have to again use ALTER command with DROP like this:

ALTER TABLE person DROP experience;

Note that with ADD you have to specify data-type of the column also which is obviously not required with DROP.

14.Drop the created table. Use DROP TABLE command followed by table name.

DROP TABLE person;

15.Drop database also. Use DROP DATABASE command followed by database name.

DROP DATABASE emp;

16.Try advance SQL topics like creating views, stored procedures, cursors, join etc.



www.howtodothings.com

Directories

Blogs Directory















Blog Directory





Blog-Search.com













Wil's Domain Weblog












border="0"/>

Link Exchange

1.Link2Me SEO Link Exchange Directory
Quality directory of webmasters actively seeking link exchange. Improve your search engine rankings and link popularity the easy way. Work clever not hard.
2.Link Market - Free Link Exchange, Link Swap and Link Trade Directory
Have you ever tried to exchange links, swap links, or trade links? Was it hard? Use link market instead; - it is easy to use, free and very smart. It will save you hours of work.
3.The Link Exchange - Your ultimate resource for link exchange!