Upgrade Error - Valid Database compatibility level and successful connection rule

Recently when I was trying to upgrade an SQL Server 2008 R2 instance to SQL Server 2012, I was presented with an error during the upgrade rules validation.
The error message was, "The report server database is not a supported compatibility level or a connection cannot be established."

When I checked the reporting server databases, the databases had the right compatibility level.

Then I found that the SQL Server Name defined in the Reporting services configuration manager was not existing. This caused the validation rule to fail.

After changing the SQL Server Name to right server name, all the validations passed and it let me upgrade the SQL Server Instance. 

Change SSAS Deployment mode from multidimensional to tabular mode without reinstalling Analysis Services

It so happens that sometimes minds change easily and things needs to be done without reinstalling.
One such case is changing the Deployment mode of SSAS from multidimensional to tabular mode in SQL Server 2012.
To change the deployment mode

• Backup the multidimensional Analysis services databases on the instance (if any)
• Detach the multidimensional Analysis services databases from the instance (if any). These databases will not be usable in tabular mode
• Navigate to the path "<Install Location>:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config" and backup the file "msmdsrv.ini"
• Open the file "msmdsrv.ini" and change the value of DeploymentMode to 2.
  0 - Multidimensional
  1 - SharePoint
  2 - Tabular
  
• Re-Start the SQL Server Analysis services 

SSAS multidimensional mode - Before Change

SSAS tabular mode - After Change

Error while installing SQL Server 2012 - Access to the path 'C:\Program Files\Microsoft SQL Server\110\License Terms\SQLServerDenaliCTP_License_EVAL_1033.rtf' is denied.

When installing SQL Server 2012 you might run into an error which says "Access to the path 'C:\Program Files\Microsoft SQL Server\110\License Terms\SQLServerDenaliCTP_License_EVAL_1033.rtf' is denied."

To workaround this error, browse to the path "C:\Program Files\Microsoft SQL Server\110\License Terms\" and change the property of file "SQLServerDenaliCTP_License_EVAL_1033.rtf" to remove "Read-Only" and then click "Retry" and the installation will continue.

When was my database last taken Offline or Online

Here is a T-SQL script which tells when and who took the database offline or online recently.
This script utilizes the default trace and if the trace is reset after the database went offline or online then you have change the trace file path and name in the script.

DECLARE  @DBNAME nvarchar(100)
  ,@FileName nvarchar(max)
  ,@spid int
  ,@LogDate Datetime
  ,@Status nvarchar(10)
  
SET @DBNAME = 'AdventureWorks2008R2' -- Change DB Name
SET @Status = 'OFFLINE' --[OFFLINE or ONLINE]
SELECT @FileName=[path] FROM sys.traces WHERE is_default=1

DECLARE @ErrorLogTable table (Logdate datetime, ProcessInfo nvarchar(10), [Text] nvarchar(max))

INSERT INTO @ErrorLogTable
EXEC xp_readerrorlog 0,1, @Status, @DBNAME, NULL, NULL, 'desc'

SELECT TOP 1 @spid=cast(SUBSTRING(ProcessInfo,5,5) AS int)
   ,@LogDate=cast(Logdate AS nvarchar) 
FROM @ErrorLogTable

SELECT DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime
FROM sys.fn_trace_gettable( @FileName, DEFAULT )
WHERE spid=@spid and DatabaseName=@DBNAME and CAST(StartTime AS nvarchar)=@LogDate

SQL Server Replication - Configuring Peer-to-Peer Replication

The first step in configuring the Peer-to-Peer replication is to configure the distribution on all the involved SQL Server instances.
To configure the distributor, follow the steps posted here.
Once the distributor is configured on all the involved instances, we need to configure the transactional publication.
To configure the publisher, follow the steps posted here.
Once the publisher is configured, we need to enable the peer-to-peer replication for this publisher.

Data source name not found and no default driver specified - SSIS Error

Sometimes you might get the below error when you are trying to run a SSIS package which is trying to connect to a 32 bit system from a 64 bit system/driver.

[DataReader Source [320]] Error: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

To fix this error:

1. Go to "Solution Explorer"
2. Right-Click on the Project and choose "Properties"
3. Expand "Configuration Properties" and choose "Debugging"
4. Set the "Run64BitRuntime" option to "False"
5. Click "ok" and save the project

T-SQL to get the list of objects modified in x number of days

USE SansSQL; -- Change the Database Name
GO
DECLARE @Days int
SET @Days=300 -- Specify the number of days
SELECT name AS ObjectName
  ,SCHEMA_NAME(schema_id) AS SchemaName
  ,type_desc AS ObjectType
  ,create_date AS ObjectCreatedOn
  ,modify_date As ObjectModifiedOn
FROM sys.objects
WHERE modify_date > GETDATE() - @Days
ORDER BY modify_date;
GO

T-SQL query to get the Full text catalogs associated with a database

Here is a T-SQL query to get the Full text catalogs associated with a particular database

Use <Database Name>
SELECT DB_Name() AS DatabaseName, name 
FROM sys.fulltext_catalogs;
GO

To get the Full text catalogs associated with all databases in a instance, use the above query with sp_msforeachdb

Exec sp_msforeachdb '
USE [?];
SELECT DB_Name() AS DatabaseName, name 
FROM sys.fulltext_catalogs;'
GO

Connect to SSIS service on machine failed: Error loading type library/DLL

When connecting to an SSIS instance on a newly installed system you might be sometimes presented with the error "Connect to SSIS service on machine <Machine Name> failed: Error loading type library/DLL"

Install MongoDB on windows

Installing MongoDB on windows is very easy and happens in 3 simple steps.
MongoDB is self-contained and does not have any other system dependencies. You can run MongoDB from any folder you choose.

One: Download the MongoDB binaries from its website.

There are 3 builds available for windows

1. MongoDB for Windows 2008 R2 - This runs on only Windows Server 2008 R2, Windows 7 64-bit, and newer versions of Windows and takes advantage of recent enhancements to the Windows Platform and cannot operate on older versions of Windows.
2. MongoDB for Windows 64-bit - This runs on 64-bit version of windows operating system
3. MongoDB for Windows 32-bit - This runs on 32-bit version of windows operating system

Small data in a Big Data is a useful data

Every bit of data has its own space – be it BIG or SMALL!

Considering the universe which is HUGE, Earth is one small bit of it and the space required for individual like us is one tiny tot. That doesn't stop us from dreaming BIG or living BIG in all.

In the database technology world today the most buzzing word around us is BIG Data – so this is like Universe with loads of information which is formed of million-billion bit of tiny tots.  So, in that way every bit of data is informative talking about something or the other.

One Such practical example wherein we see such usage of data, be it BIG or SMALL is Facebook. Facebook introduced a concept known as Graph Search (concept of breaking huge chunk of data into smaller bits making it more consumable). The level Graph search has adopted is getting to each individual information. This data is being consumed to give back the ‘personal belongingness’ for each individual – just because Graph search is helping us to get there. This ultimately means Big Data has showcased what every bit of data can be – BIG or SMALL!

Many companies have already adopted this technology, with more and more companies are moving towards this technology making use of the abundant data around us.

We could see such adoption at NASA for analyzing and forecasting of various extra celestial activities. So, data is important in every possible way! The useful data or the end result we see might be small but without the big data it is impossible to derive this tiny useful data which makes a drastic changeover.

BIG REVOLUTION is here! Embrace it!!

What is MongoDB?

On this auspicious day of Diwali, Let's learn something new.
At SansSQL, we are always committed to Learn and Share new things and technologies.

What is MongoDB?

MongoDB is a cross-platform document-oriented database system.
It is an Open source database and classified as a "NoSQL" database. This provides high performance, high availability, and easy scalability.

Importance of DAILY CHECK LIST!

Here I am, back from long holidays to my blogs! I was thinking of writing in which happens to be part of any DBA on production support (it can different levels as such).  So, have chosen to blog the importance of Daily Check list.

Daily check list:

The following is purely what we have adopted as part of the routine daily check. The check can be done at one time everyday and at a convenient time so that we don't miss anything.

First thing, we have selected list of servers which are very critical for the business and have to be part of check list.