What’s new in SQL Server RC0 setup

1. Datacentre Edition is no longer available as a MS SQL Server 2012 RC0 candidate, Read More
2. MS SQL Server 2012 RC0 includes new edition – SQL Server Business Intelligence,  Read More
3. Service Pack 1 is the minimum requirement for Windows 7 and Windows Server 2008 R2 operating systems, Read more
4. Data Quality Services can be installed using SQL Server 2012 RC0 Setup, Read more
5. Product update is the new feature available with MS SQL Server 2012 RC0, this will integrate latest update with main product including MS Update, WSUS, local folder or UNC. This feature is an extension to Slipstream Functionality which was available in SQL Server 2008 PCU1. We can use command prompt or configuration file to override default values to find updates by specifying values for UpdateSource parameter, Read more
6. We can now install MS SQL Server on Windows Server 2008 R2 Core SP1, not all features are supported though, Read more
7. SQL Server Data Tools (formerly, BIDS) is now part of setup, with this we can carry out all database design work, and can build solutions for SSAS, SSRS and SSIS. Read more
8. Now, Support for SQL Server multi-subnet clustering included, Read more 
9. Databases (systems and users) can now be on UNC path, we may need to make sure that proper NTFS and File Share permissions are assigned, Read more
10. Local disk can be used for Tempdb in cluster, Read more
11. Built-in Administrator and Local system account will not be part of sysadmin role
12. Itanium editions are no longer supported

Read More

Revamping Surat User Group

Dear Friends, we had a user group meeting last week – I and Vinay Pugalia was  here. We have discussed many things to revamp and re-launch Surat User Group. Here are the minutes of meeting:

1) Regular Meetings -> Monthly, Proposed schedule :Saturday evening, 1900 to 2000

2) Regular Events -> 1 every 2 months, 1 workshop every quarter

3) Decide and form SUG Website, with Blog,Forums section, Facebook page and twitter handle

4) Press Notes after every event

5) Help from CSI Surat -> Have to meet, discuss and encourage Local IT professional to join and PARTICIPATE

6) Seminars/ Workshop :-free, TBS on weekends (Sat-Sun) to have discussion with Professional / Students

7) Different topics and technology, every time has 1 continue and 1 different topic

8) Initially we'll take session and then, we'll encourage others to take sessions and like that

9) Form Core Team & Backup Team

10) Role definition and Role assignment

11) Encourage new speaker to start with very basic topics to build their confidence

12) More Online activities etc.

13) Ask speakers to provide technology they can confidently speak

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Maintenance Plan mystery

Last week I had interesting (or I can say weird ? ) case of Maintenance Plan. One of the server that we monitor has reported job failure – this was a part of maintenance plan. Looking at the job history and log file I came to know that this job was failing because of some database which wasn’t available – this database was dropped few days back, and, the maintenance plan has it’s reference stored somewhere. I have search around system tables etc. but nothing unusual or nothing helpful was available that indicates the source of this database.
Finally, I have edited the maintenance plan which resolved this case, here is what I did:

1. Alter the maintenance plan "some maintenance plan"
2. note down the databases name
3. altered it and selected all the system databases
4. saved the maintenance plan changes and exit
5. re-open the maintenance plan
6. re-selected those databases (from step 2)
7. saved the maintenance plan changes and exit
8. re-invoked the job
9. and, the job completed successfully

And, the mystery persist, can you guys suggest me if I miss something to refer/research ?

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Using NOLOCK hint

Use NOLOCK hint to avoid block - this is what I have often heard/see in many forums I participate, during local user group events and meeting. I have always advised that its not that good idea to use hints, as it may cause data corruption and blocking. And, anyways, there are many things that you can do to avoid blocking, like:

1. use sp for everything (almost)  
2. try to avoid using cursor
3. transaction shouldn't be too big etc
4. and, use READPAST hint , I will still say, use this only when you don't have choice

But these all comes from the experience from the field and haven't anything concrete to quote as reference point until last night, I was googling something and this blog article from  Dave, on MSDN Blog showed up.  Now, I can quote SQL Server NOLOCK Hint and Other poor ideas as reference to my peers,and friends at local user group, and I am referencing it here for you to read and make a note.  


I hope this helps.


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/) 

Read More

Download SQL Server 2008 SP3 CU2

MS has recently released SQL Server 2008 Service Pack 3 Cumulative Update 2 Released‏ which has fixes reported after SQL Server 2008 SP 3, this build version is  10.00.5768.00.
Below are the bugs that have been fixed in this CU:

1. FIX: A backup operation on a SQL Server 2008 or SQL Server 2008 R2 database fails if you enable change tracking on this database
2. FIX: CLR stored procedure returns NULL after it is recompiled in SQL Server 2008 R2
3. FIX: Access violation when you insert a record into a new empty partition in SQL Server 2008

There are few more things we have to keep in consideration while applying this CU:
Pre-install:
1) You should be running on SQL Server SP3, if you haven't installed SP3 for SQL Server here is the link from where you can obtain SP3, Link => http://support.microsoft.com/kb/968382
Post-install:
* It is advisable to reboot the system once CU is applied
Please make sure you test the CU in Dev/QA/UAT environment before you apply it in the PROD environment.
Here is the link from where you can download the SQL Server 2008 SP3 CU2, link => http://support.microsoft.com/kb/2633143/en-us

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Download SQL Server 2012 Developer Training Kit

Microsoft has released SQL Server 2010 Developer Training Kit which includes :

• Labs
• Demos
• and, Presentations

This kit will greatly help you learning Developer and BI Solutions.


One will require to have Windows 7 or Windows Server 2008 R2 to install this kit. Once you have download the kit, launch the installer and it will ask you which lab, demos and presentations you want to include and install them based on your choice.


Sounds interesting? Grab it from the link


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Resolving 701 There is insufficient system memory to run this query

In recent past while working on an assignment I have encounter an error 701 There is insufficient system memory to run this query . I had a quick look at the server and noticed that server is not configured proper for max memory, and have suggested client to make changes to the max memory settings for the server. While I've suggested changes I have quote two articles, thought they would be a help to you as well to understand better on how SQL Server Memory managed. An article by SQL Server MVP Jonathan Kehayias will help you understand the memory management very well. 




1) http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx


2) http://support.microsoft.com/kb/912439

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com)

Read More

DATABASE_OBJECT_CHANGE_GROUP do not audit SP or other object

Deepak Kumar (friend of mine and founder of http://www.sqlknowledge.com )  were chatting yesterday. We were discussing about audit feature in SQL 2008, Deepak has enabled this feature for one of his client since month. And when he was looking at the log he found that there were entries but they are related to Tables only, and not other objects like SP(s). 

We were discussing and google about the same and found an entry in connect where in it was answered.  

If we need the other objects to be audited we have to add SCHEMA_OBJECT_CHANGE_GROUP to the audit specification. Here is an excerpt from the connect:

Thanks for your feedback. The behaviour you're seeing is by-design. In order to audit CREATE/DROP of an SP, you need to add the SCHEMA_OBJECT_CHANGE_GROUP to your audit specification. The DATABASE_OBJECT_CHANGE_GROUP is actually auditing the ALTER permission check on the SCHEMA as part of the CREATE statement. 

You can find the complete entry and more information at connect article  


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Refresh QA Database with Manual Scripts

Couple of week back Megha Sharma send me an email with a document attached, this document is all about how to refresh QA database with manual script. Here is the preview of the document, and the reason why we should follow the method in her own words:

QA environment frequently needs Database refresh and hence space on its disks. QA Database is refreshed from Production followed by a data purge, which leaves a lot of free space in the database. In order to release the free space, we follow database shrink command, a lengthy & single thread I / O operation, also cause high data fragmentation. To avoid this resource & time consuming activity, we do a Refresh Database with manual scripts, in which, we create a new database, transfer tables, data & other objects via scripts. The Generate Scripts option of a database generates script for the complete database and transfers data (using Insert into command, highly logged), to avoid this we do a Select * into, to transfer table definition & data (minimal logged & fast, being a bulk operation) & then generate Table Objects (Keys, Constraints, Triggers, Indexes) via manual scripts & other database objects (Views, Stored Procedures, Functions, Users, Roles, Schemas) via generate scripts.

Sounds interesting ? Want to download the complete document ? Download Link

Please post back your review in the comment section here.


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

workaround for orphaned users

The server principal  is not able to access the database  under the current security context, is the error message appears for those users who was able to access the database / application earlier especially after refreshing database from production to dev, test or uat enviornment when the correspondent login is dropped.
The reason is that, when we restore the database the the SID for the user mismatches, and it became orphaned. The workaround for this issue is very simple, you will have execute the below code

 -- This part will report if there are orphaned users    
USE 'yourdbname'    
GO    
sp_change_users_login     
    @Action='Report'    
GO    
-- This part will fix orphaned users    
USE 'yourdbname'    
GO    
sp_change_users_login     
    @Action='update_one'    
    ,@UserNamePattern='youruser'    
    ,@LoginName='yourlogin'    
GO

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

SQL 2008 R2 Administration Cook Book

Couple of week back I have received a special gift, from the person whom I admire as a elder bro and mentor – Satya SkJ. Recently he has written a cook book on SQL Server 2008 R2 Administration, forward written by MVP Brad McGehee, and reviewed by Vinod Kumar M – the most admirable person in SQL Server Community in INDIA. I am very excited to read this book thoroughly, the thing I like most in every chapter is “How it works”  section. Very soon I will post detailed review for this book.

You can find the detailed information about each chapter at the below location :
                                                  Chapter 1: Getting Started with SQL Server 2008 R2
Chapter 2: Administrating the Core Database Engine
Chapter 3: Managing the Core Database Engine
Chapter 4: Administering Core Business Intelligence Services
Chapter 5: Managing Core SQL Server 2008 R2 Technologies
Chapter 6: Improving Availability and enhancing Programmability
Chapter 7: Implementing New Manageability Features and Practices
Chapter 8: Maintenance and Monitoring
Chapter 9: Troubleshooting
Chapter 10: Learning the Tricks of the Trade
Appendix: More DBA Manageability Best Practices



Sounds interesting ? You can purchase the one for you at Link => https://www.packtpub.com/microsoft-sql-server-2008-r2-administration-cookbook/book#in_detail



-- Hemantgiri S. Goswami (http://www.sql-server-citation.com)

Read More

CU 16 for SQL Server 2008 SP1

 

Microsoft has recently released a Cumulative Update (CU) # 16 for SQL Server 2008 SP1 which contains hotfixes which were resolved after SQL Server 2008 SP1; once you will install / apply this CU the server version would be 10.00.2850.00

As always it is recommended that you test this CU in the development/test/QA environment first before you roll it out in production environment. Once you apply this CU, you might have to restart you server.

Download => Cumulative Update # 16

Find KB article which explains what is a different model for Incremental Servicing Model (ISM) like Critical On-Demand known as COD, On Demand known as OD, Cumulative Update known as CU and General Distribution Release known as GDR. Here is the reference link => Incremental Servicing Model

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

SQL Server DBA Quiz on BeyondRelational

Friend of my mine and fellow MVP Jecob Sebastian is running SQL Server DBA Quiz 2011 on his community web site – BeyondRelational.  He asked me to submit question as a Quiz Master, so, here is my question for you all :

You have an SQL Server Integration Service (SSIS)  installed on some machine and you have client tool installed on your local desktop, when you tried to connect to the machine you have SSIS service running on from your local desktop you will get an error “RPC server is unavailable”

You will have to let us know what the root cause of this issue and how to resolved it.

Know the answer already?

Click Here to Submit an Answer

-- Hemantgiri S. Goswami (http://www.sql-server –citation.com )

Read More

SQL Server Wait Types in SQL 2008

While working on an assignment of troubleshooting performance issue few month back I have come across a situation where I have to provide good information about the different waittypes and what they do, while researching a good documentation on wait types I have found the most authentic information on PSS Engineer’s blog where Bob Ward has put them nicely. This blog was published in 2009 but its good to keep handy as reference.

Most of the time wait type says us where the bottleneck are, if you wait types like IO_Completion, PAGE IO LATCH, this is indication for disk IO subsystem has some bottleneck. We have to use perfmon counter to verify this and then work around on resolving this issues.

Let me quote some links for the further reading:

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx

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

http://blogs.msdn.com/b/psssql/archive/2009/07/08/q-a-on-latches-in-the-sql-server-engine.aspx

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Overcome update conflict in P2P replication

When we have Peer to Peer (P2P) replication in place on SQL Server 2008, probably we would see a conflict

1) conflict of type 'Update-Update' was detected at peer 2 between peer 1 (incoming)

2) conflict of type 'Update-Delete’ was detected at peer 2 between peer 1 (incoming)

Microsoft has confirmed that this is known issue with P2P replication, and Microsoft has released a fix for this with Cumulative hot fix for SQL Server 2008 SP1. I am penning down some links for you as a point of reference for troubleshooting P2P conflict.

Paul Libson has wrote an excellent article on how to handle P2P replication

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

http://technet.microsoft.com/en-us/library/bb934199.aspx

http://technet.microsoft.com/en-us/library/ms147366.aspx

http://technet.microsoft.com/en-us/library/ms151865.aspx

 

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Script to Configuring Auto Growth to Fix MB

Few month ago I have written a post about what is Auto Growth and what is the best practice for Auto Growth. In this post I have discussed about why should we have Auto Growth for databases set in a fixed MB instead in percentage and how it will impact us.


In this post, I am going to give you a script that will help you find the database files which have Auto Growth option set in percentage and alter them all to fixed MB in one go.

/* 
-- Created By: Hemantgiri S. Goswami
-- Date: 29th April 2011
-- Version: 1.0

*/

-- Creating the table to capture temporary data
IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U')
	DROP TABLE ConfigAutoGrowth
GO	
CREATE TABLE DBO.ConfigAutoGrowth
(
iDBID		INT,
sDBName		SYSNAME,
vFileName	VARCHAR(max),
vGrowthOption	VARCHAR(12)
)
PRINT 'Table ConfigAutoGrowth Created'
GO
-- Inserting data into staging table
INSERT INTO DBO.ConfigAutoGrowth
SELECT 
	SD.database_id, 
	SD.name,
	SF.name,
	--sf.fileid, 
	--SUSER_NAME(owner_sid),
	--recovery_model_desc,
	CASE SF.status & 0x100000
	WHEN 1048576 THEN 'Percentage'
	WHEN 0 THEN 'MB'
	END AS 'GROWTH Option'
FROM SYS.SYSALTFILES SF
JOIN 
SYS.DATABASES SD
ON 
SD.database_id = SF.dbid
GO

-- Dynamically alters the file to set auto growth option to fixed mb 
DECLARE @name VARCHAR ( max ) -- Database Name
DECLARE @dbid INT -- DBID
DECLARE @vFileName VARCHAR ( max ) -- Logical file name
DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option
DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql


DECLARE db_cursor CURSOR FOR
SELECT 
idbid,sdbname,vfilename,vgrowthoption
FROM configautogrowth
WHERE sdbname NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' ) 
AND vGrowthOption  = 'Percentage'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption  
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)
	SET @Query  = 'ALTER DATABASE '+ @name +' MODIFY FILE (NAME = '+@vFileName+',FILEGROWTH = 500MB)'
	EXECUTE(@Query)

FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption  
END
CLOSE db_cursor -- Closing the curson
DEALLOCATE db_cursor  -- deallocating the cursor

GO
-- Querying system views to see if the changes are applied
SELECT 
SD.database_id, 
SD.name,
SF.name,
--sf.fileid, 
--SUSER_NAME(owner_sid),
--recovery_model_desc,
CASE SF.STATUS 
& 0x100000
WHEN 1048576 THEN 
'Percentage'
WHEN 0 THEN 'MB'
END AS 'Growth_Option'
FROM SYS.SYSALTFILES SF
JOIN 
SYS.DATABASES SD
ON 
SD.database_id = SF.dbid
GO

--Dropping the staging table
DROP TABLE ConfigAutoGrowth 
GO

Hope this helps!!


-- Hemantgiri S. Goswami

Read More

Troubleshooting Oracle Link Server Issue

Most of the time, due to different business requirements we do have to work on various RDBMS systems, Oracle and MS SQL Server are the widely used and popular RDBMS. Sometimes we need to import/export data from/to SQL Server and for that we’ve to use Link Server feature of MS SQL Server.

In one of my recent project, a critical application has a job that pulls in the data from an Oracle database, we have a DTS Package and some job scheduled for this task. since couple of days we’ve observed below error message in the job history and while running ad-hoc queries. I am penning down the error message and the solution (rather, temporary workaround) we have found out :

1) The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

==> In this case, rebooting MS DTS service  helped us.

2) The maximum number of active transactions that the MS DTC log file can accommodate has been exceeded.  You must increase the size of the MS DTC log file if you wish to initiate more concurrent transactions.

==> In this case, increasing Log file size helped us.

3)Server: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "OraLnkSvr" reported an error. The provider ran out of memory.

4) Server: Msg 7330, Level 16, State 1, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "OraLnkSvr".
OLE DB provider "MSDAORA" for linked server "OraLnkSvr" returned message "Out of memory.".

==> In case 3 & 4, rebooting SQL Server service helped us.

 

Hope this helps.

 

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Checking DB Mirroring Status

Often I see a question in community on how to quickly check the status of the database mirroring, sometime in busy environment and busy server launching database mirroring keep us waiting for a while, so is there a way we can check database mirroring status ?


Yes, of course we do have; execute below statement and you will have a status of the database mirroring for all the database you have configured mirroring on :

SELECT DB_NAME(database_id),   
mirroring_role_desc,    
mirroring_state_desc    
FROM sys.database_mirroring    
WHERE mirroring_guid IS NOT NULL; 

Let me know if you are looking some specific code, and I will try to post it here!!


- Hemantgiri S. Goswami (http://www.sql-server-citation)

Read More

Accessing SSIS on a Remote Server

In one of my recent assignment I was asked to help developers who were not able to work on SSIS on a remote computer, whenever they made an attempt to connect to SSIS on Remote server they were getting “Access is Denied” error. Researching bit on this I come across an article on MSDN which help me resolve this issue – this issue is all about a DCOM Permission. 
Here are the steps which helped me resolve this issue by granting necessary permission and adding developer user[s] or group for DCOM:
1) Open Component Services MMC Snap-in by Open Control Panel –> Administrative Tools –> Component Services
2) Expand Component Services node –> Expand Computers –> Expand My Computer and click DCOM Config
3) Select DCOM Config and it will display the sub list in the right pane, select MsDTSServer100

4) Right click on MsDTSServer100 and select Security tab

5) As shown in the above snapshot assign proper permission to user[s] or user group by clicking on Edit button under (you have to select Customize before you click Edit button)

a) Launch and Activation Permissions
     a.1) Local Launch
    a.2)  Remote Launch
    a.3) Local Activation
    a.4) Remote Activation
b) Access Permissions
    b.1) Local Access
   b.2) Remote Access
c) Configuration Permissions
   c.1)  Full Control
   c.2) Read
    c.3) Special Permission

6) Assign the appropriate permission on “My Computer” under Component Services
7) In MSDB database grant db_dtsoperator permission to user[s] or user group
8) Restart the Integration Services services, and you are done.
source : http://msdn.microsoft.com/en-us/library/aa337083.aspx

Read More

Winner of the contest

Dear Readers,

I am very please to announce winner of the contest about describing best HA & DR solution. The contest was successful, there are about  5 entries I have get from various  sources like linkedin and email, I would like to thank all the participant and readers. The winner is Dave Thomas.

My apologies, I suppose to declare winner on 25th April but could not able to make it.

Read More

MVP Again year 2011

Hi,
 

I have a good news to share with you all - my MVP award is renewed one more time, this is the 4th year of receiving this prestigious award (2nd time in the row) . As you all aware MVP award requires renewal every year. On the 1st of April I received from Microsoft which reads: 

Read More

Describe HA and DR strategy and win

Dear Readers,

I am very please to made an announcement about the contest.  As you aware that I have wrote a book on SQL Server 2008 High Availability which was published on 24th January 2008 2011 by Packt Publication. Now you have a chance to win a subscription worth £150 of digital library at Packt Publication and a Paper book of SQL Server 2008 HA, here is the information.

Who this book is for?

This book is written for the System Administrator, experienced SQL Developers who want to learn about the topic – SQL Server High Availability, Aspiring DBAs. That means, this book is having a step-by-step instructions, pre-requisite with plenty snapshots to get you through the installation of SQL Server High Availability options like Cluster, Replications ( Snapshot, Transactional, Peer-2-Pee and Merge), Log Shipping and Database Mirroring.

I have tried to include external reference for further study on that particular topic if you wish to read some advance information, I have also include few common issues and how to resolve them for every topic i.e. How to troubleshoot common issues for Clustering, Replication, Log Shipping and Database Mirroring.

Here is the detailed index for every chapter of this book at - http://www.sql-server-citation.com/2011/01/sql-server-2008-high-availability-book.html

Where to buy?
One can purchase my book SQL Server 2008 High Availability from Packt Publication in Paper and eBook format at https://www.packtpub.com/microsoft-sql-server-2008-high-availability/book or one can purchase this from Amazon at http://www.amazon.com/Microsoft-Server-2008-High-Availability/dp/1849681228/ref=sr_1_1?ie=UTF8&s=books&qid=1299390127&sr=8-1



Contest Information:

Describe the best HA and DR solution you have designed or worked upon, especially in case of physically dispersed location, and the reason why?

The best answer will win a  subscription of digital library worth £150 at Packt Library for 1 year and 2nd best answer will win Paper book of SQL Server 2008 HA.

The best answer will be judged by me and Satya Shyam K Jayanty (whom I admired as Guru.), we will announced the winner by 25th April 2011.

Rules:
1. Their will be two winners - 1 Subscription and 1 Paper book of SQL Server 2008 HA
2. The contest will remain open until 5th of April 2011
3. The Packt Pub Subscription will be free for 1 year

Update:
By mistake I have mentioned that my book was published on 24th January 2008, I did not noticed it until Ashish Sharma draw my attention, I have corrected it now. Thank you Ashish :)

What are you waiting for, start participating and be a lucky winner!!!



Regards
-- Hemantgiri S. Goswami | www.sql-server-citation.com

Read More

Cannot resolve the collation conflict

Recently when I was referring to SQL Server Forums I saw a question that reads like:
I get an error  Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

What is a Collation?
In simple words Collation is the set of rules that ensure proper use of characters for Language or Alphabet. Here are some recommendation from books online about choosing collation:

• Select a BINARY2 collation if binary code point based ordering is acceptable.
• Select a Windows collation for consistent comparison across data types.
• Use a new 100-level collation for better linguistic sorting support.
• If you plan to migrate a database to the upgraded instance of SQL Server, select the collation that matches your existing collation of the database.

Solution:

The error that we are referring here shown up because the two databases have different collation for some reason, now, to overcome this issue one may use COLLATE clause, but do remember that COLLATE clause will work with CHAR,VARCHAR,TEXT, NCHAR, NVARCHAR and NTEXT data type.

The Syntax :

SELECT * 
FROM 
SYS.databases SD
JOIN
SOMEDB.DBO.SOMETBL JC    
ON 
SD.database_id  = JC.DBID  COLLATE Latin1_General_CI_AI

Here are some external resource for further reading




http://msdn.microsoft.com/en-us/library/ms143508.aspx>




http://blogs.msdn.com/b/qingsongyao/archive/2008/08/25/sql-server-2008-katmai-s-new-collations.aspx

photo credit: mdanys via photopin cc

Read More

Tip for Backing up User Databases

We all take a database backup as a first resort to recover from a damage occur during the disaster / server crash / for any other reason if require to recover or restore our data back to its previous stat as it was before the database server was crashed!! And for this we schedule a job or make use of database maintenance plan which will take a database backup be it Full / Differential or Log, and we all take a special care and consideration when we design the backup strategy!!

But what happened if one fine day when you need your database backup file to work well and it doesn’t!! What if it is corrupted!! And you have only a Full backup for that day?

Today I am going to discuss about the script that I have posted in the download section couple of days back; the script will not only perform a full database backup of all user databases but it will also verify the validation of the backup file!  It's a must to have a database backup strategy where in we do not keep multiple copies of database backup  and if one have one or two days retention policy. 

declare @int int ,@dbname varchar(22),@maxdbid int
declare @int int ,@dbname varchar(22),@maxdbid int
declare @bkpath varchar(25),@path varchar(50)
select @maxdbid= max(dbid) from master..sysdatabases
set @int = 0
set @bkpath='C:\SQLDB\backup\'
USE master
while (@int < @maxdbid)
begin
set @dbname = (select [name] from master..sysdatabases where dbid=@int and status!=1073807392)  
if (@dbname != 'tempdb') 
 begin
  select @int as 'DBID',@dbname as 'Database'  
  select @path=@bkpath+@dbname+CONVERT(VARCHAR(12),GETDATE(),112)
  BACKUP DATABASE @dbname TO DISK = @path  
  print @path
  print @bkpath
  restore verifyonly from disk=@path
  end  
 set @int = @int + 1  
 continue 
 break
end

Check the line # 17, restore verifyonly from disk = @path is the line that will help us to overcome above issue. This is not a hidden code lying in a secret place somewhere but this is something that we may have missed in our code – I know most of you have already using this option for your database backup validation, how many of you are using this option? Or out of curiosity any other option!!

PS: This code is the very basic code that I have wrote initially, you may modify and use the script according to your requirement without any obligation.

Your suggestions welcome!

Regards

- Hemantgiri S. Goswami

Read More

SQL Server 2008 High Availability – A Book

I have no words to explain my excitement and happiness to share news with you all - my book on SQL Server 2008 High Availability is published!!
I have started authoring this book in May 2010 and as the time passes and gradually I move to the final chapter of the book, I was feeling like aha finally it is done!!! – Do you think so, it was this easy?
No, every publication house has their own set of rules to be followed while authoring a book and a process to be followed before the chapter or entire book is ready to be published i.e. 1^st edit, 2^nd edit of the chapter until final edit and the Packt Publication is no exception.
Since this is the first time I was authoring book I was having little or no idea about authoring a book. The editors from Packt Publication helped me a lot by helping me time to time with proper answers to my queries. I must admit that I could not be able to complete this book without support and encouragement from my family including my 2 year old daughter Dhruti.
I hope that you all will enjoy reading and learning from this book as I did while authoring and contributed my little part for the growth of my beloved SQL Server J
Let me tell you about this book:
The term High Availability means that the servers or systems that host or run the business-critical applications should be highly available 24 X 7. As the word it-self defines how important it is to make these applications and data available for end-users as well as business users, if this data is not available for a short time, it will be a big problem for both sets of users. Imagine a bank spread across the country and having a huge customer base. One fine day, their server crashes! If the bank relies only on backups, then it might end up losing approximately 15 to 30 minutes of data, depending on the backup strategy. Now the HA options related to SQL Server such as clustering, replication, log shipping, and database mirroring will help overcome this situation.
In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring, below are the chapters:
Chapter 1: Understanding Windows Domains, Domain Users, and SQL Server Security
Chapter 2: Implementing Clustering
Chapter 3: Snapshot Replication
Chapter 4: Transactional Replication
Chapter 5: Merge Replication
Chapter 6: Peer-to-Peer Replication
Chapter 7: Log Shipping
Chapter 8: Database Mirroring
Appendix A: Troubleshooting
Appendix B: External References
If you are interested to read more about the detailed / contents of each chapter you may find them here https://www.packtpub.com/toc/microsoft-sql-server-2008-high-availability-table-contents
And, here is the link for the sample chapter https://www.packtpub.com/sites/default/files/1223-chapter-1-understanding-windows-domains-domain.pdf

Regards
Hemantgiri S. Goswami

Read More

Download SQL Server Training Kit

Couple of months back I have wrote a blog article about the availability of Microsoft SQL Server Developer Training Kit which is very helpful resource for the SQL Server Developers, trainers and professionals to understand the improvements comes with the release of SQL Server 2008.
Recently, 18th January 2011 Microsoft has released the updated version of Microsoft SQL Server Developer Training Kit which has

• # 31 Presentations (includes Slide decks, videos and transcripts)
• # 27 Demos (includes installer scripts, videos and transcripts)
• # 12 hands on labs (includes installer scripts)

The main highlight of the updated version is – BI Solution, yes you read it right. The version of developer training kit has the units for BI Solutions like:

• Build your first Microsoft BI Solution with SQL Server 2008 R2
• Introduction to BI Modelling Techniques

And all of this are free!!
Download the updated version of SQL Server Developer Training kit from here –> http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d
You may want to check the complete list of what is included in SQL Server Developer Training Kit and System Requirements before you begin download. 
Regards
Hemantgiri S. Goswami

Read More

Download Section for Script and free ebook

Dear Readers,


Good News!!


I have created a new section on this site from where you can download various scripts I have created and uploaded on my community website. Currently you can download scripts for:

• To Find Missing Index
• To get various SQL Server Property details
• To search a given word from a string
• Script which will take backup and verifies the backup set as well
• Script which will give you an idea on how to perform transaction in smaller chunks

You may find them all at http://www.sql-server-citation.com/p/downloads.html, and I promise that I will surely upload some more scripts in coming future.

Regards
Hemantgiri S. Goswami | http://www.sql-server-citation.com/

Read More

welcome 2011

Dear Readers,

Wishing you all very happy and prosperous new year, may the new day bring you joy and happiness

Read More