Labels

Thursday, May 29, 2008

SSL Certification

Hi,

In this post, we see how you can certify your Web Server, Web Site or Web Pages within a Web site. Here I have demonstrated at the Server Level.

Certify IIS with SSL –

It is a three step process.

· Generating a Certificate Request File

· Applying for a Server Certificate

· Installing Your Server Certificate

Generating a Certificate Request File

· Go to IIS – Default Web Site

· Properties – Directory Security Tab

· Server Certificate Button.

· This will launch the Web Server Certificagte Wizard for generating the CSR(Certificate Signing Request) file.

At the end of the wizard you’ll get something like below.

Important to note in this wizard – After few initial screens, one of the screen ask to enter ‘Common Name’. Below are the details.

Common name—

· Internet - Must be a FQDN (Fully Qualified Domain Name); for example, www.xp.com. You should not include the protocol (http://).

· Intranet – Must be a computer’s NetBIOS name. e.g xp.co.in

Applying for a Server Certificate

After generation of a Certificate Request File, required is to apply for a server certificate from a certificate authority. These three are the more popular ones:

· Verisign Inc. (http://www.verisign.com)

· Thawte Consulting (http://www.thawte.com)

· GTE CyberTrust Solutions (http://www.cybertrust.gte.com)

· Visit either of these sites and provide some identifying.

· After you provide this information, you can Submit Your CSR File (Mostly just a Copy Paste the Certificate file Content) through an online form.

· After your information is verified, you Receive An E-Mail Message that contains instructions for downloading your New Server Certificate.

Mostly, the certificate is dispalayed as content in a page. Then remaining is simply copying the contents form the IE page and pasting it into a new file with extension as .cer / .txt

Installing Your Server Certificate

· To do so, launch the Web Server Certificate Wizard once again as in 1st step.

· Choose the option labeled Process the Pending Request and Install the Certificate.

· This will prompt for the file with .cer extension saved above. You can execute the same with file having .txt extension as well.

· Choose the file and choose next, next.. and you are done.

· Now if you wish you can View the Certificate using the same– Directory Security Tab.

Using SSL in ASP.NET Pages

After the SSL is configured on the Web Server, we can request pages in both normal(http://) and secure fashion (https://).

· However, if we wish, we can force users to use SSL when requesting All Sites, Particular Site or Particular Pages in a Site.

· To do so, go to IIS and open the property sheet of either the Default Web Site, , Particular Site or Particular Pages in a Site.

· Choose the Directory Security or File Security tab. Next, click the Edit button.

· Under Secure Communications - Check Require Secure Channel.

· If you want to require 128-bit SSL, click the Encryption Settings button and choose Require 128-Bit Encryption.

Thanks & Regards,

Arun Manglick Senior Tech Lead

Don't miss to Ask before Joining

Hi,

This is my personal experience that after joining a company, there are lot of situations where I felt regret – Oh! I didn’t ask this question before joining and if I would have done that I might not have joined.

Core Department -

· Your Roles and Responsibilities – Be Cautious

· Your Technology and Version – Be Specific

· Where your Designation fits in Hierarchy

· Who are below/above to you

· Your next Designation

· Your next appraisal due.

· Abroad assignments – Any Bond

· Abroad Policies

· Notice Period

Finance Department -

· Reimbursement Details (Car, Petrol, Education) – To increase your take home

· Notice Pay recovery

· LTA – Monthly /Yearly

· PF contribution and optional/mandatory

· Form -16 requirement of the previous company (Sometimes company denies to release salaries unless you submit Form-16 of the last company. e.g WNS Gurgaon)

Admin Department -

· Office Timings (Any unusual timings for project specific)

· Causal Wear in week days

· Cab Services

· Zim / Canteen facilities

Even if you try to ask these queries, the un-reliable HR, will never give you the answers in written/mail. But be candid, and don’t rely on the verbal words and ask them to give in written. Otherwise it happens that the HR person, who might have committed you a lot, is now no more in company the time you joined.

My companies with bad Experience –

· Sapient Gurgaon

· WNS Gurgaon

· Rapidigm Pune

· Cybage Pune

  • Accenture Pune.

Never rely on HR.

Thanks & Regards,

Arun Manglick || Senior Tech Lead || +91 20 30230500 Ext: 620 | +91 9850901262

Friday, May 23, 2008

String.Empty vs String. Length: And why String.IsNullOrEmpty

String.Empty vs String. Length: And why String.IsNullOrEmpty

Comparing against String.Length == 0 is faster.

Reason:

· Length is stored and need not to be calculated.

· Numeric operations are in itself faster than String

Though it is faster, it will throw an exception in case the string is null.

Input

String.Length Results

Null

Format Exception

Empty string (“”)

0

So overall better is String.IsNullOrEmpty(someString). It safely works as below.

· On Null strings as no exception is thrown

· On Empty strings as no object object instance is created. See Post.

Reference: http://www.velocityreviews.com/forums/t101572-stringlenght-vs-stringempty.html

Thanks & Regards,

Arun Manglick || Senior Tech Lead

String.Empty vs ""

String.Empty vs "":

string str = String.Empty; // Faster

string str = “”;

The fact - String.Empty is faster than “”.

Reason - This is because an object instance is created for empty quotes leading to more assembly code on the execution stack where String.Empty is a static constant and thus does not create an object instance, it's initial value is used instead.

So if you are really looking for ultimately in memory efficiency, I suggest String.Empty.


Gotcha:

string str = String.Empty;
Console.WriteLine(str);

Now an object is not created for str since it is initialized using String.Empty.
So what happens when the compiler executes the second line( Console.WriteLine(str)). If an object is not created this statement should have thrown an error.

Reference: http://www.csharper.net/blog/string_empty_vs_empty_quotes___quot__quot___vs_string_length.aspx

Thanks & Regards,

Arun Manglick || Senior Tech

Convert.Int32 vs Int32.Parse

Conversion using ‘Convert.Int32 vs Int32.Parse’ operator

Basically the Convert class makes it easier to convert between all the base types. Ingternally, the Convert.ToInt32(String, IFormatProvider) underneath calls the Int32.Parse.

However the only difference is that if a null string is passed to Convert it returns 0, whereas Int32.Parse throws an ArgumentNullException.

Below are results with various type of inputs to Convert.Int32()

Input

Convert.Int32() Results

Null

0

Empty string (“”)

Format Exception

Alphabet

Format Exception

Numeric

Sucessfully Conversion

You can see, except null and numeric input, Convert.Int32(), throws an exception. Hence to avoid it better to check whether the conversion can be made or not. Here is the post to how to do it.

Thanks & Regards,

Arun Manglick || Senior Tech


Thursday, May 15, 2008

SQL Server - Performance Tuning

Hi,

 

Recently we have been facing the problem – Search Stored Procedures were taking more time than usual. Even some of them were taking more than 1 min to execute. We took the help of SQL Profiler to find out such statistics. After strong brain storming we found the simple solution – ReIndex all Tables

 

Here is the solution & script.

 

·          Set the compatibility mode on your database to 90.

·          ReIndex all Tables.

 

 

exec sp_dbcmptlevel 'DATABASE_NAME', 90;  -- Set the DATABASE_NAME to the target database

 

 

DECLARE @Database VARCHAR(255)

DECLARE @Table VARCHAR(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT

 

 

SET @Database = 'DATABASE_NAME'   -- Set the DATABASE_NAME to the target database

 

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName 

               FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' 

 

-- create table cursor

EXEC (@cmd)

OPEN TableCursor 

 

FETCH NEXT FROM TableCursor INTO @Table 

WHILE @@FETCH_STATUS = 0 

 

BEGIN

 

DBCC DBREINDEX(@Table,' ',90) 

FETCH NEXT FROM TableCursor INTO @Table 

 

END 

 

CLOSE TableCursor 

DEALLOCATE TableCursor

 

EXEC sp_updatestats

 

The above has reduced the time from more than 1 min to just 1 sec.

 

Happy Performance.

 

Thanks & Regards,

Arun Manglick || Senior Tech Lead || +91 20 30230500 Ext: 620 | +91 9850901262

 

SQL Server Q&A

Hi,

Below is the facts on the SQL Server, which might help from the interview perspective.

sp_help

To Display Information About a Database Object

sp_depends

To Display Information on Database Object Dependencies

sp_helpconstraint

To list all constraints defined for a specified table

sp_recompile

To recompile Procedures & Triggers

sp_helptext

Print the Text of the Stored Procedure

sp_helpextendedproc

Show the Name of the DLL Associated with an Extended Stored Procedure

sp_helpindex

Display Indexes Defined for a Table

sp_settriggerorder

To set the order in which Multiple triggers executes

Performance Impact of Constraints

· Constraints help maintain database integrity in a number of ways; however, such protections introduce performance drawbacks.

· Adding constraints can slow down the process of bulk imports, as well as other highspeed operations that need to occur against your user-defined tables.

· If you are doing bulk insert operations, you may want to consider temporarily dropping constraints (and indexes too) as a means of decreasing the load time.

Constraints vs Triggers

· If you have a choice between triggers and constraints, choose constraints if the functionality will meet your application needs.

· Constraints operate against the data before a change is made and are generally faster and less of a performance drain.

· Triggers, generally should be used for more complex business logic and data-driven decision-making.

· There are also cases where constraints cannot be used; for example, when you require referential integrity for cross-database or cross-server tables.

Unique Constraint

· You can choose multiple UNIQUE constraints for each table and are

· Allowed to set a UNIQUE constraint for columns that allow NULL values (although only one NULL value is allowed per column).

· Like primary keys, UNIQUE constraints enforce entity integrity by ensuring that rows can be uniquely identified.

Foreign Key Reference

· The referenced table column or columns must be part of a primary key or unique constraint.

· The referenced and referencing columns should also be of the same data type.

· Also, you cannot define foreign key constraints across databases or servers (for that, use triggers).

Cascading Change with ON DELETE

· The two types of cascading changes are ON DELETE and ON UPDATE.

· When ON DELETE is used, if someone attempts to delete a primary key or unique value, referencing rows in the foreign key table will also be deleted.

· Cascading updates and deletes can sometimes have a negative effect on performance; so do test this behavior before deploying it in a production environment

Use ROWGUIDCOL

· Unlike the IDENTITY column, which guarantees uniqueness within the defined table, the ROWGUIDCOL property ensures uniqueness across all databases in the world.

· This unique ID is generated by a combination of the ROWGUIDCOL property, the UNIQUEIDENTIFIER data type, and the NEWID() function

CREATE TABLE BookShipment

(unqShipmentID uniqueidentifier ROWGUIDCOL CONSTRAINT defID DEFAULT NEWID(),

vchShipmentDescription varchar(100) NULL)

Disable and Enable Constraints

ALTER TABLE <table_name>

[NOCHECK or CHECK] CONSTRAINT [ALL or <constraint_name> [,] <constraint_name_n…>]

Temporary Table

· Temporary tables are defined just like regular tables, only they are stored in the tempdb database.

· Temporary tables are often used to store temporary results, for use in queries, updates, and stored procedures.

· They can be used to return or pass a set of results from a called procedure to a calling stored procedure.

· Developers often use temporary tables to avoid Transact-SQL cursors.

· Temporary tables are defined like regular tables, and can have indexes, constraints (but not foreign key constraints), and other related elements just like regular tables.

· Temporary tables suffers due to the performance overhead that they sometimes incur, including excessive transaction logging, and locking of database objects.

· There are two different temporary table types: local and global.

Local Temporary Table

· Available for use by the current user connection that created them.

· They are defined by adding a two pound (##) signs.

· Multiple connections can create the same named temporary table for local temporary tables without encountering conflicts

· The internal representation of the local table is given a unique name so as not to conflict with other temporary tables with the same name created by other connections in the tempdb.

· Local temporary tables are dropped by using the DROP statement or are automatically removed when the user connection is closed.

Global Temporary Table

· Once a connection creates a global temporary table, any user with access to the current database can access.

· the tableThey are defined by adding a single pound (#) sign.

· You cannot create simultaneous versions of a global temporary table, as this will generate a naming conflict.

· Global temporary tables are removed if explicitly dropped by DROP TABLE or, automatically, after the last connection exits and the global temporary table is no longer referenced by any connections.

Table Variable

· Can sometimes work more efficiently than a temporary table, as SQL Server automatically clears it after it has been used; you do not need to DROP a table variable explicitly.

· Table variables can incur less locking and logging than a temporary table, because they are only available for the duration of the batch, function, or stored procedure where they are used.

· Table variables can also reduce recompilations of a stored procedure, compared to the temporary table counterparts.

DECLARE @<variable_name> TABLE (<table_definition>)

DECLARE @Authors TABLE

(iAuthorId int NOT NULL ,

vchLastName varchar (50) NULL ,

vchFirstName varchar (50) NULL)

Views

· Is a virtual representation of a table

· Used to provide de-normalized data.

· Views are also useful For Managing Security And Protecting sensitive data. If you wish to obscure the database's schema from the end user, you can grant permissions exclusively to views, rather than the underlying table.

Regular Views

· You define a regular view with a SELECT statement.

· Views are also updateable if they meet the following conditions:

o They have no aggregate functions or the TOP, GROUP BY, UNION, or DISTINCT clauses in their definition.

o They don't contain computed values or values based on functions.

o Any non-nullable columns on the base table must be included in the view definition.

· DELETE operations are only allowed if the view references one table in the FROM clause that defines the view.

CREATE VIEW <view_name>

{WITH ENCRYPTION | WITH SCHEMABINDING | WITH VIEW_METADATA }

AS

<select_statement>

{WITH CHECK OPTION }

GO

Note:

WITH CHECK OPTION

Used to make sure any INSERT activity in the view does not include a value that cannot be read from that view.

WITH ENCRYPTION

To not to allow your view definition to be viewable

Indexed Views

Note: Indexed Views is diferent than Clustered Index on the Table. Reason - The underlying (base) tables are not impacted physically by the creation of indexed views, as the view is treated like a separate database object.

· A view is no more efficient than the underlying SELECT query used to define it, unless an index is added to it.

· You must first create a unique clustered index on the view. Once this view has been built, the data that makes up the view is stored in much the same way as a clustered index for a table is stored.

· You can also create additional nonclustered indexes, as you would for a regular table. Prior to creating nonclustered indexes on the view, you must first define the clustered index.

· An indexed view are used implicitly even if the table name is used in the from clause, in place of View name.

· Are more useful for static data (reporting or data warehousing data), than data that is frequently updated; this is because base tables with frequent updates will trigger frequent index updates against the view, potentially reducing the benefit of query performance against data update speed.

Stored Procedures

· Produce faster execution times than ad hoc queries

· The key to a stored procedure's good performance is SQL Server's ability to store a Pre-Compiled Execution Plan for commands used within the stored procedure

· Increased Network performance - As the Transact-SQL statements from the stored procedure are invoked on the server itself, reducing Transact-SQL calls over the network. This leaves only the stored procedure call and parameters and returned results (when applicable) to be transferred over the network.

· Force a Stored Procedure to Recompile - When table data referenced from within a stored procedure undergoes significant changes, or new indexes are added to the referenced tables.

Extended Stored Procedures

· Allows to extend the functionality of SQL Server by referencing functionality from dynamic link libraries (DLLs).

· Extended stored procedures are defined by referencing a DLL file and are not defined with Transact-SQL statements like a regular stored procedure.

· Because a DLL is loaded and used from within SQL Server, there is an increased risk of memory leaks or performance issues (depending on the DLL). If a DLL misbehaves, SQL Server can crash.

sp_addextendedproc @functname = <'procedurename'>,

@dllname = <'dll_name'>

User-Defined Functions

· Were introduced in SQL Server 2000.

· Types of UDFs

o Scalar UDFs –

§ Return one value, and cannot return table data types.

o Inline UDFs

§ Return a table data type, and do not explicitly define the returned table, but rather use a single SELECT statement for defining the returned rows and columns

o Multistatement UDFs

§

Stored Procedures

User-Defined Functions

Accept parameters

Same

Can contain output parameters

Cannot contain output parameters

Optional

Must always return a scalar value or a table

No

Can be used within your queries (Select, Where & From) and DML statements.

Used as a substitute for a sub-query.

A function call can also be joined with other tables.

Indexes

· Indexes assist with query processing by speeding up data access against tables and views.

· Indexes are defined by selecting an index key, which is made up of one or more columns from the table or view.

· If indexes do not exist for a table, that table is called a Heap; meaning the data pages are stored in no particular order. A heap referenced in queries results in a table scan. A table scan occurs when all rows in the table are evaluated against the required end-result (instead of just a subset of the data).

· Two types of indexes, Clustered And Nonclustered.

· SQL Server uses a B-Tree data structure for each clustered and nonclustered index.

· When an index is used, SQL Server navigates from the Btree structure's root and traverses down to the leaf nodes.

Clustered Indexes

· Determine the physical order of data in a table.

· You can only have one clustered index for each table, as the data pages can only be physically stored in one way

· After a clustered index is created, data pages are physically contiguous, which can assist with the speed of queries.

· For a table with a clustered index, the leaf nodes are the data pages themselves.

· Good candidates - Columns that are

o Queried often in range queries (Between, <, >)

o Order large result sets,

o Used in aggregate functions, and

o Whose contents are distinct (primary or unique keys)

o Lastly, Choose a Smaller Clustered Index Key over a wider column, reducing the size of the index needed and improving I/O operations.

· Bad candidates - Columns that are

o Frequently updated columns and

o Non-unique columns

Non-Clustered Indexes

· It stores index pages separately from the physical data, with pointers to the physical data located in the index pages and nodes. In a way it contains poitner to the Clustered Index leaf pages

· Nonclustered index columns are stored in the order of the index key column values.

· You can have up to 249 nonclustered indexes on each table or indexed view.

· For nonclustered indexes, the leaf node level is the Index Key Coupled To A Bookmark.

· The nonclustered index bookmark points to the B-Tree structure of the table's clustered index (if one exists). If the base able is a heap, the nonclustered index bookmark points to the table row's Row-ID.

· Good candidates - Columns that are

o Used in Where clause

o Order smaller result sets on unique data

o involved in searchable arguments using operators such as =, <, >, IN, EXISTS, and LIKE.

To Index or not to Index?

· Indexes take up space, and should not be added 'just in case'. View Tools for testing the effectiveness of your indexes.

· Indexes can slow down data loads significantly, and may not make sense for tables that are used expressly for data Imports And Staging.

· If your table will not be queried and is used as an intermediate staging ground, leaving indexes off the table may be a better choice.

· The same goes for very small tables where, no matter what, the query optimizer always performs a table scan.

· Keep in mind that heaps become naturally fragmented over time and, if data updates are frequent enough, even small tables can begin to take up more space than necessary. There are those who believe that a clustered index should exist for every table in the database, period.

· One technique for reducing bulk load operations on a table is to remove the index during the load and add the index once the load is finished

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

[ WITH < index_option > [ ,...n] ]

[ ON filegroup ]

< index_option > :: =

734

{ PAD_INDEX |

FILLFACTOR = fillfactor |

IGNORE_DUP_KEY |

DROP_EXISTING |

STATISTICS_NORECOMPUTE |

SORT_IN_TEMPDB

}

Triggers

· There are two types of triggers,

o AFTER triggers - Execute after the data modification has been completed against the table

o INSTEAD OF triggers -

§ Execute instead of the original data modification.

§ INSTEAD OF triggers are allowed for both tables and views

· Multiple triggers can be defined for one table. The order in which they are fired can be configured using the sp_settriggerorder stored procedure

· The Nested Triggers server option will determine if triggers can cause other triggers to fire (cascading triggers). If enabled, triggers can cascade in a chain of up to 32 firings.

· SQL Server creates two "virtual" tables specifically for triggers, the Deleted and Inserted tables. These two tables capture before and after pictures of the modified data.

· We can Enable/Disable triggers.

Operation

Inserted Table

Deleted Table

INSERT

New Rows

-

UPDATE

New Rows

Old Rows

DELETE

-

Deleted Rows

· Points to Remember

o Triggers should be written to run quickly.

o Constraints usually run faster than a trigger

o You can only create triggers on table/viewss that you own

o If cascading referential integrity for DELETE or UPDATE is defined for the base table, you cannot define an INSTEAD OF DELETE or INSTEAD OF UPDATE on that table.

Creating an AFTER trigger

CREATE TRIGGER trg_BookAudit ON Books FOR INSERT

AS

BEGIN

UPDATE Inventory

SET iTotalBooks = iTotalBooks + (SELECT COUNT(*) FROM inserted)

END

GO

Creating an INSTEAD OF trigger

CREATE TRIGGER trg_BookAuthor_INSTEAD ON vBookAuthors INSTEAD OF INSERT

AS

INSERT Books (vchBookName, iAuthorId)

SELECT inserted.vchBookName, inserted.iAuthorId FROM inserted

INSERT Authors (iAuthorId, vchLastName, vchFirstName)

SELECT inserted.iAuthorId, inserted.vchLastName, inserted.vchFirstName

FROM inserted

GO

DELETE TABLE and TRUNCATE TABLE

DELETE TABLE

TRUNCATE TABLE

Logging

NA

No RollBack

Can be RollBack

Can have criteria

NA

Thanks & Regards,

Arun Manglick || Tech Lead