Access or connect Sql Server on Azure Virtual machine

Mixing IAAS (Infrastructure as a service ) & PAAS (Platform as a service) is a exciting concept and Microsoft windows Azure allows us to have this excitement.
First or rather easy step in any application migration would be to migrate our application as is. This would mean that we would have a Azure virtual machine hosting Sql Server for our databases. This is actually as simple as it sounds. Now once we have this ready, requirements like connecting on premise SSMS (Sql Server management studio) to Sql Server on Azure VM or connecting other on premise Admin related pages or application to Sql Server on Azure VM would popup. In this article we will tackle these requirements and achieve connection to Sql Server on Azure VM from on premise entities.
So let us start with creating a Azure VM hosting Sql Server. For simplicity we will name this VM as VM-SqlServer12


Once we have this machine, we will create a end point which would allow us to connect to Sql Server.
Go to End Points tab and click on Add End Points

Select the default Add EndPoint option

Ensuring correct port numbers is very important here. The public port is used to access the virtual machine from the Internet. The private port is used to control internal access to the virtual machine through the firewall

Once our end point is defined we will actually connect (RDP) into this VM and update the firewall settings so that it allows us to connect to Sql Server installed in it

While connecting we will be prompted to enter the password that we entered while creating this VM.

We will open up Sql Server configuration manager and ensure that Named Pipes and TCP/IP are enabled. To enable a protocol, right-click the protocol name and select Enable.

Next step would be to make a firewall rule such that incoming request on port 1433 for Sql Server are allowed
Go to Start | All Programs | Administrative Tools and open Windows Firewall.

Change default Rule Type to Port

Keep the protocol as TCP but enter specific port as 1433

For the remaining part of New Inbound Rule wizard select the default and add the rule.
Final step would be to create a user in Sql Server which would allow us to make Sql Authentication.
Open SSMS and create a new Login

In new Login window select Sql Server authentication and create a testuser

Next step would be to enable Sql authentication. Right click on sql server and click on properties.

Go to security tab and select Sql Server and Windows Authentication mode.

Well now we are almost there. In order to ensure that all these configuration changes are applied we will restart the Sql server and try to connect it from on premise SSMS. In order to connect to Sql server from on premise SSMS we would form the server name as per following rule
tcp:<DNS Name>,Public Port ----> tcp:vm-sqlserver12.cloudapp.net,55555
 

ASP.NET, generate and download PDF report without a popup window using RDLC report

This blog covers multiple challenges faced during a web development project. In this blog article we will see how to generate a report in PDF format and download it without using popup window

Lets start with a Visual Studio web site project – ASP.NET Reports Web Site.

Visual Studio wizards will help you create a connection string & datasets. This project template will also give us a default.aspx with a report viewer control on it. We will use default items added by Visual studio and move ahead to design our report. We will just add a simple table and pie chart to our report.

Once we have our report working fine we will start writing code which will give us PDF of this report. Now lets add a page called ExportPDF.aspx into our project and add following code in the codebehind file of that page.

protected void Page_Load(object sender, EventArgs e)

{

    if (!Page.IsPostBack)

    {

        try

        {

            var reportViewer1 = new ReportViewer

            {

                ProcessingMode = ProcessingMode.Local

            };

            reportViewer1.LocalReport.ReportPath = "Report.rdlc";

            reportViewer1.LocalReport.EnableExternalImages = true;

            reportViewer1.LocalReport.EnableHyperlinks = true;

            //Supply datasets

            var dt1 = GetData("SELECT DimEmployee.FirstName, DimEmployee.LastName, FactSalesQuota.CalendarYear, FactSalesQuota.SalesAmountQuota, DimEmployee.DepartmentName FROM DimEmployee INNER JOIN FactSalesQuota ON DimEmployee.EmployeeKey = FactSalesQuota.EmployeeKey");

            reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", dt1));

            var bytes = reportViewer1.LocalReport.Render("PDF");

            Response.Buffer = true;

            Response.ContentType = "application/pdf";

            Response.AddHeader("content-disposition", "inline;attachment; filename=Sample.pdf");

            Response.BinaryWrite(bytes);

            Response.Flush(); // send it to the client to download

            Response.Clear();

        }

        catch (Exception ex)

        {

        }

    }

}

private DataTable GetData(string query)

{

    var sqlDt = new DataTable();

    var SqlConnection = new SqlConnection(string.Format("Server={0};Database={1};Integrated Security=True", "mdt845", "AdventureWorksDW2008"));

    var sqlCommand = new SqlCommand(query, SqlConnection);

    var sqlDa = new SqlDataAdapter(sqlCommand);

    sqlDa.Fill(sqlDt);

    return sqlDt;

}

Once above page is ready we can actually add a button in our default page which will invoke ExportPDF page and give us our required pdf report.

<input id="Button2" type="button" value="Export to PDF" onclick="exportToPDF()" />

On click of above button we will run following javascript code. Note here that obvious way to call our ExportPDF.aspx page would be window.open but that would flash a popup window and hence instead we would use the form post technique.

<script type="text/javascript">

    function exportToPDF() {

        var form = document.createElement("form");

        form.setAttribute('method', "post");

        form.setAttribute('action', "ExportPDF.aspx");

        document.body.appendChild(form);

        form.submit();

    }

</script>

Once we have all of the above pieces in place following will be our output.

On click of Export to PDF button following pdf file will be generated.

On Similar lines word and excel report also can be generated using report viewer control and RDLC report.

Error in SharePoint 2010 Client Object Model - The request uses too many resources

 

Recently one of our customer faced a weird error “The request uses too many resources”. Everything works fine on my dev & testing environment but its that one customer who faces this error in a module which is implemented using SharePoint Client OM.

On further analysis I found that the error was from the fact that too many commands are sent in one ExecuteQueryAsync call. SharePoint sets a limit of 256 objects in one ExecuteQueryAsync call. This can be confirmed from following powershell call

Get-SPWebApplication | %{$_.ClientCallableSettings}

This can be easily updated using powershell commands

$webApp = Get-SPWebApplication "site"
$webApp.ClientCallableSettings.MaxObjectPaths = 2000
$webApp.Update()

Once above powershell was run my customer was able to get past that error and use the module.

Various cloud (Google / AWS / Windows Azure) storage prices

 

Google Drops Pricing On Cloud Storage 20%, Google is offering a new service called Durable Reduced Availability Storage, a service that allows people to store data at a lower price by limiting its availability. It’s similar to AWS Glacier

Monthly Usage	Old Standard Storage (per GB)	Standard Storage (per GB)
First 0-1 TB	$0.12	$0.095
Next 9TB	$0.105	$0.085
Next 90TB	$0.095	$0.075
Next 400TB	$0.085	$0.07
Additional Storage	Contact us	Contact us

 

Amazon Web Services Drops S3 Storage Service Pricing About 25%

Monthly Usage	Old Standard Storage (per GB)	Standard Storage (per GB)
First 1 TB	$0.125	$0.095
Next 49 TB	$0.110	$0.080
Next 450 TB	$0.095	$0.070
Next 500 TB	$0.090	$0.065
Next 4000 TB	$0.080	$0.060
Over 5000 TB	$0.055	$0.055

 

Microsoft windows Azure Blob storage price as of today (Expect an announcement from MSFT also)

Monthly Usage	Old Standard Storage (per GB)	Standard Storage (per GB)
First 1 TB	$0.125
Next 49 TB	$0.110
Next 450 TB	$0.095
Next 500 TB	$0.090	Wait and watch
Next 4,000 TB	$0.080
Next 4,000 TB	$0.055
Over 9,000 TB	Contact us

Windows Azure storage emulator does not start

If you have installed Windows Azure SDK and stopped your SQL Express service then by default Azure storage emulator on local machine will not start.

Following steps will help you change the SQL instance used Azure storage emulator

1. Click Start, point to All Programs, and then click Windows Azure –> Windows Azure SDK for .net.
2. Right-click Windows Azure SDK Command Prompt, and then click Run as administrator.
3. Type the following command

DSInit /sqlInstance:.

Above command will ensure Azure storage emulator points to default instance of SQL server and not to Express version.

More information about DSInit utility can be found here

SharePoint 2010, Set custom master page in Feature activate event

One of the most common requirement requested is to have custom master page for a SharePoint site. Best place to set the custom master page for a site is Feature activate event. Lets see how this can be done. We will start with a SharePoint empty project and add a master page in it.

Above seen additional templates for SharePoint come from http://cksdev.codeplex.com/

Once a custom master page is added lets add event receiver for the feature. In this event receiver we will add a method SetMasterPage and we will call this method from Feature activate and deactivate event.

private void SetMasterPage(SPWeb web, string masterPage)

{

    var masterUri = new Uri(web.Url + "/_catalogs/masterpage/" + masterPage);

    web.MasterUrl = masterUri.AbsolutePath;

    web.CustomMasterUrl = masterUri.AbsolutePath;

    web.Update();

}

public override void FeatureActivated(SPFeatureReceiverProperties properties)

{

    var parentSite =  properties.Feature.Parent as SPSite;

    if (parentSite != null)

    {

        SPSecurity.RunWithElevatedPrivileges(delegate

        {

            using (var site = new SPSite(parentSite.RootWeb.Url))

            {

                using (var web = site.OpenWeb())

                {

                    web.AllowUnsafeUpdates = true;

                    SetMasterPage(web, "CustomMasterPage.master");

                }

            }

        });

    }

}

Note that by default V4.master is the default master page for a site. We will set this master page as default in our feature deactivate event.

public override void FeatureDeactivating(SPFeatureReceiverProperties properties)

{

    var parentSite = properties.Feature.Parent as SPSite;

    if (parentSite != null)

    {

        SPSecurity.RunWithElevatedPrivileges(delegate

        {

            using (var site = new SPSite(parentSite.RootWeb.Url))

            {

                using (var web = site.OpenWeb())

                {

                    web.AllowUnsafeUpdates = true;

                    SetMasterPage(web, "V4.master");

                }

            }

        });

    }

}

Note – This can also be done through powershell script.