Code Review Factors

 

• Misc
• Boxing – Use if ‘ref’ keyword
• Instantiation - Use if ‘new’ keyword
• Loops
• Proper Collection used
• ‘as’ operator
• ConvertToInt – Extension method
• Return statement
• Hard Code Values
• Check for nulls
• Use of Temporary variables
• Avoid Global variables

 

 

 

• Code Compliance – As followed by our earlier Architect. It’s a long list and comprised of lot of factors in brief.
• Others are as below.
• Frequent code paths
• Frequent loops.
• Boxing
• Collections
• Null Checks
• Instantiation checks
• Type Conversion
• Proper use of Resource Files
• TypeCasting
• Access Specifiers
• Proper use of Redirection
• String operations
• Avoid multiple property access calls
• Unnecessary Long Codes
• Naming convention for Resource keys
• JS vs Serverside Validations
• Proper use of DataBinding

 

 

 

Thanks & Regards,

Arun Manglick || Senior Tech Lead

Code - New Window and Session Sharing

Code - New Window and Session Sharing-

 

This is in addiotion to the Post in Dec 2008 - Link

 

Server Side	protected void lnkNewWindow_Click(object sender, EventArgs e) {             try             {                 //System.Diagnostics.Process.Start("Iexplore.exe", "http://localhost/MxWebUI/login.aspx");                 System.Diagnostics.Process.Start("Firefox.exe", "http://localhost/MxWebUI/login.aspx");             }             catch (Exception ex)             {                 Trace.Write(ex.Message);             } }   <asp:LinkButton ID="lnkNewWindow" CausesValidation="false" runat="server" OnClick="lnkNewWindow_Click" Text="<%$ Resources:LinkNewWindow %>"></asp:LinkButton>
Client Side	function OpenNewWindow() {     var path = window.location.href;     var index = path.lastIndexOf("/");     path = path.substring(0, index);     path = path + "/login.aspx";     var WshShell = new ActiveXObject("WScript.Shell");     WshShell.Run("Iexplore " + path); }     <asp:LinkButton ID="lnkNewWindow" CausesValidation="false" runat="server"  OnClientClick="Javascript:OpenNewWindow();" Text="<%$ Resources:LinkNewWindow %>"></asp:LinkButton>

 

 

Thanks & Regards,

Arun Manglick || Senior Tech Lead

 

02 - Recovery Approaches

Hi,

 

Backup & Recovery is the core of a DBA's job. The top priority of a database administrator is to have a database recovery plan for all of your SQL Server installations; not only should you have a plan, you should

be able to carry it out. This means knowing the recovery process inside and out, and testing the plan.

 

This post will not be covering Backup in detail, only appraches will be discussed.

 

 

Database Recovery Methods

 

You have three recovery methods available with SQL Server 2000.

 

-          Full

-          Bulk-Logged

-          Simple

 

 

Full Recovery

 

-                      This model allows full, differential, and transaction log backups.

-                      All operations are fully logged; this means that your transaction log can quickly grow large during bulk loading, SELECT INTO, and CREATE INDEX operations.

-                      The trade-off is that you can recover from a transaction log to a specific point in time or transaction mark (more on this later).

 

 

 

Bulk-Logged Recovery

 

-                      This model allows full, differential, and transaction log backups.

-                      The transaction log backups, however, will include minimal logging for:

 

o         Bulk processes

o         SELECT INTO

o         CREATE INDEX

o         Text/image operations

 

-                      This model is similar to SQL Server 7.0's select into/bulkcopy database option. This minimal logging will help keep your transaction log at a reasonable size, but the trade-off is inability to restore a transaction log using point-in-time recovery. Also, if the data file is damaged, minimally logged operations will need to be redone; this is because in bulk-logged mode, a transaction log backup requires access to the database data files. If SQL Server cannot reach the corrupted files, the database cannot be backed up.

 

 

Simple Recovery

 

-                      This recovery model allows for full and differential backups.

-                      It does not allow transaction log backups, unless you are issuing a TRUNCATE_ONLY backup. The SIMPLE model is equivalent to the trunc log on checkpoint database option in SQL Server 7.0 and prior versions. This model does not log:

 

o         Bulk load operations

o         SELECT INTO operation

o         CREATE INDEX

o         Text/image operations

 

-                      The advantage is that log space used is kept to a minimum. The disadvantage is you can only recover from a full or differential backup.

 

 

Understanding the Transaction Log – Recovery Process

 

-                      Before reviewing how to back up databases, let's take a quick detour and review the architecture of the transaction log.

-                      A SQL Server database requires at least one data file and one transaction log file. Even if you are using the SIMPLE recovery model, you need a transaction log, which is used to recover the database. SQL Server writes the following activity to the database transaction log:

-                       

o         The start and end of each transaction in the database. These are written sequentially.

o         Images of data before and after a modification.

o         Every extent (eight 8KB pages) allocation or de-allocation.

o         CREATE TABLE, CREATE INDEX (if full or bulk-logged recovery model), DROP TABLE, and DROP INDEX.

o         If full recovery model, SELECT INTO, bcp operations, text or image modifications.

o         If bulk-logged recovery model, limited logging of SELECT INTO, bcp operations, text or image modifications.

 

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead

01 - Backup Approaches

Hi,

 

Backup & Recovery is the core of a DBA's job. The top priority of a database administrator is to have a database recovery plan for all of your SQL Server installations; not only should you have a plan, you should

be able to carry it out. This means knowing the recovery process inside and out, and testing the plan.

 

This post will not be covering Backup in detail, only appraches will be discussed.

 

 

Database Backup Methods

 

You have five backup methods available with SQL Server 2000.

 

-          Full Backups

-          Transaction Log Backups

-          Differential Backups

-          Offline Backups

-          Snapshot Backups

 

 

Full Backups

 

-                      A full backup copies your entire database.

-                      Full backup allows you to restore your entire database from the latest full backup.

-                      This is the most time-consuming backup option. Since the database is online when it is backed up, the backup includes changes and log file entries from the point the backup began to the point that it is completed.

 

 

Transaction Log Backups

 

-                      Transaction log backups back up the transaction log from the previous full backup or transaction log backup.

-                      When the backup completes, SQL Server truncates the inactive portion of the log.

-                      Transaction log backups have low resource overhead and can be run frequently.

-                      Transaction logs are cumulative, meaning each backup is part of a sequential line of transaction log backups.

 

-                      To recover the database using transaction logs, you must first restore from the Full Database Backup and all Transaction Log Backups that have occurred from that point.

 

 

Differential Log Backups

 

-                      Differential backups copy all changed pages since the last full backup. They are:

 

o         Smaller than full database backups

o         Faster in backup time then full backups

o         Slower in total restore time (full plus differential restore)

 

-                      Differential backups work side-by-side with transaction log backups.

-                      Since the database is online when it is being backed up, the backup includes changes and log file entries from the point the backup began to its completion.

 

-                      Differential backups, unlike transaction log backups, are self-contained and only require the latest full backup from which to restore.

-                      For example, if you run a full backup at 8am, a differential backup at 10am, and an additional differential backup at 1pm, this 1pm differential backup will include all changes since 8am.

 

 

Offline Backups

 

 

-                      Offline backups take your database offline, either by shutting down the SQL Server service or detaching the database.

-                      Once the database files are no longer in use, you may:

 

o         Copy them to a remote server

o         Use a third-party backup utility

o         Back up using Window 2000's backup tool

 

-                      This should be least considered for most databases, as they do not make the database highly available.

-                      Caching for stored procedures and prepared statements also suffers, as the database has to begin from scratch when it is reattached or SQL Server is restarted.

 

 

Snapshot Backups – Or Split Mirroring

 

-                      Third-party hardware and software must be purchased; but for very large databases, this may be your best backup solution.

-                      At best, backups and restores of a very large database can be performed in seconds; this is achieved by splitting a mirrored set of disks, or producing a copy of a disk block as it is written while keeping the original.

 

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead

 

02 - Data Migration Techniques

Hi,

 

Here we'll cover– How to Move Logins

 

-          Before moving or copying databases from other SQL Server instances, you should first move over the logins that have associated users in the databases.

-          SQL Server holds logins in the syslogins table (actually this is really a view that references the sysxlogins table) in the master database.

-          There are three different options for transferring logins between SQL Server servers, as detailed below.

 

 

Reference - To move logins and passwords from SQL Server 7.0 to SQL Server 2000, see Knowledge Base article Q246133 INF: How to Transfer Logins and Passwords Between SQL Servers

 

There are different methods to achieve the same, viz.

 

1.      Transact-SQL Script

2.      DTS Package Transfer Logins Task

3.      Scripting Out Logins and Passwords

 

 

 

Transact-SQL Script

 

-          The first option is available if you have kept a Transact-SQL script of all login present in your SQL Server 7.0 server.

-          For example, the following script adds users (with passwords), along with Windows-authenticated domain users:

 

 

Script of SQL Authentication and Windows Authentication users

sp_addlogin @loginame = N'JaneDoe', @passwd = '123#$', @defdb = N'pubs', @deflanguage = N'English'   sp_addlogin @loginame = N'JackSmith', @passwd = 'MeowMix1', @defdb = N'pubs', @deflanguage = N'English'   GO sp_grantlogin @loginame = N'JOEDOMAIN\joey' GO sp_grantlogin @loginame = N'JOEDOMAIN\marcy' GO

 

-          However, most DBAs will not have a complete script of login additions, so best to go for options 2 and 3.

 

 

DTS Package Transfer Logins Task

 

-          In Enterprise Manager, expand Server Group and the registration from which you wish to transfer logins.

-          This assumes you are using the SQL Server 2000 Enterprise Manager.

-          Expand the Data Transformation Services folder and right-click Local Packages. Select New Package.

-          In DTS Designer, select Task | Transfer Logins Task.

-          Continue with the wizard to complete the task.

 

 

Note-

 

-          Using this DTS method transfers passwords but not the original SID.

-          You still must fix orphaned login\database associations.

-          sysadmin permissions are required to use this method.

 

 

Scripting Out Logins and Passwords

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead

01 - Data Migration Techniques

Hi,

 

Here we'll cover– Restore Another SQL Server's Database from Backup to SQL Server 2000

 

There are different methods to achieve the same, viz.

 

1.      Backup & Restore

2.      Detaching and Reattaching

3.      DTS Wizard

4.      Enterprise Manager SQL Scripting

5.      Copy Database Wizard

6.      BCP

7.      BULK INSERT

 

 

 

Backup & Restore

 

-          This is one of the method of migrating databases from server to server - Back up the source database and restore it on the destination server.

-          This method works for 7.0 to 2000 and 2000 to 2000 transfers.

-          You cannot restore SQL Server 6.5 databases into SQL Server 2000.

-          We can either use Enterprise Manager or Query Anlayzer to do it.

 

-          Right Click any DB || Tasks || Backup

-          Right Click any DB || Tasks || Restore

 

 

Detach & Attach

 

-          This presumes that you wish to bring the database offline in the source server and copy it to the destination server.

-          There are two methods for doing this, through Enterprise Manager and through Transact-SQL in Query Analyzer.

 

DTS Wizard

 

-          DTS Wizard allows you to import and export schemas and data from selected source and destination databases.

-          To load DTS Wizard, go to Start | Programs | Microsoft SQL Server | Import and Export Data.

-          Select Next at the DTS Import/Export Wizard dialog box.

-          Select the data source for your source data. You can reference another SQL Server data source, such as ODBC data source, Sybase, or Teradata.

-          Next we select the destination settings, choosing the server, the connection method, and the destination database.

-          Proceed further will take you to compelte the wizard.

 

 

Enterprise Manager SQL Scripting

 

 

-          If you just want to transfer the schema from an existing SQL Server 7.0 or 2000 database, you can use the SQL scripting functionality.

-          Right-click the database you wish to script in Transact-SQL and select All Tasks | Generate SQL Scripts.

-          Proceed further will take you to complete the wizard

 

 

Copy Database Wizard

 

 

-          You can use the Copy Database Wizard to copy or move databases between SQL Server 7.0 and SQL Server 2000, and between SQL Server 2000 instances.

-          Keep in mind that you cannot move or copy system databases or databases involved with replication.

-          If any of the databases are in single-user mode, Copy Database Wizard will fail.

 

-          Copy Database Wizard does not work if identical database names exist on the source and destination.

-          You must be logged in with sysadmin privileges on both servers.

-          Databases are moved sequentially and not simultaneously, even if you select multiple databases in one operation

 

-          Right Click any DB || Tasks || Copy Database

-          Proceed further will take you to compelte the wizard.

 

Use BCP

 

-          Once you have scripted out your schema and migrated it to the destination database, BCP is a great choice for extracting and loading data.

-          i.e It does not migrate DB, instead used for extracting & laoding data, one migration has already been taken place.

-          You can run BCP in a command prompt or using xp_cmdshell in Query Analyzer.

 

e.g

 

-          How to export the data from the pubs.dbo.jobs table and import into the SofaOrders.dbo.jobs table.

-          This presumes you have extracted the required table schema to the SofaOrders database already, using SQL scripting

 

 

First  BCP OUT Pubs.Jobs table	BCP "pubs.dbo.jobs" OUT C:\jobs.txt –T –SDOPEY\DOPEY –c   Press Enter to run. You should see feedback regarding the number of rows copied.
Now we have the jobs.txt data file, we can import this data into the SofaOrders.dbo.jobs table	BCP "SofaOrders.dbo.jobs" IN C:\jobs.txt –T –SDOPEY\DOPEY –c     Press Enter to run. You should see feedback regarding the number of rows copied.

 

 

-          The previous example shows a very simple BCP OUT and IN procedure; usually a production BCP OUT or IN is more complicated.

-          To handle those, required is to work with BCP format file.(Not covered here).

-          The format file gives default information used in eithera BCP IN or OUT operation, designating how data is stored and what columns each data column belongs to.

-          SQL Server includes a method for generating a format file automatically, saving you time compared with having to generate this file column by column.

 

 

BULK INSERT

 

-          BULK INSERT is like BCP, but in just one way. i.e

-          BCP does not allow to  export data from SQL Server to a text file, however it allows to copy a data file into a database table.

-          BULK INSERT performs very well as it executes within the SQL Server process, whereas BCP and DTS execute within their own processes.

 

-          If you are aiming to import data using the fastest method, you should test trade-off between the three - BULK INSERT, DTS, and BCP to decide which works best.

 

 

Import this data into the SofaOrders.dbo.jobs table, using the jobs.txt data file created ib BCP example

USE SofaOrders BULK INSERT SofaOrders.dbo.jobs FROM 'c:\jobs.txt' WITH ( FORMATFILE = 'c:\jobs_fmt.txt' )     Press F5 to execute. You should get a prompt showing the number of rows inserted.

 

 

 

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead