Wednesday, January 5, 2011
Tuesday, December 14, 2010
Garbage Collector
Overview:
This article gives the brief introduction of Garbage Collector, Generations in the heap memory, Phases of Garbage collection, destructor and the benefits of Garbage collector.
Garbage Collector:
Garbage Collector was invented by John McCarthy around 1959.
Garbage Collector in .NET Framework manages the allocation and releases the memory for the application.
To the new objects created, CLR (Common language runtime) allocates space in the managed heap as long as the space is available in the managed heap. But the size of heap is limited and all allocations done on heap must be de allocated. So, based upon the allocations being made, garbage collector gets activated to release some memory.
Before reclaiming the memory, garbage collector checks whether the objects in the heap are unreferenced. Only when the objects are no longer being used, it performs its operations.
In the above figure as Data 2 is unreferenced, it is ready for garbage collection.
Except the thread that triggered the garbage collection, all the managed threads are suspended before a garbage collection starts.
Garbage Collection:
To optimize the work of garbage collector, Microsoft has divided the heap memory into three equal parts called Generations. They are GEN0, GEN1, GEN2.This is compact garbage collection. New objects are always created in GEN0 and are promoted to other generations based on their lifespan. Long lived objects would be destroyed last as these objects would move from GEN0 to GEN1 and from GEN1 to GEN2.
In .Net the address of object can change at runtime as it goes from one generation to another.
The object once created are never guaranteed to be destroyed because they might get promoted in higher generations and garbage collector would not visit those generations as memory is mostly available in lower generations itself.
Conditions for a Garbage Collection:
Only when one of the following conditions is true, garbage collection occurs:
· If the physical memory of system is low.
· On the managed heap, if the threshold of acceptable memory usage exceeded.
· If the GC.Collect () is called. This method is rarely used because the garbage collector runs continuously.
The Garbage collector can be forced at a certain point in the code by calling System.GC.Collect (). System .GC class is a class which represents the garbage collector, and Collect () method initiates a garbage collection.
Garbage Collection Phases:
Every application in .net has set of roots known as Application roots. The storage locations are being identified by these roots, which refer to objects on the managed heap or to the unreferenced objects.
The Phases in Garbage collection are:
· Marking Phase: Find and creates list of all live objects or application roots.
· Relocating Phase: Updates the references to the objects that will be compacted.
· Compact Phase: Reclaims the space occupied by the dead objects and compacts the surviving objects.
Destructor:
Destructor of a class in .Net is mainly used for destroying COM objects which are created by it in unmanaged heap. Unmanaged heap is not managed by garbage collector.
A destructor is used to release the resources that an object is holding, when an object is ready to be destroyed Finalize method / Destructor is called on that object.
Because it’s not predictable when the Destructor method is going to be executed, it’s not recommended to only rely on destructor for releasing of resources and thus Microsoft has suggested two methods that can be invoked by the programmer for the release of resources
1. Close()
2. Dispose()
If any of these methods are invoked by the programmer before losing the reference to an object, care must be taken to avoid Finalize() method to be invoked on the same object when it is garbage collected and that can be done using GC.SuppressFinalize().
Syntax:
Public sub Dispose () or Close ()
-------Code to release resources--------
GC.SuppressFinalize (Me)
End Sub
In VB.Net only the above mentioned form of Finalize is treated as Destructor. There are multiple overloaded forms of Finalize but the other forms are not treated as destructors.
Close() or dispose() methods are custom methods and hence they have to be explicitly called just before unreferencing an object is done. Whereas destructor is automatically called when the object is destroyed by Garbage Collector.
Benefits:
· Without having to free memory, it enables to develop the application.
· In the Managed heap, objects are allocated memory efficiently.
· Clears the memory of unreferenced objects and keeps the memory available for future allocations.
· Memory safety is being provided making sure that one object cannot use the content of another object.
Summary:
Garbage collection in the Microsoft .NET common language runtime environment manages the allocation and releases the memory for application. It gets activated when the managed heap is full and there is need for release of memory. Before GC performs its action it checks whether the object is unreferenced.
Unmanaged heap is not managed by garbage collector. Destructor of a class in .Net is mainly used for destroying COM objects which are created by it in unmanaged heap.
Source: bestdotnettraining
This article gives the brief introduction of Garbage Collector, Generations in the heap memory, Phases of Garbage collection, destructor and the benefits of Garbage collector.
Garbage Collector:
Garbage Collector was invented by John McCarthy around 1959.
Garbage Collector in .NET Framework manages the allocation and releases the memory for the application.
To the new objects created, CLR (Common language runtime) allocates space in the managed heap as long as the space is available in the managed heap. But the size of heap is limited and all allocations done on heap must be de allocated. So, based upon the allocations being made, garbage collector gets activated to release some memory.
Before reclaiming the memory, garbage collector checks whether the objects in the heap are unreferenced. Only when the objects are no longer being used, it performs its operations.
In the above figure as Data 2 is unreferenced, it is ready for garbage collection.
Except the thread that triggered the garbage collection, all the managed threads are suspended before a garbage collection starts.
Garbage Collection:
To optimize the work of garbage collector, Microsoft has divided the heap memory into three equal parts called Generations. They are GEN0, GEN1, GEN2.This is compact garbage collection. New objects are always created in GEN0 and are promoted to other generations based on their lifespan. Long lived objects would be destroyed last as these objects would move from GEN0 to GEN1 and from GEN1 to GEN2.
In .Net the address of object can change at runtime as it goes from one generation to another.
The object once created are never guaranteed to be destroyed because they might get promoted in higher generations and garbage collector would not visit those generations as memory is mostly available in lower generations itself.
Conditions for a Garbage Collection:
Only when one of the following conditions is true, garbage collection occurs:
· If the physical memory of system is low.
· On the managed heap, if the threshold of acceptable memory usage exceeded.
· If the GC.Collect () is called. This method is rarely used because the garbage collector runs continuously.
The Garbage collector can be forced at a certain point in the code by calling System.GC.Collect (). System .GC class is a class which represents the garbage collector, and Collect () method initiates a garbage collection.
Garbage Collection Phases:
Every application in .net has set of roots known as Application roots. The storage locations are being identified by these roots, which refer to objects on the managed heap or to the unreferenced objects.
The Phases in Garbage collection are:
· Marking Phase: Find and creates list of all live objects or application roots.
· Relocating Phase: Updates the references to the objects that will be compacted.
· Compact Phase: Reclaims the space occupied by the dead objects and compacts the surviving objects.
Destructor:
Destructor of a class in .Net is mainly used for destroying COM objects which are created by it in unmanaged heap. Unmanaged heap is not managed by garbage collector.
A destructor is used to release the resources that an object is holding, when an object is ready to be destroyed Finalize method / Destructor is called on that object.
VB | C#(A destructor in C# cannot have any specific modifiers)) |
Protected Overrides Sub Finalize() End Sub | ~ {} |
Because it’s not predictable when the Destructor method is going to be executed, it’s not recommended to only rely on destructor for releasing of resources and thus Microsoft has suggested two methods that can be invoked by the programmer for the release of resources
1. Close()
2. Dispose()
If any of these methods are invoked by the programmer before losing the reference to an object, care must be taken to avoid Finalize() method to be invoked on the same object when it is garbage collected and that can be done using GC.SuppressFinalize().
Syntax:
Public sub Dispose () or Close ()
-------Code to release resources--------
GC.SuppressFinalize (Me)
End Sub
In VB.Net only the above mentioned form of Finalize is treated as Destructor. There are multiple overloaded forms of Finalize but the other forms are not treated as destructors.
Close() or dispose() methods are custom methods and hence they have to be explicitly called just before unreferencing an object is done. Whereas destructor is automatically called when the object is destroyed by Garbage Collector.
Benefits:
· Without having to free memory, it enables to develop the application.
· In the Managed heap, objects are allocated memory efficiently.
· Clears the memory of unreferenced objects and keeps the memory available for future allocations.
· Memory safety is being provided making sure that one object cannot use the content of another object.
Summary:
Garbage collection in the Microsoft .NET common language runtime environment manages the allocation and releases the memory for application. It gets activated when the managed heap is full and there is need for release of memory. Before GC performs its action it checks whether the object is unreferenced.
Unmanaged heap is not managed by garbage collector. Destructor of a class in .Net is mainly used for destroying COM objects which are created by it in unmanaged heap.
Order of Exceptions
Order of Catch blocks is very importent while implementing the Catch Blocks.For Example if my code is expected to throw DivideByZeroException , If you know the exception type properly you have to write the specifice expected exception then write generic exception. See below example
Ex:
public class Mathematics
{
public int DivisionResult(int x, int y)
{
try
{
return x / y;
}
catch (DivideByZeroException de)
{
return -0;
}
catch (Exception)
{
return -1;
}
}
}
Note1: In the above example if you change the order of Catch you will get compile time error “A previous catch clause already catches all exceptions of this are of a super type (‘System.Exception’)”. This is because of all the exceptions can be caught by the generic catchblock.
Note2: How many catch blocks can i have for a single try block . Answer is no limit.
Note3 : Can i have a try without a catch. Ans: a try should always either have a catch or finally. try will never exist alone ( try cannot be a bachelor ).
Ex:
public class Mathematics
{
public int DivisionResult(int x, int y)
{
try
{
return x / y;
}
catch (DivideByZeroException de)
{
return -0;
}
catch (Exception)
{
return -1;
}
}
}
Note1: In the above example if you change the order of Catch you will get compile time error “A previous catch clause already catches all exceptions of this are of a super type (‘System.Exception’)”. This is because of all the exceptions can be caught by the generic catchblock.
Note2: How many catch blocks can i have for a single try block . Answer is no limit.
Note3 : Can i have a try without a catch. Ans: a try should always either have a catch or finally. try will never exist alone ( try cannot be a bachelor ).
Tuesday, December 7, 2010
STATIC in C# .NET
In C#, whenever you declare a variable which is bound to an instance (object) of a class, your variable by default gets its own copy of all the fields in the class. So for example, if you write:
CreditCardAccountPremium cp1 = new CreditCardAccountPremium();
CreditCardAccountPremium cp2 = new CreditCardAccountPremium();
cp1.setFirstName("George");
cp2.setFirstName("Harry");
-- the instances cp1 and cp2 each contain their own string called FirstName. Changing the FirstName in cp2 has no effect on the value of the FirstName in cp1. However, there are some cases where you do not want this behavior. Maybe you would like to set a maximum value for a CashAdvance that is common to the entire CreditCardAccountPremium class. This would be universally applied to all instances of CreditCardAccountPremium.
We really want the "maxCashAdvance" to be stored in memory only once, no matter how many instances of the class we instantiate. To do this, we place the keyword static before the field declaration:
public class CreditCardAccountPremium
{
private static uint maxCashAdvance = 300;
private string FirstName;
private string LastName;
....
Fields declared with the static keyword are referred to as static fields or static data., while the other fields like FirstName are called instance fields. So an instance field belongs to an object, and a static field belongs to the class as a whole. Static fields exist from the moment the assembly containing the class is loaded, and are initialized by the runtime, independent of whether you actually declare any instances of the class. Consequently, we can also have static constructors, which serve no other purpose than to assign initial values to static data:
public class CreditCardAccountSuper
{
private static uint maxCashAdvance;
staticCreditCardAccountSuper()
{
maxCashAdvance = 300;
}
If you were to invoke a CreditCardAccountSuper.maxCashAdvance property, there is no need to assign an initial value within the Main() method; the static constructor does this automatically. Note that the static constructor in which the maxCashAdvance default value is set has the same name as the class, and cannot take any arguments.
Just as is the case with fields, you can declare methods as static provided they don't try to access instance data or any other instance methods. You might want to provide a method to allow users of the class to view the maxCashAdvance value:
public class CreditCardAccountPremium
{
private static uint maxCashAdvance = 300;
private string FirstName;
private string LastName;
public static uint GetMaxCashAdvance()
{
return maxCashAdvance;
}
You access static methods and fields associated with a class differently that you access them as objects (instances of a class). Instead of specifying the name of the instance to access the method (cp1.GetMaxCashAdvance) you will specify the name of the class: CreditCardAccountPremium.GetMaxCashAdvance(). The static modifier can be used with fields, methods, properties, operators, and constructors, but cannot be used with indexers, destructors, or types. C# trashes the whole concept of global functions, variables, and constants. Instead, you can create static class members, making your C# code not only easier to read, but less likely to cause naming and other conflicts.
source:eggheadcafe
More on Static Class
http://www.c-sharpcorner.com/UploadFile/4d790c/5736/
CreditCardAccountPremium cp1 = new CreditCardAccountPremium();
CreditCardAccountPremium cp2 = new CreditCardAccountPremium();
cp1.setFirstName("George");
cp2.setFirstName("Harry");
-- the instances cp1 and cp2 each contain their own string called FirstName. Changing the FirstName in cp2 has no effect on the value of the FirstName in cp1. However, there are some cases where you do not want this behavior. Maybe you would like to set a maximum value for a CashAdvance that is common to the entire CreditCardAccountPremium class. This would be universally applied to all instances of CreditCardAccountPremium.
We really want the "maxCashAdvance" to be stored in memory only once, no matter how many instances of the class we instantiate. To do this, we place the keyword static before the field declaration:
public class CreditCardAccountPremium
{
private static uint maxCashAdvance = 300;
private string FirstName;
private string LastName;
....
Fields declared with the static keyword are referred to as static fields or static data., while the other fields like FirstName are called instance fields. So an instance field belongs to an object, and a static field belongs to the class as a whole. Static fields exist from the moment the assembly containing the class is loaded, and are initialized by the runtime, independent of whether you actually declare any instances of the class. Consequently, we can also have static constructors, which serve no other purpose than to assign initial values to static data:
public class CreditCardAccountSuper
{
private static uint maxCashAdvance;
staticCreditCardAccountSuper()
{
maxCashAdvance = 300;
}
If you were to invoke a CreditCardAccountSuper.maxCashAdvance property, there is no need to assign an initial value within the Main() method; the static constructor does this automatically. Note that the static constructor in which the maxCashAdvance default value is set has the same name as the class, and cannot take any arguments.
Just as is the case with fields, you can declare methods as static provided they don't try to access instance data or any other instance methods. You might want to provide a method to allow users of the class to view the maxCashAdvance value:
public class CreditCardAccountPremium
{
private static uint maxCashAdvance = 300;
private string FirstName;
private string LastName;
public static uint GetMaxCashAdvance()
{
return maxCashAdvance;
}
You access static methods and fields associated with a class differently that you access them as objects (instances of a class). Instead of specifying the name of the instance to access the method (cp1.GetMaxCashAdvance) you will specify the name of the class: CreditCardAccountPremium.GetMaxCashAdvance(). The static modifier can be used with fields, methods, properties, operators, and constructors, but cannot be used with indexers, destructors, or types. C# trashes the whole concept of global functions, variables, and constants. Instead, you can create static class members, making your C# code not only easier to read, but less likely to cause naming and other conflicts.
source:eggheadcafe
More on Static Class
http://www.c-sharpcorner.com/UploadFile/4d790c/5736/
Tuesday, November 23, 2010
Tuesday, November 16, 2010
Insert multiple rows from gridview into database
you can use Sql bulk copy for this purpose
private void StartImport()
{
SqlBulkCopy bulkCopy = new SqlBulkCopy("Server=ServerName;Database=test;Trusted_Connection=True;",
SqlBulkCopyOptions.TableLock);
bulkCopy.DestinationTableName = "dest table Name";DataTable dt = CreateDataTableFromFile();
bulkCopy.WriteToServer(dt);
}
private DataTable CreateDataTableFromFile()
{//create a datatable and fill it with the values from the grridview
return the Datatable;
}
Friday, November 12, 2010
Difference Between Sql-server 2000 & 2005
I've been asked this question every time that there's a new version and yet I've never been able to give what I think is a nice, concise, logical answer that satisfies the asker. Probably it's a lack of my ability to easily form words in my mouth and get them out in the proper order, so I decided it might make some sense to do this on paper (metaphorically speaking) and help others out.
Like many of you, I usually get this question from someone
outside of SQL Server. A windows admin, a network guy, etc.,
someone who has little contact with SQL Server. Or maybe it's
someone who's been stuck with admin'ing a SQL Server instance.
In any case, I wanted to try and explain this concisely for the
non-DBAs. As I began this project, however I soon realized that
it's not easy to just give a good general answer. As with
everything else in SQL Server it seems that "it depends" is the
best general answer, so I broke this up into a few areas. This
part will look at the administrative differences and the next
will cover more of the development differences.
The Administrative Differences
Administering a SQL Server instance to me means making sure the
server service runs efficiently and is stable and allows clients
to access the data. The instance should keep data intact and
function according to the rules of the code implemented while
being well maintained.
Or for the non-DBAs, it means that you are the sysadmin and it
just works.
The overall differences are few. Sure we use Management Studio
instead of Enterprise Manager, but that's not really a big deal.
Really many of the changes, like being able to change connections
for a query, are superficial improvements that don't really
present a substantial change. If you think they do, you might be
in the wrong job.
Security is one area that is a very nice improvement. The
separation of the schema from the owner makes administrative
changes easier, but that is a big deal because it greatly
increases the chances you won't keep an old account active
because it's a pain to change owners on objects. There's also
more granularity and ease of administration using the schema as
another level of assigning permissions.
Another big security change is the ability to secure your web
services using certificates instead of requiring authentication
using a name and password. Add to that the capability to encrypt
data, and manage the keys, can make a big difference in the
overall security of your data. You have to carefully ensure your
application and access is properly secured, but just the
marketing value of encryption when you have credit card,
financial, or medical data is huge. SQL Server 2000 had no real
security features for data, allowing an administrator to see all
data. You could purchase a third party add-on, but it was
expensive and required staff training. Not that you don't need to
learn about SQL Server 2005, but it should be a skill that most
DBAs will learn and be able to bring to your organization over time.
High availability is becoming more and more important to all
sizes of businesses. In the past, clustering or log shipping were
your main choices, but both were expensive and required the
Enterprise Edition. This put these features out of the reach of
many companies, or at least, out of many DBAs' budgets. With SQL
Server 2005, you can now implement clustering, log shipping, or
the new Database Mirroring with the Standard edition. With the
ability of Database Mirroring to use commodity hardware, even
disparate hardware between the primary and mirror databases, this
is a very reasonable cost solution for almost any enterprise.
There are also online indexes, online restores, and fast recovery
in the Enterprise Edition that can help ensure that you take less
downtime. Fast recovery especially can be an important feature,
allowing the database to be accessed as the undo operations
start. With a log of open transactions when a database is
restarted, this can really add up to significant amounts of time.
In SQL Server 2000, you had to have a complete, intact database
before anyone could access it. With redo/undo operations
sometimes taking a significant amount of time, this could delay
the time from Windows startup to database availability by minutes.
Data sizes always grow and for most companies, performance is
always an issue on some server. With SQL Server 2000, you were
limited to using 2GB of RAM and 4 CPUs on the Standard Edition.
The number of CPUs hasn't changed, but you can now use as much
RAM as the OS allows. There also is no limit to the database
size, not that the 1,048,516 TB in SQL Server 2000. Since RAM is
usually a limiting factor in the performance of many databases,
upgrading to SQL Server 2005 could be something you can take
advantage of. SQL Server 2005 also has more options and
capabilities on the 64-bit platform than SQL Server 2000.
Why Upgrade?
This is an interesting question and one I've been asked quite a
bit over the last 18 months since SQL Server 2005 has been
released. The short answer is that if SQL Server 2000 meets your
needs, then there's no reason to upgrade. SQL Server 2000 is a
strong, stable platform that has worked well for millions of
installations. If it meets your needs, you are not running up
against the limits of the platform, and you are happy with your
system, then don't upgrade.
However, there is a caveat to this. First the support timeline
for SQL Server 2000 shows mainstream support ending next year, in
April 2008. I can't imagine that Microsoft wouldn't extend that
given the large number of installations of SQL Server 2000, but
with the next version of SQL Server likely to come out next year,
I can see this being the point at which you cannot call for
regular support. The extended support timeline continues through
2013, but that's an expensive option.
The other consideration is that with a new version coming out
next year, you might want to just start making plans to upgrade
to that version even if you're happy with SQL Server 2000. If the
plan is to release a new version every 2-3 years, you'll need to
upgrade at least every 5-6 years to maintain support options.
Be sure that in any case you are sure the application you are
upgrading, if it's a third party, is supported on SQL Server 2005.
Lastly, if you have multiple servers and are considering new
hardware for more than 1 of them, it might make some sense to be
sure to look at buying one large 64-bit server and performing
some consolidations. I might recommend that you wait for the next
version of SQL Server if you are worried about conflicts as I
have heard rumors of switches to help govern the resource usage
in Katmai (SQL Server 2008).
A quick summary of the differences:
Feature SQL Server 2000 SQL Server 2005
Security Owner = Schema, hard to remove old users at times Schema
is separate. Better granularity in easily controlling security.
Logins can be authenticated by certificates.
Encryption No options built in, expensive third party options
with proprietary skills required to implement properly.
Encryption and key management build in.
High Availability Clustering or Log Shipping require Enterprise
Edition. Expensive hardware. Clustering, Database Mirroring or
Log Shipping available in Standard Edition. Database Mirroring
can use cheap hardware.
Scalability Limited to 2GB, 4CPUs in Standard Edition. Limited
64-bit support. 4 CPU, no RAM limit in Standard Edition. More
64-bit options offer chances for consolidation.
Conclusion
These seem to be the major highlights from my perspective as an
administrator. While there are other improvements, such as the
schema changes flowing through replication, I'm not sure that
they represent compelling changes for the non-DBA.
**************************************************************************************************************************************************************************
2005sqlser support try catch which is not possible in 2000
In SQL Server 2000 , a row cannot exceed 8000 bytes in
size ... To solve this problem, Microsoft introduced the
VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types
in SQL Server 2005. These data types can hold the same
amount of data BLOBs can hold (2 GB)
**************************************************************************************************************************************************************************
--Reference:
http://stackoverflow.com/questions/198478/advantages-of-ms-
sql-server-2008-over-ms-sql-server-2005
SQL SERVER 2000:
1.Query Analyser and Enterprise manager are separate.
2.No XML datatype is used.
3.We can create maximum of 65,535 databases.
4.Nill
5.Nill
6.Nill
7.Nill
8.Nill
9.Nill
10.Nill
11.Nill
12.Nill
13.cant compress the tables and indexes.
14.Datetime datatype is used for both date and time.
15.No varchar(max) or varbinary(max) is available.
16.No table datatype is included.
17.No SSIS is included.
18.CMS is not available.
19.PBM is not available.
20.PIVOT and UNPIVOT functions are not used.
SQL SERVER 2005:
1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is introduced.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Cant encrypt
13.Can Compress tables and indexes.(Introduced in 2005 SP2)
14.Datetime is used for both date and time.
15.Varchar(max) and varbinary(max) is used.
16.No table datatype is included.
17.SSIS is started using.
18.CMS is not available.
19.PBM is not available.
20.PIVOT and UNPIVOT functions are used.
SQL SERVER 2008:
1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is used.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Can encrypt the entire database introduced in 2008.
--check it(http://technet.microsoft.com/en-
us/library/cc278098(SQL.100).aspx)
(http://www.sqlservercentral.com/articles/Administration/imp
lementing_efs/870/)
(http://www.kodyaz.com/articles/sql-server-2005-
database-encryption-step-by-step.aspx)
(http://www.sql-server-
performance.com/articles/dev/encryption_2005_1_p1.aspx)
(http://geekswithblogs.net/chrisfalter/archive/2008/05/08/en
crypt-documents-with-sql-server.aspx)
13.Can compress tables and indexes.
-http://www.mssqltips.com/tip.asp?tip=1582
14.Date and time are seperately used for date and time
datatype,geospatial and timestamp with internal timezone
is used.
15.Varchar(max) and varbinary(max) is used.
16.Table datatype introduced.
17.SSIS avails in this version.
18.Central Management Server(CMS) is Introduced.
-http://msdn.microsoft.com/en-
us/library/bb934126.aspx
-http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-
Server-2008-Central-Management-Servers-have-you-seen-
these.aspx
19.Policy based management(PBM) server is Introduced.
-http://www.mssqltips.com/tip.asp?tip=1492
-http://msdn.microsoft.com/en-
us/library/bb510667.aspx
20.PIVOT and UNPIVOT functions are used.
-http://blog.sqlauthority.com/2008/06/07/sql-server-
pivot-and-unpivot-table-examples/
SOURCE:allinterview
Like many of you, I usually get this question from someone
outside of SQL Server. A windows admin, a network guy, etc.,
someone who has little contact with SQL Server. Or maybe it's
someone who's been stuck with admin'ing a SQL Server instance.
In any case, I wanted to try and explain this concisely for the
non-DBAs. As I began this project, however I soon realized that
it's not easy to just give a good general answer. As with
everything else in SQL Server it seems that "it depends" is the
best general answer, so I broke this up into a few areas. This
part will look at the administrative differences and the next
will cover more of the development differences.
The Administrative Differences
Administering a SQL Server instance to me means making sure the
server service runs efficiently and is stable and allows clients
to access the data. The instance should keep data intact and
function according to the rules of the code implemented while
being well maintained.
Or for the non-DBAs, it means that you are the sysadmin and it
just works.
The overall differences are few. Sure we use Management Studio
instead of Enterprise Manager, but that's not really a big deal.
Really many of the changes, like being able to change connections
for a query, are superficial improvements that don't really
present a substantial change. If you think they do, you might be
in the wrong job.
Security is one area that is a very nice improvement. The
separation of the schema from the owner makes administrative
changes easier, but that is a big deal because it greatly
increases the chances you won't keep an old account active
because it's a pain to change owners on objects. There's also
more granularity and ease of administration using the schema as
another level of assigning permissions.
Another big security change is the ability to secure your web
services using certificates instead of requiring authentication
using a name and password. Add to that the capability to encrypt
data, and manage the keys, can make a big difference in the
overall security of your data. You have to carefully ensure your
application and access is properly secured, but just the
marketing value of encryption when you have credit card,
financial, or medical data is huge. SQL Server 2000 had no real
security features for data, allowing an administrator to see all
data. You could purchase a third party add-on, but it was
expensive and required staff training. Not that you don't need to
learn about SQL Server 2005, but it should be a skill that most
DBAs will learn and be able to bring to your organization over time.
High availability is becoming more and more important to all
sizes of businesses. In the past, clustering or log shipping were
your main choices, but both were expensive and required the
Enterprise Edition. This put these features out of the reach of
many companies, or at least, out of many DBAs' budgets. With SQL
Server 2005, you can now implement clustering, log shipping, or
the new Database Mirroring with the Standard edition. With the
ability of Database Mirroring to use commodity hardware, even
disparate hardware between the primary and mirror databases, this
is a very reasonable cost solution for almost any enterprise.
There are also online indexes, online restores, and fast recovery
in the Enterprise Edition that can help ensure that you take less
downtime. Fast recovery especially can be an important feature,
allowing the database to be accessed as the undo operations
start. With a log of open transactions when a database is
restarted, this can really add up to significant amounts of time.
In SQL Server 2000, you had to have a complete, intact database
before anyone could access it. With redo/undo operations
sometimes taking a significant amount of time, this could delay
the time from Windows startup to database availability by minutes.
Data sizes always grow and for most companies, performance is
always an issue on some server. With SQL Server 2000, you were
limited to using 2GB of RAM and 4 CPUs on the Standard Edition.
The number of CPUs hasn't changed, but you can now use as much
RAM as the OS allows. There also is no limit to the database
size, not that the 1,048,516 TB in SQL Server 2000. Since RAM is
usually a limiting factor in the performance of many databases,
upgrading to SQL Server 2005 could be something you can take
advantage of. SQL Server 2005 also has more options and
capabilities on the 64-bit platform than SQL Server 2000.
Why Upgrade?
This is an interesting question and one I've been asked quite a
bit over the last 18 months since SQL Server 2005 has been
released. The short answer is that if SQL Server 2000 meets your
needs, then there's no reason to upgrade. SQL Server 2000 is a
strong, stable platform that has worked well for millions of
installations. If it meets your needs, you are not running up
against the limits of the platform, and you are happy with your
system, then don't upgrade.
However, there is a caveat to this. First the support timeline
for SQL Server 2000 shows mainstream support ending next year, in
April 2008. I can't imagine that Microsoft wouldn't extend that
given the large number of installations of SQL Server 2000, but
with the next version of SQL Server likely to come out next year,
I can see this being the point at which you cannot call for
regular support. The extended support timeline continues through
2013, but that's an expensive option.
The other consideration is that with a new version coming out
next year, you might want to just start making plans to upgrade
to that version even if you're happy with SQL Server 2000. If the
plan is to release a new version every 2-3 years, you'll need to
upgrade at least every 5-6 years to maintain support options.
Be sure that in any case you are sure the application you are
upgrading, if it's a third party, is supported on SQL Server 2005.
Lastly, if you have multiple servers and are considering new
hardware for more than 1 of them, it might make some sense to be
sure to look at buying one large 64-bit server and performing
some consolidations. I might recommend that you wait for the next
version of SQL Server if you are worried about conflicts as I
have heard rumors of switches to help govern the resource usage
in Katmai (SQL Server 2008).
A quick summary of the differences:
Feature SQL Server 2000 SQL Server 2005
Security Owner = Schema, hard to remove old users at times Schema
is separate. Better granularity in easily controlling security.
Logins can be authenticated by certificates.
Encryption No options built in, expensive third party options
with proprietary skills required to implement properly.
Encryption and key management build in.
High Availability Clustering or Log Shipping require Enterprise
Edition. Expensive hardware. Clustering, Database Mirroring or
Log Shipping available in Standard Edition. Database Mirroring
can use cheap hardware.
Scalability Limited to 2GB, 4CPUs in Standard Edition. Limited
64-bit support. 4 CPU, no RAM limit in Standard Edition. More
64-bit options offer chances for consolidation.
Conclusion
These seem to be the major highlights from my perspective as an
administrator. While there are other improvements, such as the
schema changes flowing through replication, I'm not sure that
they represent compelling changes for the non-DBA.
**************************************************************************************************************************************************************************
2005sqlser support try catch which is not possible in 2000
In SQL Server 2000 , a row cannot exceed 8000 bytes in
size ... To solve this problem, Microsoft introduced the
VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types
in SQL Server 2005. These data types can hold the same
amount of data BLOBs can hold (2 GB)
**************************************************************************************************************************************************************************
--Reference:
http://stackoverflow.com/questions/198478/advantages-of-ms-
sql-server-2008-over-ms-sql-server-2005
SQL SERVER 2000:
1.Query Analyser and Enterprise manager are separate.
2.No XML datatype is used.
3.We can create maximum of 65,535 databases.
4.Nill
5.Nill
6.Nill
7.Nill
8.Nill
9.Nill
10.Nill
11.Nill
12.Nill
13.cant compress the tables and indexes.
14.Datetime datatype is used for both date and time.
15.No varchar(max) or varbinary(max) is available.
16.No table datatype is included.
17.No SSIS is included.
18.CMS is not available.
19.PBM is not available.
20.PIVOT and UNPIVOT functions are not used.
SQL SERVER 2005:
1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is introduced.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Cant encrypt
13.Can Compress tables and indexes.(Introduced in 2005 SP2)
14.Datetime is used for both date and time.
15.Varchar(max) and varbinary(max) is used.
16.No table datatype is included.
17.SSIS is started using.
18.CMS is not available.
19.PBM is not available.
20.PIVOT and UNPIVOT functions are used.
SQL SERVER 2008:
1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is used.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Can encrypt the entire database introduced in 2008.
--check it(http://technet.microsoft.com/en-
us/library/cc278098(SQL.100).aspx)
(http://www.sqlservercentral.com/articles/Administration/imp
lementing_efs/870/)
(http://www.kodyaz.com/articles/sql-server-2005-
database-encryption-step-by-step.aspx)
(http://www.sql-server-
performance.com/articles/dev/encryption_2005_1_p1.aspx)
(http://geekswithblogs.net/chrisfalter/archive/2008/05/08/en
crypt-documents-with-sql-server.aspx)
13.Can compress tables and indexes.
-http://www.mssqltips.com/tip.asp?tip=1582
14.Date and time are seperately used for date and time
datatype,geospatial and timestamp with internal timezone
is used.
15.Varchar(max) and varbinary(max) is used.
16.Table datatype introduced.
17.SSIS avails in this version.
18.Central Management Server(CMS) is Introduced.
-http://msdn.microsoft.com/en-
us/library/bb934126.aspx
-http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-
Server-2008-Central-Management-Servers-have-you-seen-
these.aspx
19.Policy based management(PBM) server is Introduced.
-http://www.mssqltips.com/tip.asp?tip=1492
-http://msdn.microsoft.com/en-
us/library/bb510667.aspx
20.PIVOT and UNPIVOT functions are used.
-http://blog.sqlauthority.com/2008/06/07/sql-server-
pivot-and-unpivot-table-examples/
SOURCE:allinterview
Subscribe to:
Posts (Atom)