Find out free space from a SQL server

You can right click a database to find out its size in SQL Server Management Studio, but the most important question for DBAs is how much disk space left for the databases to grow?


Here is a quick one liner.



exec master..xp_fixeddrives

Microsoft Denali/Crescent: From installation to Report Building

Background

By the time this document is written (2011/09) Microsoft releases the CTP3 (Community Technology Preview) of the next version of SQL Server (coded name Denali) with the application add-on for SharePoint (coded name Crescent).

The following document provides necessary steps to build a test sandbox environment in order to demo the Crescent web based reporting feature. The sandbox environment consists of the following:

1.       Build on a virtual machine using Microsoft Hyper V of Windows Server 2008.

2.       Single machine will host  as Active Directory Controller, SharePoint 2010, and SQL Server “Denali”

Evaluation download can be found from the following web site:

“Denali” CTP 3(available to registered partners only):

https://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID=37147

SQL Server 2008 R2 Eval: http://msdn.microsoft.com/en-us/evalcenter/ff459612

SharePoint 2010 Eval: http://sharepoint.microsoft.com/en-us/Pages/Try-It.aspx

Windows Server 2008 Eval: http://www.microsoft.com/en-us/server-cloud/windows-server/2008-r2-trial.aspx

“Denali” sample databases: http://msftdbprodsamples.codeplex.com/releases/view/55330

Overview of the installation step:

a.       Create a Hyper V vm .VHD file with Windows 2008 as guest operating system.

b.      Configure Windows 2008 as Active Directory Domain Controller.

c.       Install SQL Server 2008 R2, Database Engine Services Only.

d.      Install SharePoint only DO NOT CONFIGURE.

e.      Install SQL Server Denali with Report add-on for SharePoint .

f.        Run SharePoint Configuration Wizard.

g.       Create SharePoint Services Application for SQL Server Reporting Services.

h.      Configure SharePoint Content Type to add SQL Server Reporting features.

i.         Test it by creating report.

a.       Create a Hyper V vm .VHD file with Windows 2008 as guest operating system

1.       Login to a Windows 2008 R2 Server  (must have 500GB free hard disk space and 24GB of RAM)

2.       Open Hyper V manager. Select New to create a new virtual machine (250~300GB hard disk space and 12~16GB of RAM)

3.       On the CD-ROM drive of VM, select a Windows 2008 Server CD media or .ISO file.

4.       Click start to start up the vm, Windows 2008 installation begins automatically.

b.      Configure Windows 2008 as Active Directory Domain Controller.

1.       When guest OS installation is completed.  Renamed the machine name (for example: mscrescent)

2.       Go to Server Manager to install the following roles: IIS web server, application server, AD services, DNS server, WINS server.

3.       Also in Server Manager, install the following features:  Desktop Experience,  .NET Framework 3.5.1

4.       Disable all firewall.

5.       Run DCPROMO to promo the vm as its own AD domain controller (for example: denali.local) it requires server restart.

6.       After server restarted, check to confirm server is now a domain controller by verify the computer name now should be mscrescent.denali.local.

7.       Go to Active Directory Users and Computers. Add two user account with domain admin rights: sqladmin (for SQL Server instance access account) and spadmin (for SharePoint Services Application access account)

c.        Install SQL Server 2008 R2, Database Engine Services Only.

1.       As a SharePoint prerequisites. A Sharepoint_Config database is needed to host SharePoint’s content data before installation. And it CANNOT be SQL Express.

2.       Install SQL Server 2008 R2 Trail (standard version is preferred).

3.       Select Custom installation.

4.       Select component to install. Select only the Database Services Engine, then Next.

5.       When Configure database instance window appear, specify user account sqladmin as a SQL database administrator.

6.       Let the installation run and complete.

d.      Install SharePoint only DO NOT CONFIGURE.

1.       Install SharePoint 2010, click on “install prerequisites” to install and auto-configure any necessary updates before the installation begins.

2.       Install SharePoint. SharePoint setup will go through a pre-installation rules check to verify all necessary updates and configurations are present. If Rules check is failed, follow the error message to install/configure updates if necessary.

3.       When ask to specify a database to host content database (SharePoint_Config), specify the SQL server name (which is local host) and default instance.

4.       When ask to specify a access account for SharePoint Central  Administration, browse and select the domain\spadmin account.

5.       Installation runs. When complete. A SharePoint Configuration Wizard Window opens. Uncheck the box for Begin Configuration Wizard. Just install SharePoint. Do not configure it.

6.       Let the SharePoint installation run and complete.

e.      Install SQL Server Denali with Report add-on for SharePoint .

1.       Extract SQL Denali software package. Install Denali

2.       On Installation dialog, click New SQL Server stand-alone installation or add features to an existing installation.

3.       Click Next, SQL Denali will process a series of support rules. If passed, click Next to continue the installation process.

4.       On the  Setup Role page, select SQL Server Feature Installation

5.       Select the following features (the minimum requirement for SharePoint add-on over SQL Server reporting “Crescent”)

1.       Database Engine Services

2.       Reporting Services – SharePoint

3.       Reporting Services Add –in for SharePoint Products

4.       Management Tools – Basic

5.       Management Tools – Complete

6.       Click Next on the Installation Rules page. If passed, click Next again to continue.

7.       Instance Configuration page open, accept the default instance of MSSQLSERVER (The goal is to make both SQL Denali and SharePoint 2010 using the same database instance.) click Next.

8.       Report Services Configuration page open, a default selection “Install only” should be selected. If not, select it. ( the goal is to make configuration wizard to be the last step to run after all components are installed.) click Next.

9.       Installation Configuration  Rules page will run again, please review and make sure all components are selected and installation requirement is passed. Click Next to begin Denali installation.

f.        Run SharePoint Configuration Wizard.

1.       Now the test server vm should have AD controller, SQL Denali and SharePoint installed. Login as domain admin, click on Start, All Programs, SharePoint Products, SharePoint Configuration Wizard.

2.       Modify Server farm Settings page opened, the database server name should be the local test server name (in that case: mscrescent. Just the host name should be fine. Should not use the “localhost” as server name)

3.       The Database name should be “SharePoint_Config”

4.       Click Next, select “This machine will host the web site”

5.       A complete list of configuration should be listed.  Click Advanced Settings to specify a TCP port for SharePoint Central Administration (default is 8080)

6.       Click Finish. SharePoint Configuration Wizard begins to run the configuration process. It will be long while.

g.       Create SharePoint Services Application for SQL Server Reporting Services.

Now the SharePoint server configuration database is up and running, the next step will be configure the SharePoint Report services.

1.       Click Start, go to Microsoft SharePoint  2010 Products.

2.       Right click on SharePoint 2010 Management Shell, click on Run as Administrator

3.       A command prompt window opened. Type the following then enter to install Report  Services:

Install-SPRSService

4.       Type the following then enter to install the services proxy:

Install-SPRSServiceProxy

5.       Go to SharePoint Central Administration, on the System Settings group, click on Manage Services on Server.

6.       Locate SQL Server Reporting Services Service, then click Start.

h.      Configure SharePoint Content Type to add SQL Server Reporting features.

First we will create a default site so users can access

1.       From SharePoint Central Administration, under Application Management group, click on Manage web applications.

2.       On the Web ribbon, click New

3.       Leave every setting as default. Under IIS Web Site check the radio button Use an existing IIS web site.  You can name the Host Header as “Crescent Demo”

4.       Under the Service Application Connections, make sure SQL Server Reporting Services is checked to select. Then click OK to create a default web site in SharePoint.

Next we will logon to our default web site to configure the SharePoint document content type.

1.       Logon to the SharePoint web site we just created (http://sharepoint-server-name/)

2.       On the left hand side of the site page, click on Shared Documents. Then click on Library ribbon. Click on Library Settings.

3.       Under General Settings group, click on Advanced Settings. Look for a radio button for “Allow management of content types? “  click Yes, then click OK.

4.       Back in Document Library Settings page. Scroll down to Content Types section, click “Add from existing site content types” then select all SQL report services content types (including Report Data Source, Report Builder Model and Report Builder Report) then click OK.

5.       Back in Content Types section, make sure the selected content types are “Visible on New Button”

i.         Test it by creating report.

There are multiple methods to create BI reports in SharePoint. BI reports are created based on published data sources from SQL server. Currently (CTP3) supports Analysis Service Tabular Project from Microsoft Studio 2010. So develop a tabular project and deploy it.

When a tabular project deployed into SharePoint, it will be saved in Share Documents as data source. The quickest way to create a report is to click on the data source, select  Create “Crescent” Report from the drop down list.

End

SQL error severity

0 to 9: informational messages
10: informational messages that return status information
11 to 16: error that can be corrected by user
17 to 19: software errors that cannot be corrected by user
20 to 24: serious system error
25: SQL server service terminating error


http://msdn.microsoft.com/en-us/library/aa937483(SQL.80).aspx

SQL Rename column on table

Using sp_RENAME in SQL we can rename table name, and column name on the fly:

     USE AdventureWorks
     GO
     sp_RENAME
     'Person.Address.AddressLine1', 'Addresslin01', 'COLUMN'
     GO

The above statement using a sample table to rename a column from 'AddressLine1' to 'Addresslin01'

SQL COALESCE: Whichever not null.

Sometimes we need to retrieve information from multiple columns from a table whichever is not null. In such situation COALESCE can be used in SQL query

I picked a sample table ProductListPriceHistory. It shows each product's ID and its list price. Some of them have both StartDate and EndDate. Some of them have either one only.  

Assume those date indicated as the availability of each product, I want to retrieve the information based on the following conditions:
1.) if both StartDate and EndDate are not NULL, retrieve the first column (StartDate)
2.) if either StartDate or EndDate is NULL, retrieve whichever column is NOT NULL

Therefor I wrote a query like this:

     USE AdventureWorks2008
     GO

     SELECT ProductID,COALESCE(StartDate,EndDate)TodayPrice, ListPrice
     FROM
     Production.ProductListPriceHistory

After execute the query, it retrieve the value from StartDate and EndDate whichever is not null and input it into TodayPrice column.

SQL: Combine CASE WHEN with AND

CASE WHEN expression can combine with AND, OR..
In the following example, lets assume a condition that we need to apply a integer numeric value in three specific colors of the product AND their list price are more than 5 dollars. Using CASE WHEN on color and AND list price >5 which results only products are black, silver, red and higher than 5 dollars list price its color data value will convert as 1. 

USE AdventureWorks
GO
SELECT
Name,
ListPrice,
CASE WHEN ( Color IN ('BLACK', 'SILVER', 'RED')
        AND (ListPrice > 5))
    THEN 1 ELSE 0 END as colorcode
    FROM Production.Product

SQL: Convert data in multiple conditions using CASE WHEN..THEN

From this example above, the original table has a column 'Color' which store the color specification of the product line.

Let's assume now when developing a ETL, this column need to be convert to 'colorcode' to display the color description as number.
We could use CASE WHEN.. THEN in SQL to convert the table for reporting.
In this situation, using CASE WHEN..THEN is better than just IF..THEN because CASE WHEN could easily setup multiple conditions.
In this example we have to place a color code for black, silver, and red in our product table:

 
USE AdventureWorks2008

GO

SELECT Name,
CASE WHEN ([COLOR]='BLACK') THEN 1
WHEN ([COLOR]='SILVER') THEN 2
WHEN ([COLOR]='RED') THEN 3
ELSE 0 END AS COLORCODE

FROM

Production.Product

After we run the query, notice that the column 'colorcode' has converted color name into integer numbers:

 

SQL: Converting Data Type

Sometime data type needs to be changed for querying or BI reporting use. In situations like this the CONVERT clause comes in handy.

For example, I need to grep the data of Date for reporting. However the data type of date in the data warehouse are Date time which has date and time stamp.

I don't need the time stamp part of it. I want to turn my date key from datetime to just date, so I place a CONVERT clause within my SELECT statement. 



SELECT
CONVERT (DATE, FullDateAlternateKey) AS FullDate
FROM AdventureWorksDW.dbo.DimTime

After the query is executed, notice the difference of data type between the original table and the query output: 

MSDN has a full list of data type which CONVERT can handle. Please check out this link:

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

Create Dynamic prompt on WEBI report.

Compared to Crystal Report, create a dynamic prompt on Web Intelligence report is simplier. The design view of WEBI is always has two window: Top window contain any data field you drag and drop from the Business Objects Universe as display on the report. Bottom window contain any filters you could build. The filter can also turn to prompt.

1.in WEBI report design window, drag a data field into the Query Filter window, select a condition as "prompt".

2.When user access this WEBI report, the prompt windows will display. in this case, user must select a data value (Year name) from a list of values. 

3. Then click Refresh Query, the WEBI report display the filtered data based on user selection prompt.

4. One thing about Business Objects WEBI is quick. One click can turn filter to prompt. In this case, one right click can turn a report table into graphic. Just select "Turn To"

5.Select the graph you prefer then click OK

6. Report table now turns to bar graph.

SQL: loading data from one table to another using INSERT INTO

Here is a quick script to load data from one table to another one. You can just copy the data or add optional condition with the WHERE statement. The syntax works like this

INSERT INTO dbo.targettable ( [column1],[columnN])
SELECT [column1],[columnN]
FROM dbo.sourcetable GO

INSERT INTO AdventureWorksDW2008.dbo.FactInternetSales
([RevisionNumber],[OrderQuantity],[UnitPrice]
,[ExtendedAmount],[UnitPriceDiscountPct],[DiscountAmount]
,[ProductStandardCost],[TotalProductCost])
SELECT
[RevisionNumber],[OrderQuantity],[UnitPrice]
,[ExtendedAmount],[UnitPriceDiscountPct],[DiscountAmount]
,[ProductStandardCost],[TotalProductCost]
FROM AdventureWorksDW.dbo.[FactInternetSales]
WHERE OrderQuantity > 200
GO

Crystal Report: Build a User prompt for filtering report data

As a report builder, quite often I built predefined filters for the reports.  Sometimes report users like to filter the report data as they saw fit. In that case a dynamic prompt is needed so user can click and select  data to be displayed on the report.

in this example, a dynamic prompt is needed to allow report viewers select data by State 

1. From the Crystal Report Design View, click on Parameter to create new parameter.

2. From Parameter window, select "Dynamic" as the List of Values (or, you can specify a static list for user to select.) on Choose a Data Source, select "State" (you can select any column field of a table where Crystal Report is connected to.)

3. After you specify the value, the parameter is named as "?by State"

4. After click OK a new parameter "By State" is created, drag to the Report area.

5. On Crystal Report Design View, click on the icon "Selection Expert" to create a filter. Click on the drop down menu to select the parameter {?By State} as a filter, then OK.



6. That's it. a user prompt is created on the report, when users open this report, they will first see this window prompt for State selection.

7. After a state been selected, the report will open with specific data displayed.