Agile Method

Agile development model is also a type of Incremental model. Software is developed in incremental, rapid cycles. This results in small incremental releases with each release building on previous functionality. Each release is thoroughly tested to ensure software quality is maintained. It is used for time critical applications.  Extreme Programming (XP) is currently one of the most well known agile development life cycle model.

Advantages of Agile model:

• Customer satisfaction by rapid, continuous delivery of useful software.
• People and interactions are emphasized rather than process and tools. Customers, developers and testers constantly interact with each other.
• Working software is delivered frequently (weeks rather than months).
• Face-to-face conversation is the best form of communication.
• Close daily cooperation between business people and developers.
• Continuous attention to technical excellence and good design.
• Regular adaptation to changing circumstances.
• Even late changes in requirements are welcomed

Disadvantages of Agile model:

• In case of some software deliverables, especially the large ones, it is difficult to assess the effort required at the beginning of the software development life cycle.
• There is lack of emphasis on necessary designing and documentation.
• The project can easily get taken off track if the customer representative is not clear what final outcome that they want.
• Only senior programmers are capable of taking the kind of decisions required during the development process. Hence it has no place for newbie programmers, unless combined with experienced resources.

When to use Agile model:

• When new changes are needed to be implemented. The freedom agile gives to change is very important. New changes can be implemented at very little cost because of the frequency of new increments that are produced.
• To implement a new feature the developers need to lose only the work of a few days, or even only hours, to roll back and implement it.
• Unlike the waterfall model in agile model very limited planning is required to get started with the project. Agile assumes that the end users’ needs are ever changing in a dynamic business and IT world. Changes can be discussed and features can be newly effected or removed based on feedback. This effectively gives the customer the finished system they want or need.
• Both system developers and stakeholders alike, find they also get more freedom of time and options than if the software was developed in a more rigid sequential way. Having options gives them the ability to leave important decisions until more or better data or even entire hosting programs are available; meaning the project can continue to move forward without fear of reaching a sudden standstill.

 

Table Types

       Table-Valued Parameters is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters. It helps in using complex business logic in single routine. They reduce Round Trips to the server making the performance better.

Table-valued parameters must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine. You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

User-defined table types have the following restrictions:

• A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.
• Alias types based on a user-defined table type
• The [NOT FOR REPLICATION] option is not allowed.
• CHECK constraints require a computed column to be persisted.
• The primary key on computed columns must be PERSISTED and NOT NULL.
• A nonclustered index cannot be created on a user-defined table type unless the index is the result of creating a PRIMARY KEY or UNIQUE constraint on the user-defined table type. (SQL Server enforces any UNIQUE or PRIMARY KEY constraint by using an index.)
• The user-defined table type definition cannot be modified after it is created.
• User-defined functions cannot be called within the definition of computed columns of a user-defined table type.

Persisted - Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED allows an index to be created on a computed column that is deterministic, but not precise. For more information, see Indexes on Computed Columns. Any computed columns used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. computed_column_expression must be deterministic when PERSISTED is specified.

Partitioned Tables and Indexes

SQL Server supports table and index partitioning. The data of partitioned tables and indexes is divided into units that can be spread across more than one file group in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. The table or index is treated as a single logical entity when queries or updates are performed on the data.

Partitioning large tables or indexes can have the following manageability and performance benefits.

• You can transfer or access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. For example, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes when the data is not partitioned.
• You can perform maintenance operations on one or more partitions more quickly. The operations are more efficient because they target only these data subsets, instead of the whole table. For example, you can choose to compress data in one or more partitions or rebuild one or more partitions of an index.
• You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.

When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL Server accesses one drive at a time, and this might reduce performance. To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time.

In addition, you can improve performance by enabling lock escalation at the partition level instead of a whole table. This can reduce lock contention on the table.

Partition function

A database object that defines how the rows of a table or index are mapped to a set of partitions based on the values of certain column, called a partitioning column. That is, the partition function defines the number of partitions that the table will have and how the boundaries of the partitions are defined. For example, given a table that contains sales order data, you may want to partition the table into twelve (monthly) partitions based on a datetime column such as a sales date.

Partition scheme

A database object that maps the partitions of a partition function to a set of filegroups. The primary reason for placing your partitions on separate filegroups is to make sure that you can independently perform backup operations on partitions. This is because you can perform backups on individual filegroups.

Partitioning column

The column of a table or index that a partition function uses to partition the table or index. Computed columns that participate in a partition function must be explicitly marked PERSISTED. All data types that are valid for use as index columns can be used as a partitioning column, except timestamp. The ntext, text, image, xml, varchar(max), nvarchar(max), or varbinary(max) data types cannot be specified. Also, Microsoft .NET Framework common language runtime (CLR) user-defined type and alias data type columns cannot be specified.

Switching in and switching out partitions can be very fast, but an exclusive lock— Called ‘SCH-M’, or Schema Modification lock— is required. This means you can get blocked from loading or removing data from your table. Also, all of your enabled non-clustered indexes must be “partition aligned” to switch a partition in. This means the partitioning key must be part of each of those indexes. If you need to maintain uniqueness on a set of columns that doesn’t include the partitioning key (which is often the case in OLTP environments), this can pose a problem.

Query Performance on Partitioned Tables: Partition Elimination and Beyond

SQL Server tries to identify when it can use limited parts of a partitioned table. The SQL Server query optimizer may direct a query to only a single partition, multiple partitions, or the whole table. Using fewer partitions than the entire table is called “partition elimination.”

Statistics are maintained for the entire partitioned table or index— you don’t get additional steps in your histogram for each partition. This means that the SQL Server Query optimizer may still have a very hard time knowing how much data is going to be returned by your query, and this difficulty will increase as your table grows. The result may be slow queries.

Queries will perform better when you specify the partitioning key in the criteria (aka the “where clause”). So, although partitioning is “transparent,” for existing applications, query tuning will almost always be required.

$PARTITION returns an int value between 1 and the number of partitions of the partition function. $PARTITION returns the partition number for any valid value, regardless of whether the value currently exists in a partitioned table or index that uses the partition function.

CREATE PARTITION FUNCTION RangePF ( int )

AS RANGE FOR VALUES (10, 100, 1000) ;

GO

SELECT $PARTITION.RangePF (10) ;

Surrogate Vs Natural keys

Surrogate Key:

Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table.  Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Natural Key: 

Keys are natural if the attribute it represents is used for identification independently of the database schema.  What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.

Design considerations for choosing the Primary Key:

1. It should be not null, Unique and should apply to all rows.
2. It should be minimal (i.e. less number of columns in the PK: ideally it should be 1, if using composite keys, then make sure that those are surrogates and using integer family data-types).
3. It should be stable over a period of time (should not change i.e. update to the PK columns should not happen).

Surrogate Key

I prefer surrogate keys to be DB controlled rather than being controlled via a next-up table in the schema since that is a more scalable approach.

Pros:

1. Business Logic is not in the keys.
2. Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
3. Joins are very fast.
4. No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached – very scalable.

Cons:

1. An additional index is needed.  In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).
2. Cannot be used as a search key.
3. If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.
4. Always requires a join when browsing the child table(s).

Natural Key

 Pros:

1. No additional Index.
2. Can be used as a search key.

 Cons:

1. If not chosen wisely (business meaning in the key(s)), then over a period of time additions may be required to the PK and modifications to the PK can occur.
2. If using strings, joins are a bit slower as compared to the int data-type joins, storage is more as well.  Since storage is more, less data-values get stored per index page.  Also, reading strings is a two step process in some RDBMS: one to get the actual length of the string and second to actually perform the read operation to get the value.
3. Locking contentions can arise if using application driven generation mechanism for the key.
4. Can’t enter a record until value is known since the value has some meaning.

Isolation Levels

Read Uncommitted

This is the lowest isolation level there is. Read uncommitted causes no shared locks to be requested which allows you to read data that is currently being modified in other transactions. It also allows other transactions to modify data that you are reading. As you can probably imagine this can cause some unexpected results in a variety of different ways. For example data returned by the select could be in a half way state if an update was running in another transaction causing some of your rows to come back with the updated values and some not to.

Read Committed

This is the default isolation level and means selects will only return committed data. Select statements will issue shared lock requests against data you’re querying this causes you to wait if another transaction already has an exclusive lock on that data. Once you have your shared lock any other transactions trying to modify that data will request an exclusive lock and be made to wait until your Read Committed transaction finishes.

Repeatable Read

This is similar to Read Committed but with the additional guarantee that if you issue the same select twice in a transaction you will get the same results both times. It does this by holding on to the shared locks it obtains on the records it reads until the end of the transaction. This means any transactions that try to modify these records are forced to wait for the read transaction to complete.

Serializable

This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

Snapshot

This provides the same guarantees as Serializable. So what’s the difference? Well it’s more in the way it works; using snapshot doesn’t block other queries from inserting or updating the data touched by the snapshot transaction. Instead row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

So on the plus side; you are not blocking anyone else from modifying the data whilst you run your transaction but…. You’re using extra resources on the SQL Server to hold multiple versions of your changes. To use the snapshot isolation level you need to enable it on the database by running the following command

ALTER DATABASE IsolationTests

SET ALLOW_SNAPSHOT_ISOLATION ON

If you enable SNAPSHOT isolation on a database, SQL Server starts implementing versioning technologies so that queries can set their isolation level to snapshot.  As soon as you enable SNAPSHOT isolation on a database, SQL Server waits for running transactions to complete, then immediately starts using versioning for data modifications. You start using an extra 14 bytes per row on tables in the database itself. Also, versions are created in the tempdb version store to hold the previous value of data for updates, deletes, and some inserts. This happens even if no queries are run using SNAPSHOT isolation.

·         If a transaction having read committed isolation level or snapshot isolation levels, it allows other transactions to update that table. Because, after the select statement, it will release the lock on table.

·         If a transaction having repeatable read isolation level, it does not allows other transactions to update that table. because, after the select statement, it will not release the lock on table till the transaction is completed

Log Shipping

        For distributed database application environment, it is always required to synchronize different database servers, back up, copy Transaction Logs, etc. If we are going to implement using application we have to put lots of efforts to build up the application. SQL Server 2005 provides an advanced feature called Log Shipping. Log shipping is an Automated Process for backing up, restoring, copying the transaction logs and synchronizing the database for distributed database server application which can improve the application performance and availability of database. In my recent project, I have done some short of experiment on it. I am going to explain it in this article.

Log Shipping is used to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server used SQL Server Job Agents for making those processes automatic. Log Shipping does not involve automatic transfer of server if there is any failure. This means it has just synchronized the databases but if the primary server fails, it will not redirect your application to a secondary server. This has to be done manually.

The main functions of Log Shipping are as follows:

• Backing up the transaction log of the primary database
• Copying the transaction log backup to each secondary server
• Restoring the transaction log backup on the secondary database

Log shipping

• Automated process to ship transaction log backups to another server
• Three jobs to perform the required actions: backup, copy and recover
• Involves three servers/instances: primary, secondary and (optional) monitoring server
• Requires full or bulk logged mode
• Can have multiple secondary copies
• Information about log shipping kept in MSDB

 Log Shipping Options

• Interval – Default is 15 minutes. Consider the impact before using a smaller number
• Delayed log restore – option that allows you to have an older copy, for an “oops” situation
• If secondary is far behind. Consider backup/copy to media/restore, consider partitioning the data.
• Careful – Backup typically goes to a file server share. Agent account needs access to share.
• Careful – Weekend maintenance jobs (like indexing) can make you run out of disk space
• No option to synchronize logins – Consider sp_resolve_logins to import.
• Monitoring – MSDB tables, agent history

Comparing to Mirroring

• Mirroring provides synchronous mode, with no data loss
• Mirroring provides automated failover
• Log shipping allows for multiple copies
• Log shipping allows for delay in applying logs

Components of Log Shipping

For implementing Log Shipping, we need the following components - Primary Database Server, Secondary Database Server, and Monitor Server.

• Primary Database Server: Primary Sever is the Main Database Server or SQL Server Database Engine , which is being accessed by the application. Primary Server contains the Primary Database or Master Database.
  
• Secondary Database Server: Secondary Database Server is a SQL Server Database Engine or a different Server that contains the backup of primary database. We can have multiple secondary severs based on business requirements.
  
• Monitor Server: Monitor Server is a SQL Server Database Engine which Track the Log Shipping process. 

Transaction log file growth

Transaction log expansion may result in the following situations: 

·         A very large transaction log file.

·         Transactions may fail and may start to roll back.

·         Transactions may take a long time to complete.

·         Performance issues may occur. Blocking may occur.

The following are the causes for transaction log growth,

1.    Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.

2.    Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.

3.    Running extremely large transactions like Bulk Insert, Select Into commands.

The following are the proactive measures in order to minimize the unexpected log file growth,

1.    If you do not want point in time recovery of your databases then you can change the recovery model to Simple.

2.    Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.

3.    Configure the automatic expansion of transaction log in terms of MB instead of %.

4.    Backup the transaction log regularly to delete the inactive transactions in your transaction log if you are using full or bulk logged recovery model.

5.    You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.

When your log file has grown enormously you have 2 options to curtail the growth,

1.    Take a transaction log backup which will truncate the log file and then perform shrinking of log file using DBCC SHRINKFILE statement it will be successful.

2.    You can execute the command Backup log your database name with truncate_only (or backup log your database name with no_log) and shrink the log file. But running those commands (backup log with truncate_only or no_log) will truncate the log file but will break the log chain and hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.

Hence please try to avoid the 2^nd option I mentioned above. Due to the above fact the truncate_only or no_log option is deprecated in SQL 2008.

You need to schedule a backup of the log periodically throughout the day.  I usually discuss the recovery strategy with the owner of the product and get there expectations of service level.  For internet e-commerce application databases, I run a backup to disk every 15 minutes of the logs that are immediately swept off to tape by Net backup.  For other databases that are "less critical" as defined by the owners, I backup the logs every two hours, and they are managed directly by Net backup.  Other databases are in simple recovery mode.  What happens is when you do a full backup or a log backup, the LSN's in the log get marked as safe, and can be then truncated by SQL and the space is reused.  If you aren't doing the Log backups between full backups, you aren't really benefitting from the Full Recovery Model.  At best you can hope to be able to get the tail log file from disk and use it in recovery to a point in time.  However, if your disks are the cause of a crash, you will only have the full back up on tape (hopefully) that you can recover to.