Website for 4th Challenge
The register and login link wont work as the data is being fetched from the SQL SERVER VM:-
A little late..but updating my GITHUB Project link for the 3rd Challenge
Took time to update as my Internet Connection was slow..
Introduction
We see a vast change to world Climate with intervention of adverse Climate effects. Some places have severe winters, excess Summer temperature, draughts, excessive rainfall etc. It is due to the pollution in the atmosphere and other changes (ozone layer depletion), the climate cycle have changed. Now a days we don't keep track of what are the things we are doing or what little changes we can do make world a better place to live in . The best way would be to create an application that keep track of our changes in one go. It can keep updating on facts and figures that will help us and what little subtle changes can we make to reduce the carbon emission.
Background
SETUP
I will be using Windows Azure to build Dynamic websites for the changing world application. The realtime data to fed will be provided by World Bank Climate data API,the general world scenario will be used by BBC World API Beta(Such as updates from The technical program HORIZONS which provides very good information of how new technology can change our World),Carbon Footprint Calculation from Carbon footprint data API.The proposed structure is given below.
Adding BING MAPS
Using Bing maps I can find which areas are proper ways to reduce the carbon emission. It will also the responsibility of the registered members to give information. I will use PUSHPINS to share the concerns of a particular area.
Future Prospects for the App
At adverse conditions say if the conditions are not met for a particular Flora and Fauna to flourish (say in India we are having and extended summer we can suggest ways and engage people to get the best information on which crops can be harvested during that extended summer). We can engage the registered users to use the chat server and suggest ways to come to a common conclusion then update the facts in the websites.
Technologies and frameworks to be used
ASP.net 4.5,MVC,SignalR,Iquery,HTML,Javascript,C#,Jcryption
SDKs and API
Azure SDKs, World Bank Data API and BBC World data API
An insight into Windows Azure
I will be providing details on the technology or the resources that I will be using to build my application
BLOB (Binary Large Object)
blob storage is a storage device that allows you to store massive data such as video and audio into the cloud in a logical structure and offers you some advantages to windows azure does with services, such as availability, scalability and redundancy.
SQL Database
Built on top of a SQL Server technology and offers comparable functionality. The main difference from a developer perspective is that you don't connect to a SQL Server but directly to a database (It is Database as a Service after all)
VM
allows us to deploy an image to windows azure (say Windows Server 2012 VHD) and host it in a hosted service. OS customizations or native applications running in a standalone fashion. It is also known as PaaS (Platform as a service) helps us to migrate existing to OS (applications) to windows azure. It is shown below
Deployment
The next figure shows how to deploy a project in windows azure
Most Important
Windows Azure will help me bringing my application to place without thinking about the operational structure and add value to my product. It will act as a BLACKBOX without caring about the internal delicacies of entire Infrastructure, Platform and Software as service.
A deep dive into information gathered
Carbon Footprint
Definition from the journal Carbon Management
A measure of the total amount of CO2 and Methane emissions of a defined population, system or activity considering all relevant sources, sinks and storage within the spatial and temporal boundary of the population, system or activity of interest. Calculated as carbon dioxide equivalent(CO2e) using the 100 years global warming potential (GWP 100).
Reducing Carbon Footprint
The mitigation of carbon footprints through the development of alternative projects, such as solar or wind energy or reforestation, represents one way of reducing a carbon footprint and is known as carbon offsetting
In the ASP.NET chat room powered by SignalR we can discuss ways with community members on how we can reduce the carbon emission. We can share pics on where trees are being planted etc.
How to use the Carbon Footprint Data and from where I got the idea
I was just checking on ways on how to use the Carbon Footprint Calculator then got the information from this website
Carbon Calculated API
is implemented as JSON over HTTP using just GET Requests. Every Resource like Object Template, generic Object, or Relatable category has their own URL and are manipulated in isolation
API methods
Carbon Calculated API includes the Restful methods to receive data.
BASE URL:-
For every request you need to use the following url structure:-
World Climate Data
As I was more interested in the world climate data and how it impacts us came across the World Bank Climate Data API which provides complete list of how to utilize formats of various factors and give running updates.
What is Climate data API
The Climate data API programmatic access to most of the climate data used on the World Bank's Climate Change Knowledge portal data in real time. Web Developers can use the API to access the Knowledge Portals data in real time to support their own application so long as they abide by World Bank's Terms of use.
About the data
All the data in the Climate Data API are derived from 15 global Circulation Models(GCMS),the most comprehensive physically based models of climate change available and used by the Intergovernmental Panel on Climate Change(IPCC) 4th assessment reports.
The Climate data API uses REST-based requests in which general form looks like
//http://climatedataapi.worldbank.org/climateweb/rest/v/country/type/var/start/end/ISO3
where ISO3 is an ISO3 Country code indicating the country for code request
Why I made up my mind using Jcryption
I was searching the internet on how can I use the data APIs then in StackOverflow I found somebody mentioned ways to use the third party APIs in .NET using Jcryption and I am exploring the opportunities to use it.
Jcryption
is a JavaScript HTML-Form encryption login, which encrypts the POST/GET date that will be sent when you submit a form. Jcryption 2.0 is now able to communicate encrypted with the server. It uses AES for encryption ,RSA is used only for Secure Key Change. How to call it in a form
SignalR Enabled Chat Room
using SignalR to build a ChatRoom where users will communicate with each other sharing views, pics and ways to improve the conditions of World Weather and how can we improve upon controlling the Carbon Emissions, Storing CO2 emitted etc. SignalR also can be used to provide more accurate realtime data in the applications we build.
SQL Database
The Information and the Pics the users share will be kept in the SQL Database. I will update more as I start working on it.
Let's make this world a better place to live in more greener and safer for siming generations to thrive and flourish.
The Second Challenge...Website Creation
The mainpurpose was to create a dynamic website for windows azure.What i used was WEBMATRIX for Windows Azure.I tried lot of thing but found Webmatrix for starters easy to use because i am a novice to Windows Azure Development.The following description gives you ways i implemted for developing the site.
Logging on to Windows Azure
You need to first login to Windows Azure to get yourself going.
The figure shows the main login window
when you login at the bottom you have WEBMATRIX tab.Just click on it to get WEBMATRIX installed in your computer.
It will install all the prerequisites WEBMATRIX installation.The next figure shows the completion phase when Webmatrix is installed in your computer.
What is WEBMATRIX
WebMatrix is a set of free development tools that designed to allow users to easily create dynamic, data drivenweb sites and publish them on the Internet
The tools and technologies included as part of the WebMatrix platform include among others, a database, web server, deployment tool, Search Engine Optimization (SEO) tool and a server-side scripting language. Although all of these tools and technologies are available for download and use as separate entities, Microsoft has combined them into a single stack known as WebMatrix. By doing this a web developer can control the development of all aspects of a web site from within a single environment, greatly reducing complexity. This reduction in complexity realizes many benefits, not least of which are the shortening of development times and a greatly reduced entry barrier – the developer must learn to use just one integrated environment, rather than five or six separate tools.
ADDING WEB HELPER METHODS TO WEBMATRIX
Web Helper methods are important as it allows us to use media content etc for RAZOR engine
the figure shows the nuget to be added as webhelper
the figure shows web helper added
Webmatrix Stack
Creating a site from a template. It allows to add the website directly to azure
Creating a Blank website from a webmatrix template
Different templates that are available for creating the Open Source CMS
The Web Matrix Community
WebMatrix has an active and vibrant on-line community, made up of a wide range of beginners, students, hobbyists and professional developers, where new members are always warmly welcomed. There are several excellent on-line forums available, which are a good place to ask any questions you may have;
These include
The Webmatrix Blogs
What is Razor?
Razor is a simple to use, yet extremely powerful, programming syntax for inserting server-side code into web pages. When you create a new ASP.NET Web Page (.CSHTML or .VBHTML) in WebMatrix, you are creating a file that has two totally separate types of content: client-side and server-side. The client-side content of an ASP.NET Web Page can contain any of the usual code and markup that you would expect to see in a normal HTML page, including HTML, JavaScript, and CSS. The server-side content contains instructions to the web server in C# (or VB.NET), which are used to create dynamic content and interact with other resources, such as databases and file systems
Render Page Model of Razor Engine
Another View
Devloping Websites using Razor.First step creating the CSHTML file.
The steps for getting the website to azure is easy as you have already synched the account just use web matrix to write the code test and directly click the publish button the website will be live.
Using the code
The whole website was built with the default template that consisted of CSHTML pages. Below are the codes used to develop the website using razor engine
The _Layout.cshtml page
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>@Page.Title - My ASP.NET Web Page</title>
<link href="~/Content/themes/base/jquery.ui.all.css" rel="stylesheet" type="text/css" />
<link href="~/Content/Site.css" rel="stylesheet" type="text/css" />
<link href="~/favicon.ico" rel="shortcut icon" type="image/x-icon" />
<script src="~/Scripts/jquery-1.8.2.min.js"></script>
<script src="~/Scripts/jquery-ui-1.8.24.js"></script>
<script src="~/Scripts/modernizr-2.6.2.js"></script>
<meta name="viewport" content="width=device-width" />
</head>
<body>
<header>
<div class="content-wrapper">
<div class="float-left">
<p class="site-title"><a href="~/">@Video.Flash(path: "animation/fg1.swf",
width: "300",
height: "400",
play: true,
loop: true,
menu: false,
bgColor: "red",
quality: "medium",
scale: "exactfit",
windowMode: "transparent")</a></p>
</div>
<div class="float-right">
<section id="login">
@if (WebSecurity.IsAuthenticated) {
<text>
Hello, <a class="email" href="~/Account/Manage" title="Manage">@WebSecurity.CurrentUserName</a>!
<form id="logoutForm" action="~/Account/Logout" method="post">
@AntiForgery.GetHtml()
<a href="javascript:document.getElementById('logoutForm').submit()">Log out</a>
</form>
</text>
} else {
<ul>
<li><a href="~/Account/Register">Register</a></li>
<li><a href="~/Account/Login">Log in</a></li>
</ul>
}
</section>
<nav>
<ul id="menu">
<li><a href="~/">Home</a></li>
<li><a href="~/About">About</a></li>
<li><a href="~/Contact">Contact</a></li>
</ul>
</nav>
</div>
</div>
</header>
<div id="body">
@RenderSection("featured", required: false)
<section class="content-wrapper main-content clear-fix">
@RenderBody()
</section>
</div>
<footer>
<div class="content-wrapper">
<div class="float-left">
<p>© @DateTime.Now.Year - My ASP.NET Web Page</p>
</div>
</div>
</footer>
@RenderSection("Scripts", required: false)
</body>
</html>
The code for default.cshtml page.
<pre>@{
Layout = "~/_SiteLayout.cshtml";
Page.Title = "Home Page";
}
<h1>Carbon Emission related tweets....</h1>
@{
var client = new WebClient();
var json = client.DownloadString("http://search.twitter.com/search.json?q=carbon emission");
var search = Json.Decode(json);
}
@foreach(var result in search.results){
<p><strong>@result.from_user (@result.from_user_name)</strong><br />
@result.text<br />
<em>@DateTime.Parse(result.created_at).ToString("F")</em>
</p>
}
@section featured {
<section class="featured">
<div class="content-wrapper">
<hgroup class="title">
<h1>@Page.Title.</h1>
<h2>Why the CLimate condition is affecting us?.</h2>
</hgroup>
<h1>CARBON FOOTPRINT INFORMATION</h1>
<p>A carbon footprint has historically been defined by Championne as "the total
sets of greenhouse gas (GHG) emissions caused by an organization, event, product or person."[1]
However, calculating the total carbon footprint is impossible due to the large amount of data
required and the fact that carbon dioxide can be produced by natural occurrences. It is for this reason
that Wright, Kemp, and Williams, writing in the journal Carbon Management, have suggested a more practicable definition:
"A measure of the total amount of carbon dioxide (CO2) and methane (CH4) emissions of a defined
population, system or activity, considering all relevant sources, sinks and storage within the spatial
and temporal boundary of the population, system or activity of interest. Calculated as carbon dioxide
equivalent (CO2e) using the relevant 100-year global warming potential (GWP100)."[2]
Greenhouse gases can be emitted through transport, land clearance, and the production and consumption
of food, fuels, manufactured goods, materials, wood, roads, buildings, and services.[3] For simplicity
of reporting, it is often expressed in terms of the amount of carbon dioxide, or its equivalent of other GHGs, emitted.
Most of the carbon footprint emissions for the average U.S. household come from "indirect"
sources, i.e. fuel burned to produce goods far away from the final consumer. These are distinguished
from emissions which come from burning fuel directly in one's car or stove, commonly referred to as
"direct" sources of the consumer's carbon footprint.[4]
The concept name of the carbon footprint originates from ecological footprint,discussion,[5] which was developed
by Rees and Wackernagel in the 1990s which estimates the number of "earths" that would theoretically be required
if everyone on the planet consumed resources at the same level as the person calculating their ecological footprint. However,
carbon footprints are much more specific than ecological footprints since they measure direct emissions
of gasses that cause climate change into the atmosphere.
Measuring Carbon Footprints
An individual's, nation's, or organization's carbon footprint can be measured by undertaking a GHG emissions
assessment or other calculative activities denoted as carbon accounting. Once the size of a carbon footprint is known,
a strategy can be devised to reduce it, e.g. by technological developments, better process and product management,
changed Green Public or Private Procurement (GPP), carbon capture, consumption strategies, and others. Several
free online carbon footprint calculators exist, with at least one supported by publicly available peer-reviewed
data and calculations from the University of California, Berkeley's CoolClimate Network research consortium.[6][7]
The mitigation of carbon footprints through the development of alternative projects, such as solar or wind energy
or reforestation, represents one way of reducing a carbon footprint and is often known as Carbon offsetting.
The main influences on carbon footprints include population, economic output, and energy and carbon intensity
of the economy.[8] These factors are the main targets of individuals and businesses in order to decrease carbon
footprints. Scholars suggest the most effective way to decrease a carbon footprint is to either decrease the amount
of energy needed for production or to decrease the dependence on carbon emitting fuels.[8]
</p>
</div>
</section>
}
<h3>We suggest the following:</h3>
<ol class="round">
<li class="one">
<h5>CHANGING WORLD SCENARIO</h5>
This sit from NASA gives you the Climate conditions as per NASA details.
<a href="http://climate.nasa.gov/">Learn more…</a>
</li>
<li class="two">
<h5>CARBON FOOTPRINT CALCULATION</h5>
this website allows you to calculate carbon footprint.
<a href="http://www.carbonfootprint.com/calculator.aspx">Calculate..</a>
</li>
</ol>
The about.cshtml page code
<pre>
@{
Layout = "~/_SiteLayout.cshtml";
Page.Title = "About";
}
<h1>Carbon FootPrint related tweets....</h1>
@{
var client = new WebClient();
var json = client.DownloadString("http://search.twitter.com/search.json?q=carbonfootprint");
var search = Json.Decode(json);
}
@foreach(var result in search.results){
<p><strong>@result.from_user (@result.from_user_name)</strong><br />
@result.text<br />
<em>@DateTime.Parse(result.created_at).ToString("F")</em>
</p>
}
<hgroup class="title">
<h1>@Page.Title.</h1>
<h2>The important Information.</h2>
</hgroup>
<article>
<p>
@Video.Flash(path: "animation/1.swf",
width: "400",
height: "600",
play: true,
loop: true,
menu: false,
bgColor: "red",
quality: "medium",
scale: "exactfit",
windowMode: "transparent")
</p>
</article>
<aside>
<h3>Aside Title</h3>
<p>
Will be updated with more data
</p>
<ul>
<li><a href="~/">Home</a></li>
<li><a href="~/About">About</a></li>
<li><a href="~/Contact">Contact</a></li>
</ul>
</aside>
</p>
<p />
The code for Contact.cshtml page
<pre>@{
Layout = "~/_SiteLayout.cshtml";
Page.Title = "Contact";
}
<h1>worldweather related tweets....</h1>
@{
var client = new WebClient();
var json = client.DownloadString("http://search.twitter.com/search.json?q=worldweather");
var search = Json.Decode(json);
}
@foreach(var result in search.results){
<p><strong>@result.from_user (@result.from_user_name)</strong><br />
@result.text<br />
<em>@DateTime.Parse(result.created_at).ToString("F")</em>
</p>
}
<hgroup class="title">
<h1>@Page.Title.</h1>
<h2>Your contact page.</h2>
</hgroup>
<section class="contact">
<header>
<h3>Phone:</h3>
</header>
<p>
<span class="label">Main:</span>
<span>+917278485517</span>
</p>
<p>
@Video.Flash(path: "animation/fg1.swf",
width: "500",
height: "600",
play: true,
loop: true,
menu: false,
bgColor: "red",
quality: "medium",
scale: "exactfit",
windowMode: "transparent")
</p>
</section>
<section class="contact">
<header>
<h3>Email:</h3>
</header>
<p>
<span class="label">Info:</span>
<span><a href="mailto:abhishek.nandy81@gmail.com">abhishek.nandy81@gmail.com</a></span>
</p>
<p>
<span class="label">General:</span>
<span><a href="mailto:abhishek.nandy81@gmail.com">abhishek.nandy81@gmail.com</a></span>
</p>
</section>
<section class="Complete Earth Information">
<header>
<h3><a href="http://www.breathingearth.net/"
title="Breathing Earth Live Data">http:
</header>
</section>
Things tried but missed
I tried with responsive UI but somehow could not figure out why it was not reflecting in my website.I tried with the BootStrap API of Nuget package and was noyt getting the desired result might be that i have to know more about Responsive UI but I promise i will experiment and show the results.I was unable to integrate the SignalR chatroom(I am trying.)
Things happened by chance.
Integration of Twitter search API was able to retrieve posts related to Carbon Footprint,Carbon emission etc.
The final result
The website is hosted. It does follwing things. The HOME PAGE gives you info on Carbon Footprint. Tweets regarding Carbon emission.It will redirect you to a page where you can calculate Carbon Footprint. Another link takes you to the NASA World Climate weather.
About Page
Allows users to get tweets related to carbon footprint
Contact Page
shows tweets related to world weather. another link takes you to to the breathing earth.net page where you get total world scenario.
The Website as a result
http://ed1981.azurewebsites.net/[^]
I will be including the whole solution in next few days.
Whats next
This challenge has given me to learn more and more.It is surprise developer challenge and with a work of 5 days to get the site done was very helpful for me. I will try harder in next few weeks.
"AIM FOR STARS ATLEAST YOU WILL REACH A PLANET"
till my next update Bye.
The SQL Azure Challenge
As i have very less knowledge in Databases gearing up for this challenge to know more.My idea is to learn about SQL databases first and then experiment with it.
Resources required:
1)First of all we need to create a database with Windows Azure Account
2)Need to install SQL server 2012 express edition in the computer.
3)Have to get through the flow of SQL Azure.
4)Check how data binds in Cloud
The whole experience will be new one. I will Start the journey tommorrow...
Facts about SQL Azure
Its a Microsoft Technology based on SQL Server 2012 placed in the cloud platform.SQL Azure allows you to store data make relational queries against stored data which can be structured or unstructured.
More updates to follow..
What is SQL Azure?
SQL Azure is a Cloud base subset of SQL Server.It has a special backend technology provided by Microsoft and high availability by using CRUD operation in databases.SQL Azure uses T-SQL as the query language and Tabular Data Stream (TDS) as the protocol to access the service over internet.It uses an XML-based format for data transfer
Limitations of SQL Azure comapred with SQL Server
SQL Azure Offers No SQL CLR Support
It doesn't provide support for the CLR. Any databases that are built using CLR objects will not be able to be moved to SQL Azure without modification
Objects Certain objects available in SQL Server aren’t available in SQL Database. If your applications depend heavily on these features, you may have difficulty using SQL Database, and you may need to rethink your application design to accommodate these limitations.
The following are some of the limitations that currently apply to SQL Database:
• CLR. The.NET CLR isn’t available in SQL Database. As a result, you can’t create extended stored procedures or extended functions.
• System functions. SQL Database supports many system functions, including Aggregate functions and Ranking functions.
However, SQL Database doesn’t support RowSet functions, including these:
- OPENQUERY
- OPENXML
- OPENROWSE
- OPENDATASOURCE
- System stored procedures.
Only a small subset of system stored procedures are available in SQL Database, in the following categories:
- Catalog stored procedures
- Database engine stored procedures
- Security stored procedures
- System tables.
None of the system tables are available.
- System views. A subset of system views is available; you can access some of them from the master database and others from user databases.
The following are some of the system views available (for a complete list, refer to the online MSDN library for SQL Database):
- sys.sql_logins
- sys.views
- sys.databases
- sys.columns
- sys.objects
- Heap tables. SQL Database doesn’t allow the use of heap tables. All tables must have a clustered index.
Starting the experiment with SQL Azure
As i am very new to SQL as a whole i will start by just checking in.Exploring the SQL Azure tab is the most important thing
Have a look at the SQL Azure I have added a figure below
One thing i found out while using Windows Azure in Windows8
As SQL Azure is powered by Silverlight the manage option of SQL Azure will open in Desktop mode with google chrome you cannot access it in Windows 8 UI mode(Tiles Window)
Loggin in to the console
To do any activity we need to add data to SQL Azure.Start with creating a table
add data to it
Query against the database
SELECT * from toy
Now Click on run to execute query
Connecting the SQL Azure Database from SQL SERVER 2012 Management Studio
We can easily connect to the SQL Azure database while specifying the name of the server at the login.Click on Connect
Querying against the database.As we logged in to the Management studio we start by qurying the SQL Azure database.The T-Sql given below
SELECT @@version
The result will be the current version of the database.
A bit deeper creating tables,Indices and queries
The next query will create a table Contact
CREATE TABLE [Contact](
[ContactID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Title] [nvarchar](8) NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[EmailAddress] [nvarchar](50) NULL,
[Phone] [nvarchar](30) NULL,
[Timestamp] [timestamp] NOT NULL
)
adding index to the email address field
CREATE INDEX IX_Contact_EmailAddress
ON Contact(EmailAddress)
Adding a row to the new contact table
INSERT INTO [Contact]
([Title],[FirstName],[LastName],[EmailAddress],[Phone])
VALUES
('Mr','Santabanta','Songe','san@banta.com','444-1234-1111')
Lets do something new by showing the execution path or plan
Need to use SHOWPLAN_ALL option
SET SHOWPLAN_ALL ON
GO
SELECT * FROM Contact WHERE EmailAddress ='san@banta.com'
GO
SET SHOWPLAN_ALL OFF
To see how the data behaves we need to add more data to the table(or shall we say to play with it).We need to create stored procedure
CREATE PROCEDURE AddData
@NumRows int
AS
DECLARE @counter int
SELECT @counter = 1
WHILE (@counter < @NumRows)
BEGIN
INSERT INTO [Contact]
([Title],[FirstName],[LastName],[EmailAddress],[Phone])
VALUES
('Mr','santabanta','doom',CAST(@counter as nvarchar)+'san@banta.com','444-1234-1111')
SELECT @counter = @counter + 1
END
Adding large chunk of data
now we add 10,000 rows to see how the data behaves with the database just for an experiment
EXEC AddData 10000
This query will show the execute plan
SET SHOWPLAN_ALL ON
GO
SELECT * FROM Contact WHERE EmailAddress ='san@banta.com'
GO
SET SHOWPLAN_ALL OFF
The dynamic IP Address issue i have faced
The IP address that is provided by the Broadband changes dynamically(for DHCP protocol)you will get an error if you try to login if the PC gets disconnected from Internet.The Solution :- need to add Firewall rules to the database.
The power of CTRl+L
When we hit CTRL+L in the management studio we get the total execution plan GUI wise.
Conditions that will terminate the data connections
There are certain conditions when the data connection gets terminated following are the scenarios
• Lock Consumption. If your application consumes more than 1 million locks, you will get error code 40550. Monitoring the sys.dm_tran_locks management view provides the state of your locks.
• Uncommitted Transactions. A transaction locking internal resources for more than 20 seconds will be terminated with error code 40549.
• Log File Size. If the log file for a single transaction exceeds 1GB in size, the connection will be terminated with error code 50552.
• TempDB. If you run large transactions, or large batches of commands, or a large sorting operation consuming more than 5GB of space, the session will be terminated with error code 40551.
• Memory. If your statements consume more than 16MB of memory for over 20 seconds, your session will be terminated with error code 40553.
• Database Size. If the database exceeds its configured maximum size, any attempt to update or insert data will fail with error code 40544. You can resolve this error by dropping indexes, purging tables, or increasing the size of the database using the ALTER DATABASE statement.
• Idle Connections. Any connection staying idle for more than 30 minutes will be terminated. No error is returned in this case.
• Transactions. Transactions lasting more than 24 hours will be terminated with error code 40549.
• Denial-of-Service Attacks. If many login attempts fail from a specific IP address, any attempt to connect from that IP address will fail for a period of time in order to protect the SQL Database service. No error code is returned.
• Network Problems. If a network problem is the source of a terminated session, you will not receive a specific error code from SQL Database; however, you may receive Socket errors.
• Failover. When SQL Database is in the midst of failing your database over to another node, your active sessions will be disconnected. You may receive a Socket exception or a generic error indicating that you should retry your operation.
• High Activity. If the server hosting your database experiences significant stress or exceeds its operating boundaries, such as too many busy workers, SQL Database might disconnect the sessions with error code 40501.
Transient Errors
• Error 20. The instance of SQL Server you attempt to connect to does not support encryption.
• Error 64. A connection was successfully established with the server, but then an error occurred during the login process: provider: TCP Provider, error: 0 - The specified network name is no longer available
• Error 233. The client was unable to establish a connection because of an error during the initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server: provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.
• Error 10053. A transport-level error has occurred when receiving results from the server. An established connection was aborted by the software in your host machine
. • Error 10054. A transport-level error has occurred when sending the request to the server: provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.
• Error 10060. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections: provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
Checking for the firewall rules currently there
SELECT * FROM sys.firewall_rules
Experimenting with backup and restore
Exploring DAC
DAC Fx is a set of tools, APIs, and services designed to improve the development, deployment and management of SQL Server database schemas. In most environments, the DBA creates and maintains sets of T-SQL scripts that create or alter database objects, such as tables and stored procedures. The issue is that in many instances the DBAs need to maintain multiple sets of scripts; one for the initial creation and another for updates and modifications that update the database from one version to another. Add on top of that multiple versions of those scripts, and you have the makings of a complex maintenance scenario. This is where DAC Fx comes in. Instead of building and maintaining sets of T-SQL scripts, SQL Server 2008 R2 introduced the concept of a BACPAC. A DAC, or Data-Tier Application, is an entity that contains all of the database objects used by an application. Its purpose is to provide a single unit for creating, deploying, and managing objects instead of managing each object individually. When a DAC is ready to be deployed, it is built into a DAC package, or DACPAC. This package is a file that contains the DAC definition, and is simply a zip file containing multiple XML files
Deploying Directly To SQL Azure
Right Click on database and then click on Tasks and then click on deploy directly to SQL Azure
More updates to come...
Copying a Database..
Copying a database allows us to add another instance of the already deployed database
CREATE DATABASE ed12 AS COPY OF servername.ed11
ENZO BACKUP
You need to register for the free usage of the enzo backup.download the software.Link it to BLOB and you will get a free license.
press CTRL+B to open the backup window
The Link to download the software..
http://www.bluesyntax.net/DownloadBackup20.aspx
Experimenting with the adventure works database
First download it from the codeple site LINK http://msftdbprodsamples.codeplex.com/releases/view/55330
Figuratively shown below
A big problem!! I was unable to attach the Adventure Works database
I was trying to attach the Adventure Works Database MDF file but was unable to do so.I was getting error,searched it in Internet and found a solution here:-http://www.mssqltips.com/sqlservertip/2638/attach-sample-database--adventureworks-in-sql-server-2012/ .Dropping the log file added the database!! hurrah the database was added.
Adding the Database to SQL Azure
Now as we have a big database we can try putting the database back to SQL Azure.Let's give it a try.We need to follow the wizard and the SQL Azure Database.
Another error Somehow the DATA APPLICATION FRAMEWORK was not working with Adventure Works database
had to try another method created a small database to repeat the same steps(Might be that all queries are not matched with SQL Azure).Small data did the trick.The operation was completed.The BACPAC file at this point can be archived, deleted, or used for other import services. In this simple example, the BACPAC was stored locally. However, best practices state that if you will be importing the database to multiple servers or multiple times, you’ll want to store the BACPAC in Windows Azure storage
Exporting and Importing to SQL Azure via a BACPAC using SQL Server 2012
DAC Export
Need to Click on the Service Database and then on Tasks then Export Backup.Once the export has completed successfully, click Close. At this point, you have a BACPAC that you can then import to one or multiple SQL Azure servers. Granted, you can’t import to multiple SQL Azure servers simultaneously, but unlike the Deploy Database to SQL Azure option, with the Export and Import functionality, you can import the BACPAC multiple times without rerunning the export step again. It’s now time to import that DAC package (BACPAC) to SQL Azure.
DAC IMPORT
Connect to your SQL Azure server in SQL Server Management Studio, and in the Object Explorer window right-click the Databases node and select Import Data-Tier Application from the context menu
More backup Time
USING ENZO BACKUP
need to install the Enzo Backup Cloud Software.I Installed for experiment the 10 days trial version.When you login to Enzo Backup you need to mention the storage name as well as key details.
Enzo backup asks for the storage name and values so that the Enzo backup software can link to SQL AZURE.The steps are shown in the figures below.
Now the next figure shows how enzo backup software connects to SQL AZURE database
The next figure shows the result when connection is successfull.
The next figure shows selecting the SQL database.
The solution when the ENZO Backup is updated.
Open up a Cloud Service project
Before we need to add a WebRole.Adding a grid view to the database.
Connecting to the SQL Azure Database
Creating the package
The package and configuration file is created.
The simple deployment strategy
Deploying the project to Cloud
With figures are shown the steps to deploy the solution to Cloud...Be Careful (As it is an experiment work i have not put the solution to Staging Environment..then on you will be charged)
Select the local package and configuration files
uploading the package..
The packaging initiated..
Th total structure formed
The Journey continues..
After all i have gathered SQL Azure has a lot to offer being a subset of SQL Server and with more use people will be well versed with the integration with cloud.I had little knowledge about SQL when i started the article and with time span i have gained some.This is my little experimental effort(as i was clueless what to do in this challenge?) to discover SQL Azure and i hope my knowledge touched some topics..After all there is a say that Knowledge is Strength.. Till next time bye...
4th Challenge VM
Idea
the idea here was to create an application that utilizes VM and fetches data and provide the result as an output
The situation was perfect for me to try binding data from the world bank website(most authentic data) for climate change,CO2 emission etc. The only point was how to do it with the help of Windows Azure VM
Progressive steps taken
Step 1:-Creation of VM
Step2:-Accessing the data from World Bank Website
Step3:-Converting the excel to SQL Database
Step4:-Ways to access the data from .Net application
Step5:-Showing the data in the website
Tools Required:- Excel Viewer for the VM,VS 2012 express for the website creation
The Plan
I have undertaken two paths one succesfull one other unsuccessfull one.In my first update i will discuss the successfull one.
Lets Start
Log on to Windows Azure Management Portal.Click on the link where you have Virtual Machine option.Then you need to click on create a VM
In the next page you have the option new option-> go to Compute->VM->Quick Create
In my case i am creating a VM for SQL Server 2012 so i selected SQL SERver 2012 SP1 Enterprise on Windows Server 2008 R2 SP1
In the next figure when the step is done and when you select that arrow sign you will see the VM Starting
In the above figure you have seen that as the VM starts it comes in running state.
At the bottom we have the appbar we have the options for the VM. i have mentioned it figuratively
As you have created the VM to get the details of it you need to click on it.
For managing the end points of the VM we need to click on Endpoints.
When you get to click on add endpoint by default you will see the add Endpoint selected.If not
select ADD endpoint option then click on the encircled arrow to continue.
Here you need to mention the details of the endpoints the SQl Servername1 Protocol should be
TCP and mention the port.Select the private port as 1433 it's important.Put in the public port.
The details will be updated.
To get into the VM you need to click on connect.
You will get the RDP request file you need to open it.
You need to click on connect RDP initialization will start.
Put in your credentials username and password
After that it takes you to the main VM.
Before creating our environment for Vm let us analyse the process after you start connecting the VM
with RDP. You get a certificate lets see it figuratively.
We can see the details of the certificate (Like encryption technique, here it is RSA).More details
regarding validity of certificate etc is also given.
You can also import the certificate at local computer.
The import wizard will continue.
The import wizard completes.
As we have created the Windows Azure SQL Server VM we can check the statistcs on the
Windows Azure Management Portal Dashboard.Have a look at the Statistics.
Lets get back now to the RDP connection as you key in your username and password it will take you
to the VM.The view
Now for our purpose we need to launch Microsoft SQL Server 2012
You need to click on Configuration Tools then click on SQl Server Configuration Manager
Check on the SQL Server Network Configuration see that shared memory and TCP/IP protocol
is enabled.
After changing the values as we have done in the previous steps we need to restart the
MSSQL Server.
We need to import a new policy so we need to click on Windows Firewall and Advanced Security.
Right click on the inbound rules and Click on new rule
Check on rule type.Click on port and then next
Rule should be for TCP.Specified Port no. should be 1433.
Then on Action click on allow the connection and then next.
Click on Finish.
Open up the World Bank Website at the VM and then click on Climate Change.Download the data
in excel format.
The website link
http://data.worldbank.org/topic/climate-change
The easy way to access the website on VM was installing the Google Chrome.
Open up the SQL Server 2012 database.Right Click and Click on new database.
Name it as Wbapi(i named it so.)
The Most Important thing now:-
importing the excel data.Right click on the database created.Click on Tasks and then Import data.
It will take you to the SQL Server Import and Export wizard and then click on next.
On the data Source option select Microsoft excel and then next.
now we need to select the excel file that we have saved from World Bank Website .Browse it
and check the version of the excel (in my case it is 97-2003 format)
Then click on copy data from one or more tables.
Select the Source
You can edit the SQl query to involve indexing but i have left it as it is.
Then Click on Run immediately and then next.
Get through the details of the wizard and then click on finish.
Next Step:-
Establishing the connection to the Vm from SQL Server 2012 management studio.
i had go through internet so that i had to access the data in VM from ASP.Net application.
This article in internet helped me get things sorted out.
http://www.windowsazure.com/en-us/manage/windows/common-tasks/install-sql-server/
As i went through the article i started my voyage again.Steps mentioned below.
Login to the VM and then get into the SQL SERVER 2012 management studio.We had to create
a new user so went to security options and the hit new and then login.
In the server roles select sysadmin
In the security tab check that the server authentication mode is selected as SQL Server and
windows Authentication mode and login auditing as Failed logins only.
right click the server on object expolorer and click on restart.
The most important now is to access the SQL Server VM from the local copy of SQL Server 2012.
Login to the SQL Server 2012 Management Studio at local machine.Put in the VM name as your
server name("****.cloudapp.net").Authentication type will be SQL Server authentication ,login
would be the login name we craeted at the VM(in our case it is Testde and key in your password).
Then Click on connect(Everything working in favour we will login to VM)
HALF JOB DONE...
login to the VM and if Google chrome is not installed install it. lets show a snapshot
With the Google chrome installed we downloaded and converted the excel files to SQL Database
as mentioned above(Do it for Agriculture and Rural development,Environment as well as CO2
emissions)
WebLinks of the World Bank data sets
http://data.worldbank.org/topic/environment
http://data.worldbank.org/indicator/EN.ATM.CO2E.PC
The main link:-
http://datacatalog.worldbank.org/
Shown in images the steps where we acquired the data.
Create a new database at SQL Server instance of the VM.Right click on the server.Click on new
database.
Name the database.My instance wa wbapi.Click on OK.
Repeat the steps to create two more databases wbapi2 and wbapi3
For data to sync with VM and asp.net we have the Website in Windows Azure Management portal
through which the VM will be connected and data will be fetched.
Now we will start creating the website that will be synched with the Windows Azure Website.Open
VS2012 at admin mode.Click on new project and select ASP.net web forms application.
The default web form application will have three aspx webpages:-
Default.aspx,About.Aspx and Contact.aspx we will add the functionality to these pages.Enter the data and select
Grid View from Toolbox.
Choose data source click on new data source.
Select SQL Database mention ID and in our case it is SQL DataSource1 and then click ok.
Put in the Server name <VMNAME>.Select the Sql Server authentication.Username as created
on the VM and then put in password select the instance of the database(wbapi).Test
Connection.
Another Job done..
First we did it for default page same should be done for About and contact pages.Three connection
strings will be there for instances of the databases.(wbapi,wbapi2,wbapi3)
The connection were named as
wbapi connection string,wbapi2 connection string,wbapi3 connection string
Compile the project.Download the publish profile from windows azure portal
After compiling the VS 2012 project hit publish(import the publish settings and validate the
connection)
At the setting select the appropriate connection string
You will get all the details and then click on publish.
As there is large amount of data associated with the project publishing takes time once it is done
the website will be opened.
The screenshots of the main pagesof my website
After all experiments successfull and unsuccessfull ones my Azure Quota Stand as follows
Lets check on the help option available for at Windows Azure Portal.
Updated all of them in following figures very good to know information.
Finally I took advantage of the CodeProject article offline editor very useful(people who haven't tried
can have a look)
Link:-
http://www.codeproject.com/Articles/468625/Offline-Article-Editor-For-CodeProject?fid=1791171&fr=26#xx0xx
Objective achieved
1)Creating a SQL Server VM on Windows Server 2008 r2
2)Converted the excel data to SQL database
3)Created an active connection between ASP.NET application and Windows Azure SQL Server VM.
VM CHALLENGE NOTES
Windows Azure VM is easy to use and host different kind of applications.It can be used to create very realtime
application using data analytics.With the trial package of 90 days(currently 30 days & 200$ usage) we can exper
iment with different platform VMs.The VMs can be of different OS currently supportive OS are Windows,Linux(Ubuntu,
CentOS etc) image with Node.js for MAc(OSX)
Gathering more knowledge from the Windows Azure Developer Kit can be useful.Explore the opportunities with the labs
provided in it.
LINK TO DOWNLOAD:-
http://www.windowsazure.com/en-us/downloads/]
"The transformation from real world to virtual world and viceversa is an interesting one"
Till my next update Bye..
