European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

European FREE ASP.NET 4.5 Hosting UK - HostForLIFE.eu :: Sending additional form data in multipart uploads with ASP.NET Web API

clock March 20, 2014 06:10 by author Peter

If you've used ASP.NET MVC you'll be used to being able to easily process multipart form data on the server by virtue of the ASP.NET MVC model binder. Unfortunately things are not quite so simple in ASP.NET Web API. Most of the examples I have seen demonstrate how to upload a file but do not cover how to send additional form data with the request. The example below (taken from the Fabrik API Client) demonstrates how to upload a file using HttpClient:

public async Task<IEnumerable<MediaUploadResult>> UploadMedia(int siteId, params UploadMediaCommand[] commands)

{

    Ensure.Argument.NotNull(commands, "commands");

    Ensure.Argument.Is(commands.Length > 0, "You must provide at least one file to upload.");

    var formData = new MultipartFormDataContent();

    foreach (var command in commands)

    {

        formData.Add(new StreamContent(command.FileStream), command.FileName, command.FileName);

    }

    var request = api.CreateRequest(HttpMethod.Post, api.CreateRequestUri(GetMediaPath(siteId)));

    request.Content = formData;

    var response = await api.HttpClient.SendAsync(request).ConfigureAwait(false);

    return await response.Content.ReadAsAsync<IEnumerable<MediaUploadResult>>().ConfigureAwait(false);

}

The UploadMediaCommand passed to this method contain a Stream object that we've obtained from an uploaded file in ASP.NET MVC. As you can see, we loop through each command (file) and add it to the MultipartFormDataContent. This effectively allows us to perform multiple file uploads at once. When making some changes to our API recently I realized we needed a way to correlate the files we uploaded with the MediaUploadResult objects sent back in the response. We therefore needed to send a unique identifier for each file included in the multipart form.

Since the framework doesn't really offer a nice way of adding additional form data to MultiPartFormDataContent, I've created a few extension methods below that you can use to easily send additional data with your file uploads.

/// <summary>

/// Extensions for <see cref="System.Net.Http.MultipartFormDataContent"/>.

/// </summary>

public static class MultiPartFormDataContentExtensions

{      

    public static void Add(this MultipartFormDataContent form, HttpContent content, object formValues)

    {       

        Add(form, content, formValues);

    }

    public static void Add(this MultipartFormDataContent form, HttpContent content, string name, object formValues)

    {

        Add(form, content, formValues, name: name);

    }

     public static void Add(this MultipartFormDataContent form, HttpContent content, string name, string fileName, object formValues)

    {

        Add(form, content, formValues, name: name, fileName: fileName);

    }

    private static void Add(this MultipartFormDataContent form, HttpContent content, object formValues, string name = null, string fileName = null)

    {        

        var header = new ContentDispositionHeaderValue("form-data");

        header.Name = name;

        header.FileName = fileName;

        header.FileNameStar = fileName;

        var headerParameters = new HttpRouteValueDictionary(formValues);

        foreach (var parameter in headerParameters)

       {

            header.Parameters.Add(new NameValueHeaderValue(parameter.Key, parameter.Value.ToString()));

        }

         content.Headers.ContentDisposition = header;

        form.Add(content);

    }

}

With these extensions in place I can now update our API client to do the following:foreach (var command in commands)

{

    formData.Add(

        new StreamContent(command.FileStream),

        command.FileName, command.FileName,

        new {

            CorrelationId = command.CorrelationId,

            PreserveFileName = command.PreserveFileName

        }

    );

}

This sets the content disposition header like so:

Content-Disposition: form-data;

    name=CS_touch_icon.png;

    filename=CS_touch_icon.png;

    filename*=utf-8''CS_touch_icon.png;

    CorrelationId=d4ddd5fb-dc14-4e93-9d87-babfaca42353;

    PreserveFileName=False

On the API, to read we can loop through each file in the upload and access the additional data like so:

foreach (var file in FileData) {

    var contentDisposition = file.Headers.ContentDisposition;

    var correlationId = GetNameHeaderValue(contentDisposition.Parameters, "CorrelationId");

}

Using the following helper method:

private static string GetNameHeaderValue(ICollection<NameValueHeaderValue> headerValues, string name)

{          

    if (headerValues == null)

        return null;

    var nameValueHeader = headerValues.FirstOrDefault(x => x.Name.Equals(name, StringComparison.OrdinalIgnoreCase));

    return nameValueHeader != null ? nameValueHeader.Value : null;

}

In case you were interested below is the updated code we are using to process the uploaded files within ASP.NET MVC:

[HttpPost]

public async Task<ActionResult> Settings(SiteSettingsModel model)

{

    await HandleFiles(new[] {

        Tuple.Create<HttpPostedFileBase, Action<string>>(model.LogoFile, uri => model.LogoUri = uri),

        Tuple.Create<HttpPostedFileBase, Action<string>>(model.IconFile, uri => model.IconUri = uri ),

        Tuple.Create<HttpPostedFileBase, Action<string>>(model.FaviconFile, uri => model.FaviconUri = uri)

    });

    await siteClient.UpdateSiteSettings(Customer.CurrentSite, model);

    return RedirectToAction("settings")

        .AndAlert(AlertType.Success, "Success!", "Your site settings were updated successfully.");

}

private async Task HandleFiles(Tuple<HttpPostedFileBase, Action<string>>[] files)

{

    var uploadRequests = (from file in files

                          where file.Item1.IsValid() // ensures a valid file

                          let correlationId = Guid.NewGuid().ToString()

                          select new

                          {

                              CorrelationId = correlationId,

                              Command = file.Item1.ToUploadMediaCommand(correlationId),

                              OnFileUploaded = file.Item2

                          }).ToList();

    if (uploadRequests.Any())

    {

        var results = await mediaClient.UploadMedia(Customer.CurrentSite,

            uploadRequests.Select(u => u.Command).ToArray());

         foreach (var result in results)

        {

            // find the original request using the correlation id

            var request = uploadRequests.FirstOrDefault(r => r.CorrelationId == result.CorrelationId);

            if (request != null)

            {

                request.OnFileUploaded(result.Uri);

            }

        }

   }

}



SQL Server Hosting France - HostForLIFE.eu :: SQL String concatenation with CONCAT() function

clock March 10, 2014 08:05 by author Peter

We have been using plus sign (+) operator for concatenating string values for years in SQL Server with its limitations (or more precisely, its standard behaviors). The biggest disadvantage with this operator is, resulting NULL when concatenating with NULLs. This can be overcome by different techniques but it needs to be handled. Have a look on below code;

 -- FullName will be NULL for 
 -- all records that have NULL 

 -- for MiddleName 
 SELECT  
   BusinessEntityID 
   , FirstName + ' ' + MiddleName + ' ' + LastName AS FullName 
 FROM Person.Person 
 -- One way of handling it 
 SELECT  
   BusinessEntityID 
   , FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName AS FullName 
 FROM Person.Person 
 -- Another way of handling it 
 SELECT  
   BusinessEntityID 
   , FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + LastName AS FullName 
 FROM Person.Person 

SQL Server 2012 introduced a new function called CONCAT that accepts multiple string values including NULLs. The difference between CONCAT and (+) is, CONCAT substitutes NULLs with empty string, eliminating the need of additional task for handling NULLs. Here is the code.

 SELECT  
   BusinessEntityID 
   , CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName 
 FROM Person.Person 

If you were unaware, make sure you use CONCAT with next string concatenation for better result. However, remember that CONCAT substitutes NULLs with empty string which is varchar(1), not as varchar(0).



SQL Server 2012 Spain Hosting - HostForLIFE.eu :: Configure a SQL Server Alias for a Named Instance

clock March 5, 2014 05:09 by author Peter

There are plenty of tutorials out there that explain how to configure an MS SQL Server alias. However, since none of them worked for me, I wrote this post so I'll be able to look it up in the future. Here's what finally got it working for me.

My Use Case

In my development team at work, some of our local database instances have different names. Manually adapting the connection string to my current local development machine every single time is not an option for me because it's error-prone (changes might get checked into version control) and outright annoying.

The connection string we're using is defined in our Web.config like this:

<add name="SqlServer" connectionString="server=(local)\FooBarSqlServer;…"   

providerName="System.Data.SqlClient" />

This is the perfect use case for an alias. Basically, an alias maps an arbitrary database name to an actual database server. So I created an alias for FooBarSqlServer, which allows me to use the above (unchanged) connection string to connect to my local (differently named) SQL Server instance. That was when I ran into the trouble motivating me to write this post. The alias simply didn't work: I couldn't use it to connect to the database, neither in our application nor using SQL Server Management Studio.

The Working Solution

I googled around quite a bit and finally found the solution in Microsoft's How to connect to SQL Server by using an earlier version of SQL Server: The section Configure a server alias to use TCP/IP sockets pointed out that I had to look up the specific port number used by the TCP/IP protocol:

Here's how you find the port number that's being used by TCP/IP on your machine:

1) Open the SQL Server Configuration Manager.

2) Expand SQL Server Network Configuration and select Protocols for <INSTANCE_NAME>.

3) Double-click on TCP/IP and make sure Enabled is set to Yes.

4) Remember whether Listen All is set to Yes or No and switch to the IP Addresses tab.

- Now, if Listen All was set to Yes (which it was for me), scroll down to the IPAll section at the very bottom of the window and find the value that's displayed for TCP Dynamic Ports.

- If Listen All was set to No, locate the value of TCP Dynamic Ports for the specific IP address you're looking for.

You'll have to copy this port number into the Port No field when you're configuring your alias. Note: that you'll have to set the Alias Name to the exact value used in your connection string. Also, if you're not using the default SQL Server instance on your development machine (which I am), you'll need to specify its name in the Server field in addition to the server name. In my case, that would be something like YourDirectory\NAMED_SQL_INSTANCE. Remember to also define the alias for 32-bit clients when your database has both 64-bit and 32-bit clients.



HostForLIFE.eu Proudly Announces ASP.NET MVC 5.1.1 Hosting

clock February 28, 2014 10:47 by author Peter

European Recommended Windows and ASP.NET Spotlight Hosting Partner in Europe, HostForLIFE.eu, has announced the availability of new hosting plans that are optimized for the latest update of the Microsoft ASP.NET MVC 5.1.1 technology.  HostForLIFE.eu - a cheap, constant uptime, excellent customer service, quality and also reliable hosting provider in advanced Windows and ASP.NET technology. They proudly announces the availability of the ASP.NET MVC 5.1.1 hosting in their entire servers environment.

HostForLIFE.eu hosts its servers in top class data centers that is located in Amsterdam to guarantee 99.9% network uptime. All data center feature redundancies in network connectivity, power, HVAC, security, and fire suppression. All hosting plans from HostForLIFE.eu include 24×7 support and 30 days money back guarantee. You can start hosting your ASP.NET MVC 5.1.1 site on their environment from as just low €3.00/month only.

ASP.NET MVC 5.1.1  is the latest update to Microsoft's popular MVC (Model-View-Controller) technology - an established web application framework. MVC enables developers to build dynamic, data-driven web sites. ASP.NET MVC 5.1.1 adds sophisticated features. ASP.NET MVC 5.1.1  gives you a powerful, patterns-based way to build dynamic websites that enables a clean separation of concerns and that gives you full control over markup. For additional information about ASP.NET MVC 5.1.1 Hosting offered by HostForLIFE.eu, please visit http://hostforlife.eu/European-ASPNET-MVC-511-Hosting

About HostForLIFE.eu

HostForLIFE.eu is an European Windows Hosting Provider which focuses on the Windows Platform only. HostForLIFE.eu deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



Windows Server 2012 R2 Hosting - HostForLIFE.eu :: Learning Windows Server 2012 R2 on Remote Desktop Service

clock February 21, 2014 09:41 by author Peter

Microsoft just released Windows Server 2012 R2 (18 October 2013). In this article, I’m going to describe which changes are available in this release compared to the first Windows 2012 release for the Remote Desktop Services (RDS)/Virtual Desktop Infrastructure (VDI). This topic contains only brief information about Windows Server 2012 R2 Hosting, if you want to be more familiar with Windows Server 2012 R2, you should try HostForLife.eu.

Installation

Logically we need to start the installation of the RDS components. Comparing this with Windows 2013 there are actually not anything changed. The possibility to install RDS using the specific Remote Desktop Services Installation is still available, followed by the two same installation methodologies: a standard deployment to divide the different RDS roles over several servers and the Quick Start deployment where the roles are installed on one server. Using the RDS installation method logically also the question to deploy a virtual machine-based desktop deployment or session-based desktop deployment is still asked. Just as in Windows 2012 it will install a VDI infrastructure the Remote Desktop Virtualization Host or the formerly known as Terminal Server based infrastructure.

It is also still possible to use the role-based installation, also here no changes comparing to Windows Server 2012.

Which is actually new it the possibility to install the RD Connection Broker role on a Domain Controller server. Personally I only would use this for demo environments or really small infrastructures. Also there is possibility to in-place upgrade of a RD Session Host to 2012 RS (from the current 2012 version).

Configuration

Also on this part I can be really quick, there are not any noticeable changes made to the configuration part. It is fully comparable with the configuration possibilities in Windows 2012; I don’t see anything new, also no things are removed. There are also no changes visible to the User Profile Disk feature.

 

Also making changes to the RDWeb Access is still done via the same way (edit the web.config file).

Is there something new?

You could think is there something new within this R2 release for Remote Desktop Services. There are definitely changes/new features, but most they are based on the user experience. Many improvements are difficult to shown in article based review. I will describe the new features, my thoughts about those new features and where possible show the features.

Improved RemoteApp Behaviour

Although this looks like a small step for the user experiences this is in my opinion a big step. The improved behavior is shown at two ways. The first one is when you are dragging the Remote App to another location on the desktop the complete application still will be shown, while previous versions only showed the frame of the application. Secondly a full preview thumbmail is shown when hovering over the app in the taskbar, just as applications directly available on the client. In previous versions only a standard icon of the application was shown. Also there is now full support for transparent windows for RemoteApps and Aero peek, again creating the same view as local applications.

Quick Reconnect

When using the Remote and Desktop Connections (RADC) in Windows Server 2012 it could take a while to reconnect to all RemoteApps. Microsoft claims that this process is improved and is now around 5 seconds to fully reconnect. Also network loss detection has been improved with a more intuitive reconnect phase. Logically this are good improvements for a better user experience.

Codec Improvements

Also the codecs are improved to further reduce the bandwidth for non-video content including the possibility to offload all progressive decode processing to AVC/H.2.64 hardware available in the client. Also DirectX 11 is now supported by extending the ability to virtualize a GPU on a Hyper-V host.

RemoteApp within a Remote Desktop Session

Although this was already available in Windows 2012 itself, it is not known a lot so I thought it was a good idea to mentSion it once more. It is now possible/supported to start a Remote Desktop session and start within this session a RemoteApp (form another server). This options creates the possibility to use the silo concept with Microsoft RDS separating a specific (set of) application(s) on specific servers for performance, incompatibility and/or security reasons.

Dynamically monitor/resolution changes

Within Windows 2012 R2 it is now possible to change the amount of monitors during the RD session or the view of screen (for example on a Surface) the session will automatically resize according the change made without a reconnection. I think this is a big step forward and end users will love this, especially with tablets in mind where the user switches often from horizontal to the vertical view and vice versa.

Support for ClickOnce Applications

Although just a view supplier use this kind of installation technique and is has some challenges within managed user profile environments it a good thing that ClickOnce applications can be offered as a RemoteApp within Windows Servers 2012 R2. Above improvements are mostly based on the user experience. There are also some changes made from a technical or administrative viewpoint.

Online Data Duplication

Probably the biggest announcement for RDS made for Windows Server 2012R2. Online Data Deplucation can achieve a big reduction on storage capacity requirements (Microsoft claims up to 90%) for personal VDI machines. As it looks like that personal VDI machines are the most used deployment technique for VDI infrastructures Microsoft arranges that one of the biggest challenges becomes a smaller challenge.

Shadowing is back

In Windows 2012 Microsoft removed the RD Shadowing option completely, while in Windows 2008 (R2) the functionally was already reduces. Although I see that most customers are now moving to Remote Assistance Microsoft is re-introducing the shadowing functionality again. Microsoft is stating that it is supporting both single as multiple monitors, but it’s unclear to me if the situation is the same as in Windows 2008R2 where it was needed that both the user as the support team should have the same monitor amount. Unfortunate I could not test this is my demo environment, anyone with experiences and would like to share those let me know (so I can add it the article).

Conclusion: To upgrade or not to upgrade

Windows Server 2012R2 Remote Desktop Services brings in my opinion several new features and improvements that are a real added value. But should you upgrade to this R2 release. It depends on your current situation. If you are still on Windows 2008R2 or lower, I think this is a good moment to consider upgrading to the latest platform. If you are still using a 32bit edition the 64bit challenge still apply and should not be forgotten. If you are running Windows Server 2012 it depends on the current end-user satisfaction. If they do not complain about the parts that are now improved I don’t think an upgrade is necessary. A different situation if you are using personal VDI machine and can use the online data duplication feature I think upgrading to Windows Server 2012 R2 is almost a must. Summarized R2 offers several good new features and improvements.



European HostForLIFE.eu Proudly Launches Windows Server 2012 R2 Hosting

clock February 17, 2014 10:09 by author Peter

HostForLIFE.eu proudly launches the support of Windows Server 2012 R2 on all their newest Windows Server environment. On Windows Server 2012 R2 hosted by HostForLIFE.eu, you can try their new and improved features that deliver extremely high levels of uptime and continuous server availability start from €3.00/month.

Microsoft recently released it’s latest operating system Windows Server 2012 R2 to global customers. Microsoft Windows Server 2012 R2 is much more than just another service pack; adding new features that make it easier to build cloud applications and services in your datacenter.

Delivering on the promise of a modern datacenter, modern applications, and people-centric IT, Windows Server 2012 R2 provides a best-in-class server experience that cost-effectively cloud-optimizes your business. When you optimize your business for the cloud with Windows Server 2012 R2 hosting, you take advantage of your existing skillsets and technology investments.

You also gain all the Microsoft experience behind building and operating private and public clouds – right in the box. Windows Server 2012 R2 offers an enterprise-class, simple and cost-effective solution that’s application-focused and user centric.

Further information and the full range of features Windows Server 2012 R2 Hosting can be viewed here: http://hostforlife.eu/European-Windows-Server-2012-R2-Hosting

About Company
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. HostForLIFE.eu deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



European SQL 2012 Hosting - Nederland :: Check SQL Memory Usage by Builing a Report

clock February 17, 2014 07:12 by author Scott

Memory is one of the most-used resources in SQL Server. Generally, the more you have, the better query performance you’ll get. How can you track your server’s memory usage? One way is to use the Performance Monitor (Perfmon) counters exposed through the sys.dm_os_performance_counters DMV. One indicator of memory performance is Page Life Expectancy (PLE). You can capture basic memory usage over time by setting up a SQL Server Agent job to query this DMV, inserting the results into a table, and reporting on the table results.

COLLECTING THE DATA

I have a “DBAInfo” database on my instance that I use to track metrics and other information. I create a new table, MemoryHistory.

USE DBAInfo;
CREATE TABLE MemoryHistory
(ID INT IDENTITY NOT NULL,
CollectionDateTime DATETIME,
PerfmonObjectName NCHAR(128),
CounterName NCHAR(128),
CounterValue BIGINT)

Then, I create a new SQL Server Agent job that runs every 5 minutes.

The only step in this job is the below query, which queries the DMV and inserts the results into the table I created.

INSERT INTO MemoryHistory
SELECT CURRENT_TIMESTAMP,
object_name,
counter_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager';

I schedule the job to run every five minutes.

VIEWING THE DATA

Now, this data isn’t going to do me any good unless I view it, and make a decision or perform an action based on what I learn.

To view the data I’ve collected, I run the following query:

SELECT ID,
CollectionDateTime,
CounterName,
CounterValue
FROM MemoryHistory;

That’s a lot of junk to sort through when all I want to see is PLE, so I narrow down the query a bit.

SELECT ID,
CollectionDateTime,
CounterName,
CounterValue
FROM MemoryHistory
WHERE CounterName = 'Page life expectancy';

But who wants to read through results like that each time there’s a problem to see when PLE rose or fell? Not me. I’d rather see it in a graphical format. How can I do that?

SQL Server Reporting Services

I have SSRS at my disposal. I’m going to create a very simple report that will allow me to enter start and end dates, and will display a line chart for PLE during that time.

REPORTING ON THE DATA

I set up my report to have DBAInfo as my data source. In order to choose dates, I use the following query as my dataset.

SELECT ID,
CollectionDateTime,
CounterName,
CounterValue
FROM MemoryHistory
WHERE CounterName = 'Page life expectancy'
AND CONVERT(DATE, CollectionDateTime) &gt;= @Start
AND CONVERT(DATE, CollectionDateTime) &lt;= @End;

I change my @Start and @End parameters to “Date/Time” so I get a date picker.

I drag a Line Chart onto the design surface and add the CounterValue as my Value and CollectionDateTime as my Category Group.

I can preview the report to view it:

Last but not least, I’ll deploy this report to Report Manager so that I and others can run it, or even schedule a regular subscription.



ASP.NET Spain Hosting - HostForLIFE.eu :: ASP.NET App Suspend in ASP.NET 4.5.1

clock February 10, 2014 06:11 by author Peter

Suspend is the new terminate

ASP.NET App Suspend is a new feature in the .NET Framework 4.5.1 that makes ASP.NET sites much more responsive and enables you to host more sites on a single server. It is very well suited for commercial web hosters, like Windows Azure Web Sites, and Enterprise IT web hosting. HostForLIFE.eu proudly launches the support of ASP.NET 4.5.1 on all their newest Windows Server environment. HostForLIFE.eu ASP.NET 4.5.1 Spain Hosting plan starts from just as low as €3.00/month only.

ASP.NET App Suspend is a self-tuning mechanism for web hosting, a little bit like CLR GC generations (if you squint). The addition of suspend establishes three states that a site can be in on a given machine. You can see the three states in the diagram, below.

All sites start out as inactive. As sites are requested, they are loaded into memory, become active, and respond to page requests. After sites have been idle, as determined by the timeout setting, they will be suspended. Suspended sites effectively lose access to the CPU, making CPU cycles and most of the memory they were using available for requests to other sites. However, they are kept in a state – this is the new suspended state – by which they can be resumed very quickly and respond to traffic again.

Usage scenarios

- Scenarios that can benefit from ASP.NET App Suspend.

- Shared hosting (commercial hosting or enterprise IT) Companies selling or taking advantage of shared hosting can pack many more sites on a given machine, while providing much more responsive site experience.  Shared web hosting is certainly the most popular kind of hosting you will discover. As the name indicates, the equipment (server) that your particular web site is held on will be shared together with lots of other users. Because of how prevalent this sort of hosting is, the considerably low cost of managing it, and the volume of companies providing this kind of service plan you can get yourself started out for very little cash. Even though shared hosting is ideal for compact or hobby web sites, it’s not at all without it’s negative aspects. These come from the very character of ‘shared’ web hosting, check out the video above to find out more.

- Switch to shared hosting Web site owners can take advantage of low-cost shared hosting while delivering the responsive experience that they want.

- Hot spare for large sites large high-traffic sites can maintain spares in suspend, ready for when one of the servers behind a load balancer goes down (planned or unplanned).

- Disaster recovery large high-traffic sites can maintain spares in suspend in a backup datacenter, ready for when the main data center goes down or otherwise becomes inaccessible.



Windows Server 2012 R2 Italy Hosting - HostForLIFE.eu - New Highlight In Windows Server 2012 R2

clock February 7, 2014 12:15 by author Peter

Windows Server 2012 R2 brings a host of new features that greatly enhance the functionality of the operating system. Many of these improvements expand on existing capabilities of Windows Server 2012 R2. You can try the features described below, on Windows Server 2012 R2 Italy Hosting.On Windows Server 2012 R2 hosted by HostForLIFE.eu, you have new and improved features that deliver extremely high levels of uptime and continuous server availability. The platform also includes greater management efficiency. This simplifies and automates the deployment and virtualization of major workloads. Greater automation means server deployments are faster and upgrades are now even simpler.

-Windows Server 2012 R2 and System Center 2012 R2 will ship at the same time with full feature support.

-Tiered Storage Spaces provides greater performance and scalability. Allows a mix of SSD and HDD in a single space and the storage spaces engine automatically moves the hot blocks to the SSD from the HDD via the in-box tiering technology. The ability to size the SSD and HDD tiers separately is available. It's possible to pin important files to the SSD tier.

-Ability to graphically create a three-way mirrored virtual disk on a Storage Space.

-Site-to-site VPN Gateway.

-In-box iSCSI target now leverages VHDX, allowing larger LUNs.

-Dynamic NIC Teaming allows more granular balancing based on flowlets, enabling best performance and utilization of available resources.

-IPAM support for virtualized environments, providing a consistent IP management experience.

-New PowerShell Desired State Configuration (DSC) extensions as part of Windows PowerShell 4, which helps ensure the components in the data center have the correct configuration.

-Work folders enable users to have access to all their data by replicating all user data to corporate file servers then back out to other devices. Data is also encrypted and if a user device is un-enrolled from management the data corporate data is removed.

-CIM and DMTF standards-based, enabling great consistent management across all types of devices.

 



SQL Server Germany Hosting - HostForLIFE.eu :: How to PIVOT table in SQL Server / PIVOT table example in SQL Server

clock February 5, 2014 15:05 by author Peter

We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table in SQL Server. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Before PIVOT

After PIVOT



A simple PIVOT sample in SQL Server.Here we are going to demonstrate a very simple PIVOT sample without any complexity. We are having a table named Accounts and it containing customer name and their deposit with denominations.

Table Structure For showing simple PIVOT sample

CREATE TABLE Accounts(Customer VARCHAR(25), Denomination VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','10 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','50 $',6)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','100 $',1)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','10 $',4)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','50 $',3)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','100 $',11)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','10 $',20)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','50 $',12)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','100 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','10 $',0)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','50 $',5)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','100 $',5) 

In order to PIVOT above mentioned table we can use below script. The result should be as Customer name with all denomination will be coming as columns with qty as values for each column.

SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN ([10 $],[50 $],[100 $])) AS Total

 

Dynamic Query to PIVOT table for dynamic columns. In the above example we are using a simple structured table and then PIVOT with denomination values. This can be achieved only when we are having denomination values as static. Suppose this denomination values are dynamic (Each country having different denomination like $,EUR, IND etc..), we need to create a dynamic query to PIVOT above table. Suppose we are having different table for getting Denomination values and we are going to take Denomination values from this table at run time as dynamic.

CREATE TABLE Denomination(Value VARCHAR(25))
GO
INSERT INTO Denomination(Value)
VALUES('10 $')
INSERT INTO Denomination(Value)
VALUES('50 $')
INSERT INTO Denomination(Value)
VALUES('100 $')

First of all, we need to get dynamic columns names from the above table. After that we can create a dynamic query with these columns.

Declare @ColumnNames VARCHAR(100);
SELECT @ColumnNames = COALESCE(@ColumnNames+ ',','') +
'['+ Cast(Value AS VARCHAR(50)) +']' FROM Denomination cust
PRINT @ColumnNames
DECLARE @DynamicQuery Varchar(MAX);
SET @DynamicQuery = '
SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN (' + @ColumnNames + ')) AS Total'
EXEC (@DynamicQuery);


About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in