what’s the role of a “Master” database in a SQL Azure world?

Standard

One of the question that pops up in the head of first-time SQL Azure user is “What is Master Database doing in my server”. They try to delete/drop it. That does not happen. Fun fact: I tried dropping Master database when I had got my hand on SQL Azure for the first time. Any-who what we speculate is that  “OMG! I paying for this SQL Azure Master database!” Wait…No, read this:

You are not charged for the SQL Azure Master Database.

Ok Good news, But why does this “read-only” master database exist?

Here is the information I have used that is available via SQL Azure Master Database:

  • It has SQL Azure usage metrics
  • It stores logins for a given SQL Azure LOGICAL server (To manage Server-Level security)
  • To view list of all databases via sys.databases
  • To create databases via TSQL. You do that by logging into master database first.

What do use SQL Azure master database for?

That’s about it for this post.

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

paras doshi blog on facebookparas doshi twitterparas doshi google plus

Excel file does not open, Its stuck indefinitely in “opening in protected view” stage

Standard

I downloaded an Excel file (It had the PowerPivot Model) from Internet on my Windows Server Dev Machine. When I tried opening that file on my machine it got stuck in the “Opening in protected View” stage:

excel opening in protected view message

I was able to solve the issue and I would document how I did that in this blog-post:

1. Go to START > Open Excel

2. Go to File > Options > Trust Center > Trust Center Settings

excel 2010 trust center settings

3. In the Trust Center. Go to Protected View

Here you can disable the Protected View based on your needs.

Since I wanted to open a file that originated from Internet, I disabled “Protected View for files originating from the Internet”.

Clicked on OK.

4. Now go to File > Open that FILE!

Once you are done, You can enable the protected view. Important!

So That’s about it for this post, I hope it was helpful!

Did you knew: You can change the Location of “Desktop” folder

Standard

Did you knew that we can change the location the “Desktop” folder. Yes! The default location where files/folders are stored is “c:users<xyz>desktop” and you can change this.

But why do you want to do this?

1.FUN!

2. It’s a good practice to separate USER data and SYSTEM data. So if you have a D (Or E?) drive in your computer, you can change the location of the Desktop from C to D (or E).

So how do you do that?

1. Go to c:users<xyz>desktop

2. Right Click on the Desktop Folder and select “Properties”

dekstop folder properties 3. In the properties dialog box. Select “Location” and after entering the NEW LOCATION – click on MOVE. 

location property of the desktop folder

Click on Apply. You’re done!

I tested it on Windows 7 and Windows Server 2008 R2 and it works. It does not seem to work with Windows XP. If you know of any windows OS’s where it works – let us know in the comments!

I thought of writing this as a blog-post after this tip that I submitted was well-received. Any-who that’s about it for this post, I hope it was helpful!

Manage ALL relationship’s between tables while building PowerPivot Model

Standard

One of the task while building a PowerPivot Model is to create/Edit/Delete Relationships between tables. With SQL Server 2012 release of PowerPivot for Excel, we now have a Diagram view and this makes our task of defining relationships easier! Great addition. Any-who when you view your model in Diagram Model, This is how it looks:

powerpivot sql server 2012 diagram view

To Edit a Relationship, You can click on the Relationship represented by the arrow and it would pop up an “Edit Relationship” Dialog Box.

But what if you wanted to Manage ALL relationships of this Model? You can do this!

GO TO Design Tab > Click On Manage Relationships.

manage relationships powerpivot 2012

Now here you can manage (create/edit/delete) relationships among tables. Very easy!

manage relationships dialog box powerpivot 2012

That’s about it for this post. Your feedback is welcome!

 

Bird’s Eye view of SharePoint BI Dev Environment Setup process (SQL Server 2012, Sharepoint 2010)

Standard

 

Step 1
Get the Binaries: SQL Server 2012 RTM, Windows Server 2008 R2, Sharepoint 2010 SP1 x64
Setup a VM: Check the System Requirements and configure the VM. And then Install Windows Server 2008 R2 x64 SP1 (and updates)
Step 2
Add Server Roles: Application Server, File Services, Web Server
Change Server Name to a friendly Name
Step 3
At this stage, I cloned the Virtual Machine. And the following steps will be performed on the cloned (copied) virtual Machine. Thanks for the tip that I found here.
The benefit of cloning is that you have a Windows Server 2008 R2 VM Template that you can use in Future.
Step 4
On the cloned VM, run dcpromo.exe. This will make your server the Domain Controller and it will install ADDS binaries if they are not installed prior to running dcpromo.exe
Step 5
Install Sharepoint per-requisites
Install Sharepoint 2010 SP1
Note: Do NOT run the SharePoint configuration wizard
Step 6
Run SQL server setup to Install “SQL Server PowerPivot for SharePoint”:
SQL Server PowerPivot For Sharepoint
Post-Installation: Launch the PowerPivot configuration tool to configure the server
SQL Server PowerPivot For Sharepoint post installation
Step 7
perform a NEW installation of SQL Server
Analysis Services Mode: Tabular
Step 8
perform a NEW installation of SQL Server
Analysis Services Mode: Multi-Dimensional
Step 9
Verifying Reporting Services Installed in Sharepoint Mode. Tutorial here.
Also Extend the Document Libraries to include BISM connections.[Read Follow up Blog Post: How to extend document library to connect to Tabular Model to create Power View reports ]
Step 10
At this point, I installed and ran the SQL Server Training Kit 2012. It will list various “Missing” softwares like office, silverlight and also it will help you install sample databases. It’s a nice “shortcut” to make sure that you a “complete” sharepoint BI dev Environment
sql server training kit 2012 check dependencies

Resources that I refer to for configuration Details:
1) SQL Shorts Video to configure SharePoint BI environment
2) Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3)
3) Deployment Checklist: Reporting Services, Power View, and PowerPivot for SharePoint

That’s about it for this post. I’ll refer to this post whenever I have to setup my SharePoint BI Dev Environment. And if you are in the same position, then i hope the Bird’s eye view and the resources listed are helpful to you as well.

How to disable the password expiration policy in Windows Server 2008 R2 (Demo Machine) ?

Standard

Before we begin, Please note that:

1) it’s applicable for your Demo Machine only. Or your Dev Machine. The reason I am documenting it that I do not want to change the password of Windows Server on which I have my Sharepoint BI dev environment Setup. It’s MY Dev Environment and I am NOT sharing it with other folks PLUS I do not anything sensitive on it, So I can afford disabling the password expiration policy.

2) This Blog Post will Show you step by step procedure to disable the password expiration policy IF your server is NOT a Domain Controller. If it’s a domain controller then the steps are different.

UPDATE: Find a blog post about disabling password expiration policy for Domain Controller: http://parasdoshi.com/2012/06/19/how-to-disable-password-expiration-for-windows-server-2008-r2-domain-controller/

Now, Let’s begin:

1. Go to Start, Administrative Tools, Local Security Policy

2. Under Security Settings, Select Account Policies

3. Click on Password Policy.

windows server disable password expiration policy

4. Change the Value of Maximum Password Age to “0”

windows server disable password expiration policy

5. Click on Apply.

That’s about it. You have disabled the password expiration on your Windows Server Demo machine (which is not a domain controller)

If you are searching for a resource that could help you disable the password expiration policy on windows Server (Domain Controller) then please refer: http://forums.iis.net/t/1148164.aspx

UPDATE: Find a blog post about disabling password expiration policy for Domain Controller: http://parasdoshi.com/2012/06/19/how-to-disable-password-expiration-for-windows-server-2008-r2-domain-controller/