Backup, Restoring and Disaster Recovery

Hi All,

Here is an excellent piece of information on Backup,Restoration and Disaster Recovery with the suggestion and related source of information.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

Happy reading.

Read More

How to create partitioned table and index in SQL Server 2005

I have found good article on how to create partitioned table and index in SQL Server 2005 here is the link of the article http://msdn.microsoft.com/en-us/library/ms345146.aspx

hope this would helps

Read More

What is Stored Procedure and How to write Stored Procedure

Stored Procedure (SP, here after I will refer it as SP) is as its name indicates stored in a database . SP contains one or more then single T-SQL in it, and it is re-usable. The beauty or advantage of SP is, it will get complied and stored in Database, now when ever you required to run the T-SQL or bunch of T-SQL you don't need to write it and compile it again, just call that Stored Procedure(SP).
Whereas in case of individual T-SQL it will get complied every time you run them; so it helps to reduce your time writing T-SQL, it saves CPU and I/O times it would take to compile and to create execution plan.

There are some advantages of using SPs, they are:
1. You do not need to rewrite your T-SQL which you would required to run often
2. It will get complied and stored in DB, and this will helps you saving your CPU & I/O cycle. * In case if some modification is done in SP you would required to re-compile it.
3. Can accept parameter as input value and will return output based on it
4. You can call one SP in another
5. You can have security configured for them, i.e. can grant or deny exeecute permission for particular user/role/group
6. SP which has parameters can help you protect from SQL Injection Attack
7. SP will help you reduce your network round-trip and hence network traffic

Here are some basic instruction and input on how to write SP(s).
http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx http://www.sql-server-performance.com/articles/dev/create_stored_procedures_tsql_p1.aspx

Read More

should I shrink my database

I have come across to the post "should I shrink my database" many times, this is certainly not recommended unless it is required. When we have ample space on the hard drive let it be in the size it is.

Because what happens is when you shrink it and as time passes by it would required to grow again and this time it again involves your CPU and I/O cycle which would be costly affair; again, herealso there may be counter arguments too but this is what certainly not recommended on other hand it *depneds* on case to case bases.

What we should really do is to investigate the reasons, why it has grown that big (if you feel so) for example
1. Auto growth option,
2. Activities like BCP/Bulk insert,
3. Reindex,
4. defregment of database

Please refer Tibor Karsazi's article here on the same topic http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Hope this would helps.

Read More

New KB Articles on SQL Server 2005 and SQL Server 2008

Here are link of new KB Articles released on SQL Server 2005 and SQL Server 2008
List of Builds that are released after SQL Server 2005 SP2
http://support.microsoft.com/kb/937137/
List of Builds that are released after SQL Server 2008
http://support.microsoft.com/kb/956909/

FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order http://support.microsoft.com/kb/926292

Distribution Agent do not skip error 20598 SQL Server 2008 http://support.microsoft.com/kb/956376

The syntax for '' is incorrect in SQL Server 2005 Analysis Services http://support.microsoft.com/kb/956039

Read More

SQL Server 2008 - download November CTP now!!!

SQL Server 2008 November CTP is available for download, click here to grab your copy of SQL Server 2008

Here are some more resources on SQL Server 2008

SQL Server 20008 Product Overview

SQL Server 2008 Videos

SQL Server 2008 White Papers

SQL Server 2008 Webcasts

Happy Learning all :-)

Read More

MVP Again - New year gift from Microsoft

I got my MVP award renewal for the 2nd year, this has been a wonderful gift on New Year.

Read More

Learn SQL Server 2008 from Microsoft

Since some times their is a discussion on what's new in SQL Server 2008, when the reference material will be available!! Here is an answer to this, Microsoft has introduce a course at its E-Learning, the course aims to educate IT Professional who has prior experience working with SQL Server. Microsoft has made learning SQL Server 2008 easy by offering e-learning course, the course is devided into three category:

Enterprise Data Plateform

Business Intellegence

Database Developement

more details on this course is available here, all you need to have is a Windows Live Account https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=139087

Read More

Some Tips for Merge Replication

* SQLAgent service should run under same domain account, which has local admin privilege and “Log in as a service” permission. This requires on both the server. In case, if SQL Server is not part of any Domain. We can use the user account which has identical name and password at servers i.e. ReplUser on SRVA with password ReplUser! and identically ReplUser on SRVB with password ReplUser!

* User account that is used to run the SQLAgent services have to have sysadmin privilege on both server, and it should be added in to db_owner fixed db role for database which is being replicated/replicating.

* Should include all the tables which are referenced, this will help to keep data consistency.

* If there are text/image column we need to update those column explicitly Updatetext statement. http://msdn2.microsoft.com/en-us/library/aa260658(SQL.80).aspx

* Try to avoid using Identity column as PK but if it is already implemented modify the property of Identity column as ‘Not for Replication’.

* Define both server (Publisher and Subscriber) as remote server at each other.

* Allocate adequate space for distribution, subscription and publication db.

* Avoid using Identity column as PK but if it is already implemented modify the property of Identity column as ‘Not for Replication’.

* Rowguid column is must, which also can be added at the time of configuring Merge replication.

* sp_dropmergearticle can be used to drop article from publication.

* sp_addmergearticle can be used to add article to publication.

Below are some links that will be usefull for replication troubleshooting, they are:

Troubleshooting Merge Replication http://support.microsoft.com/kb/315521 and

http://msdn2.microsoft.com/en-us/library/aa237486(SQL.80).aspx

Managing Identity values in Replication environment http://msdn2.microsoft.com/en-us/library/aa237098(SQL.80).aspx

Violation of Primary Key (PK) Constraint http://support.microsoft.com/kb/813494

Merge agent incorrectly deletes rows after PK violation error

http://support.microsoft.com/kb/326483

Adding and Droping an article in existing publication http://msdn2.microsoft.com/en-us/library/ms152493.aspx

Working with BLOB data in Replciation http://technet.microsoft.com/en-us/library/ms151206.aspx

Read More

Estimating the size of Table

Many time it happens we were wrong with the Estimation of Table Size, wrong estimation of Row Size can trouble us with the growth of the database and ultimately we are ending up with the space crunch, and all this because of wrong Estimation of Table Size. So what we should consider the most with Estimating Table Sizing!!! Nullbitmap is the most important criteria here to take into consideration whenever you do a Estimation of Row Size, here is an excellent article on Row Size Estimation http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx and http://msdn.microsoft.com/en-us/library/ms175991.aspx

Read More

Tuning Stored Procedure

Most of the time I come accross the thread wherein user has requested to get started with stored procedure performance tuning , so here are some good reference material for tuning stored procedure and optimize stored procedure for performance
http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx, http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/performancetuningstoredprocedures/2505/ and http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1247499,00.html

Read More

Automate Profiler Trace

The question I recently come accorss on SQL Server Performance, the OP was asking on "How to automate profiler trace" , their are system stroed procedure that can be used to automate profiler trace. The system stored procedure you can use are sp_trace_create,sp_trace_generateevent,sp_trace_setevent,sp_trace_setfilter and sp_trace_setstatus. Here is complete procedure described on "How to automate profiler trace" , refer http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

Here is a link on "How to monitor trace on SQL Server 2005" http://support.microsoft.com/kb/912914/en-us

Read More

New KB articles are added for SQL Server 2005

New Knowledge Base articles are added for SQL Server 2005, Here are the links

FIX: The performance of a query that performs an insert operation or an update operation is much slower in SQL Server 2005 SP2 than in earlier versions of SQL Server 2005

FIX: You receive an incorrect result when you use a SQL Native Client provider to read the data on the client computer in SQL Server 2005

How to enable the SQL Server 2005 Integration Services process to generate a dump file when the process experiences exceptions

FIX: Error message when you run a query that selects many columns and that joins many tables in SQL Server 2005 Service Pack 2: "The query processor could not produce a query plan"

FIX: An application that is included in SQL Server 2005 may stop responding when you specify a network protocol that is not valid in the application

FIX: Error message when you use Database Engine Tuning Advisor to tune a database in SQL Server 2005 Service Pack 2: "An unhandled win32 exception occurred in dtaengine90.exe [4308]"


FIX: Error message when you use the Bcp.exe utility together with the queryout option in Microsoft SQL Server 2005: “BCP host-files must contain at least one column”

Some or all SQL Server 2005 services are not listed in SQL Server Configuration Manager, or you receive a "No SQL Server 2005 components were found" error message when you perform operations in SQL Server 2005 Surface Area Configuration

Error message when you run a distributed query against a loopback linked server in SQL Server 2005: "Transaction context in use by another session" or "MS DTC has cancelled the distributed transaction"

You cannot upgrade the named instance of SQL Server 2000 Desktop Engine Service Pack 3a that is installed together with Application Center 2000 Service Pack 2 to SQL Server 2000 SP4

Read More

Renaming SQL Server that hosts Reporting Server

Sometimes business demands or change in naming convention for servers lead to situation where one should change / rename the Server running MS SQL Server, it would not that panic renaming SQL Server as you have sp_dropserver and sp_addserver to help you, but what if this SQL Server box have reporting server too; well microsoft has workaround for it refer - http://technet.microsoft.com/en-us/library/ms345235.aspx

Read More

Devcon2007 - A great Success

Hi All,

I am too glad... DevCon 2007 organized pune has great success, over 1100 attendes was recorded for this event. I thought this would the highest number of audiance till date , it includs IT professionals and students.
Special thanks to all energetic members and volunteers of Pune User Group members and MSP(s) who has played key role in this success.

Read More