Resources to Enterprise and Self -Service BI

Recently I am taking extra responsibility to deliver BI solution for our company and as such need to pick up and learn BI. The purpose of this article is to share the resources that I found useful for the learning purposes. I will add more from time to time, remember to check back. Hope you enjoy it.

This article gives a good introduction to different components in Enterprise and Self-Service BI. There are more learning resources at the end of it, don't forget to check it out.
http://sqlmag.com/business-intelligence/delivering-bi-through-sql-server-and-sharepoint

This is SharePoint 2013 training from Technet.
http://technet.microsoft.com/en-us/sharepoint/fp123606

Below is useful info about building a SharePoint BI environment.
http://blogs.lessthandot.com/index.php/DataMgmt/business-intelligence-1/sharepoint-2013-bi-part-1
http://iouchkov.wordpress.com/2013/10/19/how-to-install-sharepoint-2013-on-windows-server-2012-r2/

PASS Summit 2013 Experience

I haven't been blog for almost half a year, seems like I am MIA or something. Actually there has been bit of changes to my personal life which is crazy but I am back to full action now and hopefully I can continue writing on this blog.

The first topics I am going to share after I am back is about my experience at PASS Summit 2013. If you don't know what is PASS Summit which I assumed not, PASS Summit is the world's largest, most-focused, and most-intensive conference for Microsoft SQL Server and BI professionals. If you never attend one, I strongly encourage you to do so. The next summit will be held at Seattle, Nov 4-7 2014.

In my opinion, I think below are the main reasons why PASS Summit is so attractive:
1) Networking with SQL Server professional around the world.
2) Learn from industry expert and the best.
3) Chance to learn from and speak to Microsoft development team during chalk talk session.
4) Get help from Microsoft PSS and SQLCAT team.
5) Have fun.

You can get more info on PASS Summit at http://www.sqlpass.org/summit/2014. You will know what I mean above after you have attended one. Hope to see you there next year.

When Disaster Strikes

There is an incident last week where our storage vendor login to our storage during business hour and change some configurations. You can probably guess what happened afterward. Our main ERP system database server is down. Although it is a virtual server but for some reasons we are not able to boot it up. Good thing is that we have synchronous database mirroring setup so we failover all databases to the mirror server. The failover and validation process took less than 15 minutes which meet our RTO so business is happy and since synchronous mirroring is being used so no committed data loss so audit is happy.

Key lessons you can learn from this are:

1. If you don't have any sort of high availability solution in place, seriously think about this. Although it is a simple solution, it will potentially save your job. I would recommend trying database mirroring. It is simple to implement.
2. Once you have decided the solution to be used, implement it and test it. Try to test failover and failback for not less than 20 times I would say. You will know how useful is this practice when the real disaster strikes especially if everybody is looking at your screen waiting for you to failover and bring everything back to normal.
3. Automate the whole process via any sort of scripting solution if possible. In my case, I have over 50 databases in one instance that need be to be failover, without automation, it will take me forever to do this via GUI. Other than this, it will also avoid potential human error in the process.
4. Document the procedure. If DBA is not available for whatever reason, with automation, anybody  with the correct permission can follow and perform the procedure.

Below is the script that I used to failover all databases. Noted that in this case I am not exactly failover but remove mirroring session as I have no idea how long the principal server will be down and I don't want to take the risk of transaction log blow out.

SET NOCOUNT ON

GO

DECLARE

@DBName varchar(128),

@CMD varchar(8000)

DECLARE db_cursor CURSOR

FOR

SELECT d.name

FROM sys.databases d

INNER JOIN sys.database_mirroring m                      

ON d.database_id=m.database_id 

WHERE d.name LIKE '<databasename, sysname, database>%'

AND d.recovery_model = 1

AND d.state_desc in ('RESTORING','RECOVERING')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @CMD = 'USE [master]; ' + 

'ALTER DATABASE [' + @DBName + '] SET PARTNER OFF;' 

PRINT @CMD

EXEC(@CMD)

FETCH NEXT FROM db_cursor INTO  @DBName

END

CLOSE db_cursor

DEALLOCATE db_cursor

GO

After removing the mirroring session, we have to recover the databases as they are still in no recovery state. Below is what I used.

DECLARE

@DBName varchar(128),

@CMD varchar(8000)

DECLARE db_cursor CURSOR

FOR

SELECT d.name

FROM sys.databases d

INNER JOIN sys.database_mirroring m                      

ON d.database_id=m.database_id 

WHERE d.name LIKE '<databasename, sysname, database>%'

AND d.recovery_model = 1

AND d.state_desc in ('RESTORING','RECOVERING')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @CMD = 'USE [master]; ' + 

'RESTORE DATABASE [' + @DBName + '] WITH RECOVERY;' 

PRINT @CMD

EXEC(@CMD)

FETCH NEXT FROM db_cursor INTO  @DBName

END

CLOSE db_cursor

DEALLOCATE db_cursor

GO

After this, depends on your environment, the databases are quite operational and ready to go. 

Connection of application to the databases is separated story though. I try to have all applications to use cnames to connect to the database server rather than the physical server name. In the event of failover, we just have to update the DNS to point to new server. It eliminate the hassle to update connection string especially if there are heaps of them.

I hope above reasons and example is good enough to convince you to implement some sort of high availability solution on your most critical system if you don't have one. Even you have one, it doesn't means it will always work and you know what to do, so please document and practice, practice, and practice or else please kindly update your resume.

Why Virtualizing Production SQL Server?

I asked this question to lot of DBA and most of their answer is that performance will be hindered as VMs are sharing resources. They like virtualizing lower environment (dev, test) SQL Server but definitely not on production environment. Then my question is why there is no performance issue when you have multiple SQL instances on one physical server or have multiple databases on one SQL instance? Aren't this case also sharing resources? To me, this is an issue on design, management and configuration on server but not on the technology itself. As long as you do it right, there should not be any major issue regardless of whether you virtualize the SQL Server or create multiple instances or multiple databases on single instance.

But why virtualization? Below are some reasons why I virtualized SQL Server in my environment.

Provisioning
A new server can be built in couple of minutes with the correct template. How fast can you build a physical server?

Flexibility
Server can be migrated from one host to other host transparent to user if there is resource constraint. Extra host can be added to provide more resources if needed. What do you need to do to scale up or scale out a physical environment?

Disaster Recovery
It gives you extra simpler option on DR. VMs can be continuously replicated to DR site. Failover and failback is easier and simpler compared to traditional log shipping method.

High Availability
You can use live migration to move VMs if there is hardware failure to minimize downtime.

Rollback
What happened if you patch your physical server on one nice day, reboot the server, it crashed and never able to boot again? What is the different if it is a VM and you have the snapshot created before you patch it? I think it will safe your life, time and your day will be still a nice day.

Consolidation
It is just a waste of resource if you are using 5% of CPU resource on your physical server. Why do you want to drive 10 mph with your Ferarri? Other than effective use of resources, you can also reduce hardware maintenance cost, power and cooling, create a greener environment by consolidating physical servers.

I think there is challenge in virtualizing SQL Server, the main one is to do it right to meet performance requirement of the applications and databases. However by looking at the benefit of virtualization, I think it is worth to do it. There is always challenge no matter what you do, so why not just face the challenge and do it? I will talk about how I successfully virtualized my most critical production SQL Server with minimum hiccup in my next post .

SQL Server 2012 "Evaluation period has expired" Message After Upgrading to Licensed Edition

I have a old dev box that I run evaluation edition of SQL Server 2012 that I have decided to upgrade to licensed edition. The upgrade went successfully without any issue. However I got below error message after the upgrade when I tried to launch SQL Server Management Studio:

After some Googling or Binging, I found below KB on similar error.

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

However it is for SQL 2008 evaluation edition. Since it is just a old dev box, why not just give it a shot to see what happen. So I just follow "Option 2" but with different path on the registry as this is SQL 2012. Below is the screen shot on what I changed. After that just rerun the upgrade.

Guess what, it works!

If you have similar problem, you might want to try the same thing but try at your own risk especially if it is on production server as this involved registry chance. But why do you run evaluation edition on production server? This supposed to be on SQL 2008 according to the KB, I am no sure why it is happening on my SQL 2012 instance, let me know if you know why.

Database Migration

I have been thinking of what should be my first post and this immediately pop up as this is what I have been doing for the past 6 months.

We have projects on replacing all old servers in all our data centers with new servers and as such we need to move all databases hosted on the old server to new server with minimum impact and downtown to the users. As most mission critical databases contain financial information, we also need to ensure there is no data loss and also modification to the data in the databases adhere to SOX compliance requirements.

There are multiple ways to deal with database move, backup/restore, detach/attach and SSIS are among the most frequently used method by most DBA. However the challenge is that there are about 90 databases, size range from 5-20GB each, hosted on one SQL instances for example. Think about questions below:

1) How long do you think it needs to complete the move using above method?
2) What is the rollback plan if the databases don't work on new server, are you going to use the same method to move the databases back to old server?
3) What will be the total downtime especially with rollback?

The method that I used is actually to setup database mirroring on all databases. Old server is the principal and new server is the mirror. I normally set this up a week prior to the official move date. Once I setup mirroring, I will also start collecting performance baseline information on new server. This is to ensure the performance of the databases will not be impacted. All of these steps can be done anytime without any outage.

During the official switch date time, what need to be done is just failover the databases to mirror server. The outage is essentially the failover time. If thing went wrong, the rollback is just to failback to old server.

With this method, the overall outage that I have for the SQL instance above is less than 5 minutes. As it is high safety mirroring, we are confident on the integrity of the data moved to satisfy audit requirement.

I hope this post is useful and give you an extra option if you are working on database migration project. This might not suitable for all migration project, careful planning and testing should be perform prior to implement this. Please let me know if you use any other alternative method before that actually works quite well, I am interested to learn that. Thanks.

Introduction

G'day mate, my name is BP or some people called me Boon, anyway it's just a natural key to me and there can be multiple natural keys depend on situation.

As this is the introduction part so I will mainly talk about myself. I am a SQL Server DBA at least for now. I used to be an application developer dealing with backend SQL Server, then for some reasons turn to do SQL Server development and afterward administration plus BI development.

I worked in different cities and countries, met lot of great people and organization. I won't be who I am today without them. So I think it might be time for me to give what I have learnt and knew back to the community and this is mainly this blog about. I hope you can learn something useful from here. Cheers.