About Me

I now work for Microsoft Federal in Chevy Chase, MD.

Dedicated to providing customer-driven, results-focused solutions to the complex business problems of today... and tomorrow.

At SQLTrainer.com, LLC  we understand that the technical challenges faced by businesses today are much greater in both scope and complexity than they have ever been. Businesses today are faced with understanding both local IT infrastructures as well as cloud-based technologies.

What is SQLTrainer.com?

Founded in 1998 by Ted Malone, SQLTrainer.com is a technical consulting, training and content development firm dedicated to the following core principles:

  • Technology Alone is NOT the Answer! - Implementing a particular technology because it is interesting or "cool" will not solve customer problems.
  • Technology Solutions do NOT need to be Overly Complex! - Many times developers and technical practitioners will attempt to build the most clever solution possible. While this serves to stroke the egos of those involved, it doesn't provide a maintainable solution.
  • Consultants Should be Mentors First! - When looking to hire an external consultant, businesses should look to the consultant who's willing to train themselves out of a paycheck.

Why the name, SQLTrainer.com?

SQL (pronounced See-Quell) stands for Structured Query Language, which is at the heart of every modern-day relational database system. Since many technology solutions today rely on some form of database storage or interaction, it was only logical to find a way to incorporate SQL into the name of the organization. Given that one of our core principles is to be a mentor/training above everything, the name SQLTrainer made sense. Since we also wanted to represent our embracing of the cloud, it seemed logical to add the ".com", referring to the biggest "cloud" of them all.

Live Feeds

RSS Feeds RSS
Wednesday, November 19, 2014 10:00:00 AM

To allow developers in Visual Studio to more easily incorporate the benefits of “big data” with their custom applications, Microsoft is adding a deeper tooling experience for HDInsight in Visual Studio in the most recent version of the Azure SDK. This extension to Visual Studio helps developers to visualize their Hadoop clusters, tables and associated storage in familiar and powerful tools. Developers can now create and submit ad hoc Hive queries for HDInsight directly against a cluster from within Visual Studio, or build a Hive application that is managed like any other Visual Studio project.

Download the Azure SDK now for VS 2013 | VS 2012 | VS 2015 Preview.

Integration of HDInsight objects into the “Server Explorer” brings your Big Data assets onto the same page as other cloud services under Azure. This allows for quick and simple exploration of clusters, Hive tables and their schemas, down to querying the first 100 rows of a table.  This helps you to quickly understand the shape of the data you are working with in Visual Studio.

Also, there is tooling to create Hive queries and submit them as jobs. Use the context menu against a Hadoop cluster to immediately begin writing Hive query scripts. In the example below, we create a simple query against a Hive table with geographic info to find the count of all countries and sort them by country. The Job Browser tool helps you visualize the job submissions and status.  Double click on any job to get a summary and details in the Hive Job Summary window. 

You can also navigate to any Azure Blob container and open it to work with the files contained there. The backing store is associated with the Hadoop cluster during cluster creation in the Azure dashboard. Management of the Hadoop cluster is still performed in the same Azure dashboard.

For more complex script development and lifecycle management, you can create Hive projects within Visual Studio. In the new project dialog (see below) you will find a new HDInsight Template category. A helpful starting point is the Hive Sample project type. This project is pre-populated with a more complex Hive query and sample data for the case of processing web server logs.

To get started visit the Azure HDInsight page to learn about Hadoop features on Azure. 

Monday, November 17, 2014 10:00:00 AM

Apache Spark is a popular open source framework for distributed cluster computing. Spark has been gaining popularity for its ability to handle both batch and stream processing as well as supporting in-memory and conventional disk processing. Starting today, Azure HDInsight will make it possible to install Spark as well as other Hadoop sub-projects on its clusters. This is delivered through a new customization feature called Script Action. This will allow you to experiment and deploy Hadoop projects to HDInsight clusters that were not possible before. We are making this easier specifically for Spark and R by documenting the process to install these modules.

To do this, you will have to create an HDInsight cluster with Spark Script Action. Script Action allow users to specify PowerShell scripts that will be executed on cluster nodes during cluster setup. One of the sample scripts that are released with the preview is Script Action to install Spark. During preview the feature is available through PowerShell, so you will need to run PowerShell scripts to create your Spark cluster. Below is the snippet of the PowerShell code where “spark-installer-v01.ps1” is the Script Action that installs Spark on HDInsight:

New-AzureHDInsightClusterConfig -ClusterSizeInNodes $clusterNodes

| Set-AzureHDInsightDefaultStorage -StorageAccountName $storageAccountName 
        -StorageAccountKey $storageAccountKey -StorageContainerName $containerName

| Add-AzureHDInsightScriptAction -Name "Install Spark"
        -ClusterRoleCollection HeadNode,DataNode
        -Uri https://hdiconfigactions.blob.core.windows.net/sparkconfigactionv01/spark-installer-v01.ps1

| New-AzureHDInsightCluster -Name $clusterName -Location $location

Once the cluster is provisioned it will have the Spark component installed on it. You can RDP into the cluster and use Spark shell:

  • In Hadoop command line window change directory to C:\apps\dist\spark-1.0.2
  • Run the following command to start the Spark shell.

.\bin\spark-shell

  • On the Scala prompt, enter the spark query to count words in a sample file stored in Azure Blob storage account:

val file = sc.textFile("example/data/gutenberg/davinci.txt")
val counts = file.flatMap(line => line.split(" ")).map(word => (word, 1)).reduceByKey(_ + _)
counts.toArray().foreach(println)

Read more on installing and using Spark on HDInsight here:

Read more on Script Action to make other customizations here :

For more information on Azure HDInsight:

Friday, November 14, 2014 9:00:00 AM

by Rob Farley, LobsterPot Solutions

The Analytics Platform System, with its MPP SQL Server engine (SQL Server Parallel Data Warehouse) can deliver performance and scalability for analytics workloads that you may not have expected from SQL Server. But there are key differences in working with SQL Server PDW and SQL Server Enterprise Edition that one should be aware of in order to take full advantage of the SQL Server PDW capabilities. One of the most important considerations when tuning queries in Microsoft SQL Server Parallel Data Warehouse is the minimisation of data movement. This post shows a useful technique regarding the identification of redundant joins through additional predicates that simulate check constraints.

Microsoft’s PDW, part of the Analytics Platform System (APS), offers scale-out technology for data warehouses. This involves spreading data across a number of SQL Server nodes and distributions, such that systems can host up to many petabytes of data. To achieve this, queries which use data from multiple distributions to satisfy joins must leverage the Data Movement Service (DMS) to relocate data during the execution of the query. This data movement is both a blessing and a curse; a blessing because it is the fundamental technology which allows the scale-out features to work, and a curse because it can be one of the most expensive parts of query execution. Furthermore, tuning to avoid data movement is something which many SQL Server query tuning experts have little experience, as it is unique to the Parallel Data Warehouse edition of SQL Server.

Regardless of whether data in PDW is stored in a column-store or row-store manner, or whether it is partitioned or not, there is a decision to be made as to whether a table is to be replicated or distributed. Replicated tables store a full copy of their data on each compute node of the system, while distributed tables distribute their data across distributions, of which there are eight on each compute node. In a system with six compute nodes, there would be forty-eight distributions, with an average of less than 2.1% (100% / 48) of the data in each distribution.

When deciding whether to distribute or replicate data, there are a number of considerations to bear in mind. Replicated data uses more storage and also has a larger management overhead, but can be more easily joined to data, as every SQL node has local access to replicated data. By distributing larger tables according to the hash of one of the table columns (known as the distribution key), the overhead of both reading and writing data is reduced – effectively reducing the size of databases by an order of magnitude.

Having decided to distribute data, choosing which column to use as the distribution key is driven by factors including the minimisation of data movement and the reduction of skew. Skew is important because if a distribution has much more than the average amount of data, this can affect query time. However, the minimisation of data movement is probably the most significant factor in distribution-key choice.

Joining two tables together involves identifying whether rows from each table match to according a number of predicates, but to do this, the two rows must be available on the same compute node. If one of the tables is replicated, this requirement is already satisfied (although it might need to be ‘trimmed’ to enable a left join), but if both tables are distributed, then the data is only known to be on the same node if one of the join predicates is an equality predicate between the distribution keys of the tables, and the data types of those keys are exactly identical (including nullability and length). More can be read about this in the excellent whitepaper about Query Execution in Parallel Data Warehouse.

To avoid data movement between commonly-performed joins, creativity is often needed by the data warehouse designers. This could involve the addition of extra columns to tables, such as adding the CustomerKey to many fact data tables (and using this as the distribution key), as joins between orders, items, payments, and other information required for a given report, as all these items are ultimately about a customer, and adding additional predicates to each join to alert the PDW Engine that only rows within the same distribution could possibly match. This is thinking that is alien for most data warehouse designers, who would typically feel that adding CustomerKey to a table not directly related to a Customer dimension is against best-practice advice.

 

Another technique commonly used by PDW data warehouse designers that is rarely seen in other SQL Server data warehouses is splitting tables up into two, either vertically or horizontally, whereas both are relatively common in PDW to avoid some of the problems that can often occur.

Splitting a table vertically is frequently done to reduce the impact of skew when the ideal distribution key for joins is not evenly distributed. Imagine the scenario of identifiable customers and unidentifiable customers, as increasingly the situation as stores have loyalty programs allowing them to identify a large portion (but not all) customers. For the analysis of shopping trends, it could be very useful to have data distributed by customer, but if half the customers are unknown, there will be a large amount of skew.

To solve this, sales could be split into two tables, such as Sales_KnownCustomer (distributed by CustomerKey) and Sales_UnknownCustomer (distributed by some other column). When analysing by customer, the table Sales_KnownCustomer could be used, including the CustomerKey as an additional (even if redundant) join predicate. A view performing a UNION ALL over the two tables could be used to allow reports that need to consider all Sales.

The query overhead of having the two tables is potentially high, especially if we consider tables for Sales, SaleItems, Deliveries, and more, which might all need to be split into two to avoid skew while minimising data movement, using CustomerKey as the distribution key when known to allow customer-based analysis, and SalesKey when the customer is unknown.

By distributing on a common key the impact is to effectively create mini-databases which are split out according to groups of customers, with all of the data about a particular customer residing in a single database. This is similar to the way that people scale out when doing so manually, rather than using a system such as PDW. Of course, there is a lot of additional overhead when trying to scale out manually, such as working out how to execute queries that do involve some amount of data movement.

By splitting up the tables into ones for known and unknown customers, queries that were looking something like the following:

SELECT …
FROM Sales AS s
JOIN SaleItems AS si
   ON si.SalesKey = s.SalesKey
JOIN Delivery_SaleItems AS dsi
   ON dsi.LineItemKey = si.LineItemKey
JOIN Deliveries AS d
   ON d.DeliveryKey = dsi.DeliveryKey

…would become something like:

SELECT …
FROM Sales_KnownCustomer AS s
JOIN SaleItems_KnownCustomer AS si
   ON si.SalesKey = s.SalesKey
   AND si.CustomerKey = s.CustomerKey
JOIN Delivery_SaleItems_KnownCustomer AS dsi
   ON dsi.LineItemKey = si.LineItemKey
   AND dsi.CustomerKey = s.CustomerKey
JOIN Deliveries_KnownCustomer AS d
   ON d.DeliveryKey = dsi.DeliveryKey
   AND d.CustomerKey = s.CustomerKey
UNION ALL
SELECT …
FROM Sales_UnknownCustomer AS s
JOIN SaleItems_UnknownCustomer AS li
   ON si.SalesKey = s.SalesKey
JOIN Delivery_SaleItems_UnknownCustomer AS dsi
   ON dsi.LineItemKey = s.LineItemKey
   AND dsi.SalesKey = s.SalesKey
JOIN Deliveries_UnknownCustomer AS d
   ON d.DeliveryKey = s.DeliveryKey
   AND d.SalesKey = s.SalesKey

I’m sure you can appreciate that this becomes a much larger effort for query writers, and the existence of views to simplify querying back to the earlier shape could be useful. If both CustomerKey and SalesKey were being used as distribution keys, then joins between the views would require both, but this can be incorporated into logical layers such as Data Source Views much more easily than using UNION ALL across the results of many joins. A DSV or Data Model could easily define relationships between tables using multiple columns so that self-serving reporting environments leverage the additional predicates.

The use of views should be considered very carefully, as it is easily possible to end up with views that nest views that nest view that nest views, and an environment that is very hard to troubleshoot and performs poorly. With sufficient care and expertise, however, there are some advantages to be had.

 

The resultant query would look something like:

SELECT …
FROM Sales AS s
JOIN SaleItems AS li
   ON si.SalesKey = s.SalesKey
   AND si.CustomerKey = s.CustomerKey
JOIN Delivery_SaleItems AS dsi
   ON dsi.LineItemKey = si.LineItemKey
   AND dsi.CustomerKey = s.CustomerKey
   AND dsi.SalesKey = s.SalesKey
JOIN Deliveries AS d
   ON d.DeliveryKey = dsi.DeliveryKey
   AND d.CustomerKey = s.CustomerKey
   AND d.SalesKey = s.SalesKey

Joining multiple sets of tables which have been combined using UNION ALL is not the same as performing a UNION ALL of sets of tables which have been joined. Much like any high school mathematics teacher will happily explain that (a*b)+(c*d) is not the same as (a+c)*(b+d), additional combinations need to be considered when the logical order of joins and UNION ALLs.

Notice that when we have (TableA1 UNION ALL TableA2) JOIN (TableB1 UNION ALL TableB2), we must perform joins not only between TableA1 and TableB1, and TableA2 and TableB2, but also TableA1 and TableB2, and TableB1 and TableA2. These last two combinations do not involve tables with common distribution keys, and therefore we would see data movement. This is despite the fact that we know that there can be no matching rows in those combinations, because some are for KnownCustomers and the others are for UnknownCustomers. Effectively, the relationships between the tables would be more like the following diagram:

There is an important stage of Query Optimization which must be considered here, and which can be leveraged to remove the need for data movement when this pattern is applied – that of Contradiction.

The contradiction algorithm is an incredibly useful but underappreciated stage of Query Optimization. Typically it is explained using an obvious contradiction such as WHERE 1=2. Notice the effect on the query plans of using this predicate.

Because the Query Optimizer recognises that no rows can possibly satisfy the predicate WHERE 1=2, it does not access the data structures seen in the first query plan.

This is useful, but many readers may not consider queries that use such an obvious contradiction are going to appear in their code.

But suppose the views that perform a UNION ALL are expressed in this form:

CREATE VIEW dbo.Sales AS
SELECT *
FROM dbo.Sales_KnownCustomer
WHERE CustomerID > 0
UNION ALL
SELECT *
FROM dbo.Sales_UnknownCustomer
WHERE CustomerID = 0;

Now, we see a different kind of behaviour.

Before the predicates are used, the query on the views is rewritten as follows (with SELECT clauses replaced by ellipses).

SELECT …
FROM   (SELECT …
        FROM   (SELECT ...
                FROM   [sample_vsplit].[dbo].[Sales_KnownCustomer] AS T4_1
                UNION ALL
                SELECT …
                FROM   [tempdb].[dbo].[TEMP_ID_4208] AS T4_1) AS T2_1
               INNER JOIN
               (SELECT …
                FROM   (SELECT …
                        FROM   [sample_vsplit].[dbo].[SaleItems_KnownCustomer] AS T5_1
                        UNION ALL
                        SELECT …
                        FROM   [tempdb].[dbo].[TEMP_ID_4209] AS T5_1) AS T3_1
                       INNER JOIN
                       (SELECT …
                        FROM   (SELECT …
                                FROM   [sample_vsplit].[dbo].[Delivery_SaleItems_KnownCustomer] AS T6_1
                                UNION ALL
                                SELECT …
                                FROM   [tempdb].[dbo].[TEMP_ID_4210] AS T6_1) AS T4_1
                               INNER JOIN
                               (SELECT …
                                FROM   [sample_vsplit].[dbo].[Deliveries_KnownCustomer] AS T6_1
                                UNION ALL
                                SELECT …
                                FROM   [tempdb].[dbo].[TEMP_ID_4211] AS T6_1) AS T4_2
                               ON (([T4_2].[CustomerKey] = [T4_1].[CustomerKey])
                                   AND ([T4_2].[SalesKey] = [T4_1].[SalesKey])
                                       AND ([T4_2].[DeliveryKey] = [T4_1].[DeliveryKey]))) AS T3_2
                       ON (([T3_1].[CustomerKey] = [T3_2].[CustomerKey])
                           AND ([T3_1].[SalesKey] = [T3_2].[SalesKey])
                               AND ([T3_2].[SaleItemKey] = [T3_1].[SaleItemKey]))) AS T2_2
               ON (([T2_2].[CustomerKey] = [T2_1].[CustomerKey])
                   AND ([T2_2].[SalesKey] = [T2_1].[SalesKey]))) AS T1_1

Whereas with the inclusion of the additional predicates, the query simplifies to:

SELECT …
FROM   (SELECT …
        FROM   (SELECT …
                FROM   [sample_vsplit].[dbo].[Sales_KnownCustomer] AS T4_1
                WHERE  ([T4_1].[CustomerKey] > 0)) AS T3_1
               INNER JOIN
               (SELECT …
                FROM   (SELECT …
                        FROM   [sample_vsplit].[dbo].[SaleItems_KnownCustomer] AS T5_1
                        WHERE  ([T5_1].[CustomerKey] > 0)) AS T4_1
                       INNER JOIN
                       (SELECT …
                        FROM   (SELECT …
                                FROM   [sample_vsplit].[dbo].[Delivery_SaleItems_KnownCustomer] AS T6_1
                                WHERE  ([T6_1].[CustomerKey] > 0)) AS T5_1
                               INNER JOIN
                               (SELECT …
                                FROM   [sample_vsplit].[dbo].[Deliveries_KnownCustomer] AS T6_1
                                WHERE  ([T6_1].[CustomerKey] > 0)) AS T5_2
                               ON (([T5_2].[CustomerKey] = [T5_1].[CustomerKey])
                                   AND ([T5_2].[SalesKey] = [T5_1].[SalesKey])
                                       AND ([T5_2].[DeliveryKey] = [T5_1].[DeliveryKey]))) AS T4_2
                       ON (([T4_1].[CustomerKey] = [T4_2].[CustomerKey])
                           AND ([T4_1].[SalesKey] = [T4_2].[SalesKey])
                               AND ([T4_2].[SaleItemKey] = [T4_1].[SaleItemKey]))) AS T3_2
               ON (([T3_2].[CustomerKey] = [T3_1].[CustomerKey])
                   AND ([T3_2].[SalesKey] = [T3_1].[SalesKey]))
        UNION ALL
        SELECT …
        FROM   (SELECT …
                FROM   [sample_vsplit].[dbo].[Sales_UnknownCustomer] AS T4_1
                WHERE  ([T4_1].[CustomerKey] = 0)) AS T3_1
               INNER JOIN
               (SELECT …
                FROM   (SELECT …
                        FROM   [sample_vsplit].[dbo].[SaleItems_UnknownCustomer] AS T5_1
                        WHERE  ([T5_1].[CustomerKey] = 0)) AS T4_1
                       INNER JOIN
                       (SELECT …
                        FROM   (SELECT …
                                FROM   [sample_vsplit].[dbo].[Delivery_SaleItems_UnknownCustomer] AS T6_1
                                WHERE  ([T6_1].[CustomerKey] = 0)) AS T5_1
                               INNER JOIN
                               (SELECT …
                                FROM   [sample_vsplit].[dbo].[Deliveries_UnknownCustomer] AS T6_1
                                WHERE  ([T6_1].[CustomerKey] = 0)) AS T5_2
                               ON (([T5_2].[CustomerKey] = [T5_1].[CustomerKey])
                                   AND ([T5_2].[SalesKey] = [T5_1].[SalesKey])
                                       AND ([T5_2].[DeliveryKey] = [T5_1].[DeliveryKey]))) AS T4_2
                       ON (([T4_1].[CustomerKey] = [T4_2].[CustomerKey])
                           AND ([T4_1].[SalesKey] = [T4_2].[SalesKey])
                               AND ([T4_2].[SaleItemKey] = [T4_1].[SaleItemKey]))) AS T3_2
               ON (([T3_2].[CustomerKey] = [T3_1].[CustomerKey])
                   AND ([T3_2].[SalesKey] = [T3_1].[SalesKey]))) AS T1_1

This may seem more complex – it’s certainly longer – but this is the original, preferred version of the join. This is a powerful rewrite of the query.

Furthermore, the astute PDW-familiar reader will quickly realise that the UNION ALL of two local queries (queries that don’t require data movement) is also local, and that therefore, this query is completely local. The TEMP_ID_NNNNN tables in the first rewrite are more evidence that data movement has been required.

When the two plans are shown using PDW’s EXPLAIN keyword, the significance is shown even clearer.

The first plan appears as following, and it is obvious that there is a large amount of data movement involved.

The queries passed in are identical, but the altered definitions of the views have removed the need for any data movement at all. This should allow your query to run a little faster. Ok, a lot faster.

Summary

When splitting distributed tables vertically to avoid skew, views over those tables should include predicates which reiterate the conditions that cause the data to be populated into each table. This provides additional information to the PDW Engine that can remove unnecessary data movement, resulting in much-improved performance, both for standard reports using designed queries, and ad hoc reports that use a data model.

Thursday, November 13, 2014 11:00:00 AM

Today we are pleased to announce the availability of a community technology preview release of the Microsoft JDBC Driver for SQL Server! Download the preview driver today here.

The JDBC Driver for SQL Server is a Java Database Connectivity (JDBC) 4.1 compliant driver that provides robust data access to Microsoft SQL Server and Microsoft Azure SQL Database.  Microsoft JDBC Driver 4.1 (Preview) for SQL Server now supports Java Development Kit (JDK) version 7.0.

The updated driver is part of SQL Server’s wider interoperability program, which includes the recent announcement of a preview driver for SQL Server compatible with PHP 5.5.

We look forward to hearing your feedback about the new driver. Let us know what you think on Microsoft Connect.

Thursday, November 13, 2014 10:00:00 AM

We’ve been talking a lot lately about our in-memory technology in SQL Server. If you attended the PASS Summit last week you likely heard a fair share. So, why all the fuss? Simply put, SQL Server 2014’s in-memory delivers serious business impact. According to CMS Wire “Microsoft SQL 2014 just may be the most complete in-memory solution on the market.”

Last week we told you the story of Dell and how they have boosted website performance and enabled faster online shopping experiences with SQL Server’s in-memory online transaction processing technology. Dell is not alone. Nasdaq, Bwin and EdgeNet all have seen significant performance gains. Let’s take a look at another customer, Samsung Electro-Mechanics.

Samsung Electro-Mechanics, an electrical and mechanical devices manufacturer, uses its Statistical Process Control system to manage quality control for its large-scale manufacturing facilities.  As the system evolved and became more complex, database performance suffered, impacting manufacturing quality.  To stabilize and increase performance, Samsung Electro-Mechanics implemented SQL Server 2014 in-memory OLTP and CCI (Clustered Columnstore Indexes).

By doing so, Samsung Electro-Mechanics was able to increase transactional performance by 24x using in-memory OLTP, and improve query and reporting by 22x using the in-memory Columnstore.  These performance gains far exceeded their initial goal of improving overall performance by 2x.

So consider what impact SQL Server in-memory could have on your business.

Learn more about SQL Server 2014 in-memory, or try SQL Server 2014 now.

Thursday, November 13, 2014 9:00:00 AM

We’re excited to announce that AlwaysOn Availability Groups now support Internal Listeners on Azure Virtual Machines. Today we updated our official documentation accordingly.

Availability Groups and Listeners on Azure Virtual Machines

Availability Groups, released in SQL Server 2012 and enhanced in SQL Server 2014, detect conditions impacting SQL Server availability (e.g. SQL service being down or losing connectivity).  When detecting these conditions, the Availability Group fails over a group of databases to a secondary replica. In the context of Azure Infrastructure Services, this significantly increases the availability of these databases during Microsoft Azure’s VM Service Healing (e.g. due to physical hardware failures), platform upgrades, or your own patching of the guest OS or SQL Server.

Client applications connect to the primary replica of an availability group using an Availability Group Listener. The Listener specifies a DNS name that remains the same, irrespective of the number of replicas or where these are located.  

For example: Server=tcp:ListenerName,1433;Database=DatabaseName;

To support this in Azure Virtual Machines, the Listener must be assigned the IP address of an Azure Load Balancer. The Load Balancer routes connections to endpoint of the primary replica of the Availability Group.

Internal Availability Group Listeners

Until now, the IP address of the Azure Load Balancer had to be a public IP reachable over the Internet. To restrict access to the listener only to trusted entities, you could configure an access control list for the Load Balancer IP. However, maintaining this list could be cumbersome over time.

To simplify this, you can now configure an Internal Azure Load Balancer. This has an internal IP address reachable only within a Virtual Network. This makes the Listener accessible only to client applications located

This is depicted in the picture below. An availability group has three replicas, two in Virtual Network 1 and one in Virtual Network 2. The Virtual Networks are connected via a VPN tunnel. The Availability Group has a Listener configured using an Internal Load Balancer. This disallows access outside of the connected Virtual Networks.

To create an Internal Azure Load Balancer execute the Powershell cmdlet Add-AzureInternalLoadBalancer. As depicted below, this cmdlet receives the name of the Load Balancer, the Cloud Service where it’ll be created, and a static IP address in the Virtual Network. This is the internal IP address that should be used for the listener.

Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ILBName -ServiceName $ServiceName -StaticVNetIPAddress $ILBStaticIP

Check our official documentation and start using Internal Availability Groups today!

To learn more about SQL Server in Azure Virtual Machines check our start page.

Wednesday, November 12, 2014 10:00:00 AM

There’s an old adage: time is money. Technology and the internet have changed the value of time and created a very speed-oriented culture. The pace at which you as a business deliver information, react to customers, enable online purchases, etc. directly correlates with your revenue. For example, reaction times and processing speeds can mean the difference between making a sale and a consumer losing interest. This is where the right data platform comes into play.

If you attended PASS Summit or watched the keynotes online, you saw us speak about Dell and the success they’ve had in using technology performance to drive their business. For Dell, providing its customers with the best possible online experience is paramount. That meant boosting its website performance so that each day its 10,000 concurrent shoppers (this number jumps to nearly 1 million concurrent shoppers during the holiday season) could enjoy faster, frustration-free shopping experiences. For Dell, time literally means money.

With a very specific need and goal in mind Dell evaluated numerous other in-memory tools and databases, but ultimately selected SQL Server 2014.

Dell turned to Microsoft’s in-memory OLTP (online transaction processing) technology because of its unique lock and latch free table architecture the removed database contention while still guaranteeing 100 percent durability. By removing database contention Dell could utilize far more parallel processors to not only improve transactional speed but also significantly increase the number of concurrent users. And choosing SQL Server 2014 with in-memory built-in meant Dell did not have to learn new APIs or tools their developers could use familiar SQL Server tools and T-SQL to easily implement the new in-memory technologies.

All of this meant Dell was able to double its application speeds and process transactions 9x faster. Like Dell, you also can take advantage of the workload optimized in-memory technologies built into the SQL Server 2014 data platform for faster transactions, faster queries and faster analytics. And you can do it all without expensive add-ons utilizing your existing hardware, and existing development skills. 

Learn more about SQL Server 2014 in-memory technology

Tuesday, November 11, 2014 12:55:00 PM

Last week was an exciting week for the SQL Server team, as one of our favorite events happened – PASS Summit. If you attended PASS, you probably heard a ton about the latest version of SQL Server 2014.

One of the key drivers of SQL 2014’s design was the in-memory technology that is built into the product. These capabilities and the way they were designed are a key differentiator for SQL Server 2014. Recently we discussed how using SQL Server 2014’s in-memory technology can have a dramatic impact on your business – speeding transactions, queries, and insights. Today let’s  delve a little deeper into our in-memory solution and our unique approach to its design.

We built in-memory technology into SQL Server from the ground up, making it the first in-memory database that works across all workloads. These in-memory capabilities are available not only on-premises, but also in the cloud when you use SQL Server in an Azure VM or use the upcoming in-memory columnstore capabilities within Azure SQL Database. So just what makes our approach so unique? This video describes it well.

We have five core design points for SQL Server in-memory. These are: 

  1. It’s built-in. If you know SQL Server, you’re ready to go. You don’t need new development tools, to rewrite the entire app, or learn new APIs.
  2. It increases speed and throughput. SQL Server’s in-memory OLTP design removes database contention with lock and latch-free table architecture while maintaining 100 percent data durability. This means you can take advantage of all your compute resources in parallel, for more concurrent users.
  3. It’s flexible. Your entire database doesn’t need to be in-memory. You can choose to store hot data in-memory and cold data on disk, while still being able to access both with a single query. This give you the ability to optimize new or existing hardware.
  4. It’s easy to implement. The new migration advisory built right into SQL Server Management Studio lets you easily decide what to migrate to memory.
  5. It’s workload-optimized. In-memory OLTP is optimized for faster transactions, enhanced in-memory ColumnStore gives you faster queries and reports, and in-memory built into Excel and Analysis Services speeds analytics.  

All of this combined leads to up to 30x faster transactions, over 100x faster queries and reporting, and easy management of millions of rows of data in Excel. Think about what this can do for your business.

Learn more about SQL Server 2014 in-memory, or try SQL Server 2014 now. 

Thursday, November 6, 2014 10:00:00 AM

Today we are pleased to announce the availability of a community technology preview release of the SQL Server Driver for PHP! Download the preview driver today here.

This release will allow developers who use the PHP scripting language version 5.5 to access Microsoft SQL Server and Microsoft Azure SQL Database.   The full source code for the driver will be made available on GITHUB, at https://github.com/azure/msphpsql.
 
The updated driver is part of SQL Server’s wider interoperability program, which includes the upcoming release of a JDBC Driver for SQL Server compatible with JDK7. This driver will enable customers to develop applications that connect to SQL Server against Java 7 and move forward with the Java platform.

We look forward to hearing your feedback about the new driver. Let us know what you think via GitHub.

Wednesday, November 5, 2014 10:00:00 AM

This morning at the Professional Association for SQL Server (PASS) Summit, we celebrated SQL Server 2014’s strong momentum and introduced new services that further expand Microsoft’s big data platform. We announced a major update coming to our database-as-a-service, Azure SQL Database, and easier access to our machine learning service, Azure Machine Learning. These new updates continue our efforts to bring big data to everyone by delivering a comprehensive platform that ensures every organization, every team and every individual is empowered to do more and achieve more because of the data at their fingertips.

I’m really pleased to be making these announcements today at PASS Summit, where, along with my colleagues Joseph Sirosh, corporate vice president of Machine Learning and Information Management; and James Phillips, general manager of Data Experiences; I delivered a keynote highlighting the momentum of SQL Server 2014 and other recent releases in our data platform such as Azure Stream Analytics, Azure Data Factory, Azure DocumentDB, Azure Search and Azure HDInsight. As the world’s largest gathering of SQL Server and business intelligence professionals, PASS is hugely important, enabling us to connect with SQL Server customers and gain valuable feedback to help inform the product’s development.

Customers embrace SQL Server 2014

SQL Server is the cornerstone of our big data platform. It is the world’s most widely-deployed database and is used across the globe for mission critical enterprise deployments. Last month, based largely on our work with SQL Server, Microsoft was recognized as a Leader in Gartner's Magic Quadrant for Operational Database Management Systems, and positioned furthest to the right in completeness of vision*. Earlier this year, we released SQL Server 2014, which includes built-in breakthrough in-memory OLTP and columnstore technologies, as well as hybrid cloud capabilities. Since then, SQL Server 2014 has seen tremendous growth and positive reception among customers, with more than 1.2 million downloads to date and 30 percent of Azure SQL Server virtual machines currently running SQL Server 2014.

Clalit, Dell, Eastman Chemical Company, GameStop, Kiwibank, LC Waikiki, Pros, Saab and Stack Overflow are just a few of the customers using SQL Server 2014. GameStop is using SQL Server 2014 in two main scenarios: disaster recovery and backup to Azure to accommodate the company’s infrastructure freeze for holidays and big game launches, and as the default install for new SQL Server instances. Stack Overflow is a question and answer site for professional and enthusiast programmers. By basing their platform on technologies like SQL Server 2014 (specifically taking advantage of AlwaysOn Availability Group replicas), they can have a highly available, high-performing platform that easily and quickly gets answers to thousands of global users.

Azure SQL Database, Azure Machine Learning

Later this year, we will preview a new version of Azure SQL Database that represents another major milestone for this database-as-a-service. With this preview, we will add SQL Server capabilities that will make it easier to extend and migrate applications to the cloud, including support for larger databases with online indexing and parallel queries, improved T-SQL support with common language runtime and XML index, and monitoring and troubleshooting with extended events. In addition, the preview will unlock our in-memory columnstore, which will deliver greater performance for data marts and continue our journey of bringing in-memory technologies to the cloud. We will offer these new preview capabilities as part of the service tiers introduced earlier this year, which deliver 99.99% availability, larger database sizes, restore and geo-replication capabilities, and predictable performance.

Microsoft Azure Machine Learning is a fully managed cloud service for building predictive analytics solutions, and helps overcome the challenges most businesses face in deploying and using machine learning. Starting today, it will be easier than ever for anyone to try Azure Machine Learning, as the service is now available to test free of charge without a subscription or credit card – all customers need to get started is a Microsoft account ID. This free tier is one more way Azure Machine Learning is making advanced analytics more accessible to more people. DBAs, developers, business intelligence professionals and nascent data scientists can now experiment with Azure Machine Learning at no cost.

Microsoft Data Platform

We are making all these investments in SQL Server and the rest of our data platform because we are living and working in an amazing time where organizations are utilizing data to make smarter decisions, better predict their customers’ needs and provide more differentiated products and services. Data has become the new currency and it is helping to differentiate today’s leading companies. 

To get there, organizations need a comprehensive platform to capture and manage all of their data, transform and analyze that data for new insights, and provide tools which enable users across their organization to visualize data and make better business decisions. Microsoft’s approach is to make it easier for our customers to work with data of any type and size – using the tools, languages and frameworks they want – in a trusted environment on-premises and in the cloud. To learn more about our approach to big data, visit our web page.

*Disclaimer:
Gartner does not endorse any vendor, product or service depicted in its research publications, and does not advise technology users to select only those vendors with the highest ratings. Gartner research publications consist of the opinions of Gartner’s research organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose.

Site Map | Printable View | © 2008 - 2014 SQLTrainer.com, LLC | Powered by mojoPortal | HTML 5 | CSS | Design by mitchinson