Monthly Archives: July 2018

  • SQL Server Performance: 2014 vs 2016

    sql server 2014 vs 2016

    Is an Upgrade Useful?

    Database performance is always an issue, especially when considering upgrades. Microsoft just provided a new version in 2014 of SQL Server and then another version in 2016. The 2014 version of SQL Server included some newer technology innovations that were long overdue and improved overall performance. Now the question could be raised what are the real performance differences between the 2014 and 2016 versions? Is an upgrade to the newest version of SQL Server worthwhile?

    An IT manager might well sit down with database administrators and discuss these very issues. Technical terms, code, queries and much more are likely to come out of the conversation, many of which may well leave many managers without specific database experience confused on the issue. There are some basic performance factors which can be provided that may well assist many non-database experts in their understanding of performance issues and improvements.

    One of the main factors to consider is that the SQL engine for the server version remained basically the same since 1998 when SQL version 7 was in use (specifics detailed below). With the 2014 version the engine for SQL Server was upgraded to take advantage of hardware technology improvements, so just having this version in use is a good move forward.

    But with the 2016 version there were additional changes to the SQL Server engine which made its processing better integrated with current technology so that it provides far faster processing of database functions. Some observers in the industry have likened this to cars and local transportation needs. You can have a very fast car but unless the actual infrastructure is upgraded then traffic flow may be an issue regardless of how great the car may be. Microsoft has taken the approach of improving the SQL Server engine so that it takes full advantage of technology changes to hardware.

    Likewise, when it comes to cloud-based database services, Microsoft introduced the service-based version of SQL Server 2016 to run natively in Azure. This means that all of the changes to the SQL Server engine are fully integrated on the Azure hardware platform and further means that a shift to cloud-based databases may well provide the best performance, especially for growing databases. Additionally, with newer versions of SQL in the future, databases will be much easier to upgrade from Azure for continued improvements to performance and integration.

    Comparing Performance - 2014 vs 2016

    • What should you be concerned most about when thinking of performance improvements with the latest version of SQL Server? For that answer we turn to what was changed with version 2014 which, as hinted at above, involved the server engine - specifically the cardinality estimator. Without getting into too many technical concerns, the cardinality estimator is basically the core of the query optimizer. Within this core, the cardinality estimator works with statistics within the database for best functionality. If the estimator grows less accurate, then queries are slower. The upgrade of the cardinality estimator in the 2014 version, which had not been upgraded since 1998, was a big step forward. In this regard, the 2014 version is a major improvement in performance over older versions.With the 2016 version of SQL Server, there are even more improvements to the functions of the cardinality estimator which provides even better performance. However, there may need to be testing with older databases to verify which compatibility version can run in this latest version of the SQL Server.When it comes to the compatibility levels that are available in the 2016 version you will find that if you must run an older compatibility level you may lose some of the latest feature functionalities. The good news is that if you must run the older version of the cardinality estimator there is a command line configuration which will allow you to run all of the latest features of the 2016 version with the older estimator.
    • There are a few more features which can be very beneficial to migrating to this latest version of Microsoft's SQL Server. Multi-threaded insert – select statements can now be used. Memory optimized tables can also now be multi-threaded.
    • Additionally, statistics within the database are now updated more frequently which creates a higher level of estimates for better performance. Just as an example, previous behavior for a table of 1 billion rows would have been triggered for update after changes to 200 million changes in a database. With SQL Server 2016 these updates are now triggered at 1 million changes which creates more accurate estimates of statistics within the database for much faster queries.
    • With relational databases, tables can now be linked to each other for improved data integrity using foreign key constraints. Previously there were limitations to the number of key constraints which could be used, that number being 253. With SQL Server 2016, foreign key constraints now have a limitation of 10,000. For a relational database, this means that thousands of tables can now reference to a single user table and take advantage of data integrity in a much broader range. It is important to note that this is for relational databases only.
    • The importance of the foreign key constraint changes and, as mentioned above, the number was limited with SQL Server version 2014 due to the extensive computing cost. However, Microsoft has reduced the cost to resources and increased the number of foreign key constraints by introducing a new version of the referential integrity operator. Again, without being too technical, the referential integrity checks within a database are now done within the new query execution operator. The result is much faster performance for the internal workings of databases running on SQL Server 2016.

    These are just some basic points of performance improvements Microsoft has made between versions 2014 and 2016. For a more in depth understanding of what these and other features in the latest version may mean for your database performance, you may need to consult with experts who can examine and explain more of what may be needed in consideration of migrating to SQL Server 2016 to provide the best experience to clients, customers and users of your database offerings.

    Conclusion

    It may be confusing to determine how to apply IT budgets when it comes to various upgrades, especially when considering those for databases. However, it is important to keep in mind that as databases grow in size they may not just outgrow storage availability but also processing capabilities.

    Keeping current with the latest versions will likely provide many benefits to existing databases which have already been upgraded. There may be additional factors to consider as to whether these databases are working fully integrated with the most current versions of SQL Server. In this case, it can be highly beneficial to consult with professionals who can adequately determine current health, configuration, and modes in which a database may be operating. A full investigation of organizational databases will be increasingly important to determining how performance in the latest version of Microsoft SQL Server can be improved as well as maintained for upcoming versions and possible migration to cloud-based services.

    To learn more about Microsoft SQL Server, contact our experts at RoyalDiscount.com- your online source for cheap OEM, Retail & Cloud products.


  • Microsoft Azure Cloud Security Guide

    azure cloud security

    Approaches to Security

    Anyone who's ever worked in a secure facility has developed a basic concept for what it takes to create and maintain such an atmosphere. There are any number of ways to control access to the facility using automatic gates and guards. Additionally, questions can be asked and access approved or denied based on valid need. Once an individual is granted access with in the facility, actually entering the building can also be further regulated and observed through a set of further security measures regarding doors, badges and escorts.

    Behind all of these measures are policies which have been developed and fine-tuned which provide guidance for operation of the facility. These policies are carefully developed and consistently applied in order to maintain security, even down to data access and protection. The same is true when securing data in Microsoft Azure Cloud Security. Options for securing customer information have been developed so that Azure cloud clients can rely on security facilities within their spheres of operation to maintain the confidence of data protection and mitigating unwanted access.

    Microsoft now offers a wide range of security features within its Azure cloud products which provide organizations with a high level of confidence that breaches will be minimized and any security incidents will be quickly addressed for the future. Clients can make use of Azure's tools to develop their own policies which will mitigate any intrusions and minimize the effects of those which do occur.

    Foundations of Azure Security

    Azure achieves high-end security for cloud clients by providing the following:

    • Management and control of identity and user access - Azure provides an Active Directory environment with greater control over user access to information. Multifactor authentication sign-in is available while Azure also provides greater control over authentication authorization and access control. Developers will also find tools to better integrate identity management across platforms including mobile and web apps. Authentication through Azure Active Directory can even be integrated into existing on-premise Active Directory and syncing.
    • Encryption of communications and operations procedures - Azure includes data encryption at all levels whether information is in transit or at rest. Encryption keys can be stored in vaults both on premises and in the cloud. Data can even be encrypted before it is placed into the cloud.
    • Solid networking infrastructure security - Azure hardens security networks even when communication between on-site and cloud networks are necessary. Through high-speed connections, Azure extends existing networks to the cloud using secure VPN connections while providing more design and infrastructure controls through the Azure Fabric Controller. Network Security Groups (NSG) control traffic to VM's, help secure communications between Virtual Azure Networks and implements packet filtering firewalls by default on all hosts and VM's. All datacenter locations in all regions provide a wide range of physical security and access controls to prevent breaches through such means.
    • Tools for defense against intrusion and threats - Microsoft instituted throughout all Azure regions continual monitoring, testing and prevention processes. Anything from intrusion and anomaly detection to DDoS prevention and penetration testing as well as machine learning and behavioral analytics are employed for the highest security possible. Microsoft has its own antimalware deployed to prevent intrusions into VM's and cloud services, but also supports third-party malware security solutions within subscriptions. The Azure security center serves as a main control for all client security concerns.

    Key Security Features within Azure:

    Security Center:

    From Azure Security Center you can manage all of your security needs within Azure as well as hybrid implementations between on-premise and cloud instances. This console provides a unified view of your cloud resources with discoverability across networks to on-site infrastructure, allowing for management of security and application of policies from a central location. Central management of resources including collection, analysis and search are all available in the Security Center where vulnerabilities can be found and re-mediated.

    Application Gateway:

    The Application Gateway provides application routing and load-balancing services within Azure. The Gateway is highly available and extremely scalable based on client needs. Applications, including SQL, can be protected from the most common web-based threats, vulnerabilities and exploits with a web application firewall. The Application Gateway is well integrated with other Azure services including the Azure Traffic Manager.

    VPN Gateway:

    With the use of Internet Protocol Security (IPSec) and Internet Key Exchange (IKE), Azure's VPN Gateway provides secure connectivity between more on-premise infrastructure and cloud-based resources. Point to site VPN provides VM connectivity across Azure Virtual Networks for availability even for users on the road.

    Azure DDoS Protection:

    Monitoring is always on for the Azure DDoS protection, providing adaptive tuning, application layering, complete integration with all of Azure's security features and analytics. Protection is simplified with immediate monitoring of all cloud resources to mitigate all detected attacks. Azure's DDoS protection also works through layers three through seven to counteract common threats such as SQL injections. This feature also includes alerts and telemetry reports for better understanding of any attempted attack and how to mitigate vulnerabilities against web applications. Microsoft even uses service credits as a protection against resource costs incurred from documented attacks.

    Key Vault:

    With cloud services, encryption keys are very important and Key Vault provides effective and secure storage of highly accessible keys which can be linked to applications for simple, high performance protection. The Key Vault covers passwords, secrets and policies which can be applied to cloud resources with the easy creation and migration of new vaults in a matter of minutes without the need for provisioning delays. Certificate provisioning for SSL/TLS achieves fast enrollment and automatic renewal from public CA sources.

    Azure Information Protection:

    All data can be protected with Azure's Cloud Services including tracking and monitoring of behaviors to block intrusion activities. Information can be classified through policies in such management categories as automatic, recommended or controlled by users. Protection can be extended no matter how it is shared or stored. Share controls can be passed even to customers and enacted in Microsoft Office with recommendations available for data handling for users.

    Azure Active Directory:

    Complete identity management is available in Azure Active Directory where resources can be safeguarded by intelligence driven security policies. Azure AD is highly integrated with Office 365 and enables a higher level of productivity with security and management centralized. With one identity, users can access available applications through a web-based portal along with web application availability for on-premise and mobility access.

    Azure Advanced Threat Protection:

    Threats continue to mount regularly so Microsoft's Azure's security also includes advanced threat protection. Azure Advanced Threat Protection adds the power of cloud resources to effectively detect and investigate intrusions and threats. Device, user and resource usage is monitored to determine any security anomalies for fast insight and response. The security measures improve responses to attacks and leverage Microsoft's Windows Defender Advanced Threat Protection for attack remediation

    Conclusion

    Delving a bit deeper into these security categories, it becomes clear that Microsoft has created its cloud environment with security as a top goal. However, it is important to remember that security is an ongoing role in which cloud customers have, and must, retain a role. Merely possessing a cloud presence does not remove the responsibility from the organization to follow best practices and determine policies which must be applied and followed using the available tools in Azure Cloud Services.

    To learn more about Azure Cloud Security, contact the Certified Microsoft professionals at RoyalDiscount.com - your online resource for all things Cloud.


  • Choosing between Microsoft SQL Server 2016 Standard and Enterprise Editions

    sql server plan comparison

    When Microsoft released SQL Server 2016 there were a number of changes and improvements to the flagship database. However, when considering the two main editions, Standard and Enterprise, organizations must account for a long list of features before choosing between them. Let's take a look at the main differences between the two editions regarding these available options.

    First, it is important to understand the intentions behind the two different editions. The Enterprise edition provides high end data-center availability, incredible performance, a wide array of business intelligence, unlimited virtualization and user access to data reporting. The Enterprise edition is well worth the higher licensing cost for large enterprises because of how much it offers.

    Meanwhile, the Standard edition of SQL Server 2016 offers many of the same features with limitations. This edition is intended to provide smaller organizations with all the necessary tools and features at a lower cost and less need for IT staff. The Standard edition is a highly effective version for business customers looking for budget relief.

    However, there are those customers who may need to further examine SQL Server 2016 features in order to effectively make choices between the two editions. This is where a careful examination of all the features coupled with licensing can save some companies cost, while others may quickly find they need far more database computing power.

    Since the SQL Server 2016 database engine is designed for far faster performance, the needs of an organization fall into the specifics designed into each edition. Digging further into the details can provide an assessment which enables Microsoft customers to make the best-informed decision for both immediate and long-term needs. Here are some highlights when to consider among the many options and features of SQL Server 2016 when choosing an edition:

    Scale Limits

    When it comes to scale limits, SQL Server 2016 Enterprise edition provides either unlimited memory or up to operating system limits. However, the Standard edition has a variety of memory limitations which must be considered when designing database applications. It is most important to remember that Standard edition is limited to the lesser of four sockets for 24 cores when it comes to the database engine, analysis services or reporting services. Other components may also be limited in the Standard version so it's important to check for those limitations. But, it is also an important to realize that both Standard and Enterprise editions have a maximum relational database size of 524 PB. 

    RDBMS High Availability

    Comparing RDBMS high availability between the two editions, users will find that there is plenty of overlap available when it comes to features. There are a number of features that are not available in the Standard edition, some of the most important being the lack of always-on availability groups, online page and file restore, online indexing, online schema change, fast recovery, mirrored backups, and hot add memory and CPU. 

    RDBMS Performance and Scalability

    Almost all of the RDBMS performance and scalability features are available in both Enterprise and Standard editions with the exceptions being: resource governor, partition table parallelism, NUMA aware and large page memory and buffer array allocation, and I/O resource governor. 

    RDBMS Security

    SQL Server 2016 Standard edition strongly mirrors RDBMS security from that of the Enterprise edition except for transparent database encryption and extensible Key management.

    Replication

    The Standard edition also comes with many of the same replication features as that of the Enterprise edition, the exceptions being: Oracle publishing, peer-to-peer transactional replication, and transactional replication updateable subscription. 

    Management Tools

    The management tools available in the Enterprise edition are exactly the same within the Standard edition.

    RDBMS Manageability

    Standard Edition possesses many of the same RDBMS manageability features, though there are some which are not included that are available in the Enterprise edition. These features are: parallel indexed operations, automatic use of indexed view by query optimizer, parallel consistency check and SQL Server Utility Control Point. 

    Development Tools

    SQL Server 2016 Standard and Enterprise editions provide the exact same development tools which is a strong benefit either way.

    Programmability

    Almost all of the available programmability features of the SQL Server 2016 Enterprise edition are available in the Standard edition with the exception of advanced R integration and R server (standalone). This means there are a wide array of programming options available even in the Standard edition. 

    Integration Services

    Basic integration services are available in both additions, but advanced sources and destinations as well as advanced tasks and transformations are not available in the Standard edition 

    Master Data Services

    Master data services are only available in the Enterprise edition, making the higher cost a consideration for those organizations in need of these features. Contact one of our specialists for more details. 

    Data Warehouse

    There are several integration services features not available in the Standard edition which include: star join query optimizations, scalable read only analysis services configuration, parallel query processing on partitioned tables and indexes, and global batch aggregation. 

    Analysis Services

    The Standard edition does not provide support for scalable shared databases or synchronize databases, while AlwaysOn failover cluster instances only supports two nodes. 

    BI Semantic Model (Multidimensional)

    Standard edition of SQL Server 2016 does support many of the same business intelligence semantic model (multidimensional) features as the Enterprise edition. Check with one of our specialists for more details.

    BI Semantic Model (Tabular)

    Business intelligence is an important inclusion within the Standard edition of SQL Server 2016. The Standard edition supports all of the same tabular models as the Enterprise edition except for Perspectives, Multiple partitions, and DirectQuery storage mode. 

    Power Pivot for SharePoint

    The Standard edition of SQL Server 2016 does not provide any support for Power Pivot for SharePoint so if this is a major consideration for an organization the Enterprise edition may be the choice since SharePoint is a powerful Microsoft product in wide use. 

    Data Mining

    While the Enterprise edition of SQL Server 2016 does support a wide array of data warehouse features, the Standard edition only supports standard algorithms and data mining tools (Wizards, Editors, Query Builders). 

    Spatial and Location Services & Additional Database Services

    All features and options for spatial and location services as well as additional database services are the same between SQL Server 2016 Enterprise and Standard editions. 

    Other Components

    StreamInsight HA is not available in the Standard edition.

    Conclusion 

    Choosing between Microsoft SQL Server 2016 Standard and Enterprise editions encompasses a wide array of factors. It is very important to remember that with the 2016 version, Microsoft included Business Intelligence features for the Standard edition to provide non-enterprise class customers with these useful options at a limited availability. Likewise, the Standard edition widely mirrors the Enterprise edition in terms of available features, only with limitations to scalability.

    Purchasing an Enterprise edition license most frequently means unlimited scalability for many features not available for the Standard edition. Customers must consider between the two when needs might require the higher-end edition. However, it is also easier than ever to upgrade the Standard edition to the Enterprise edition so expected database growth can also be accounted for with the possibility of business growth. Customers who choose a Standard edition will find it easier to grow as necessary by moving into the Enterprise edition when necessity requires a change.

    To learn more about SQL Server, contact the licensing experts at RoyalDiscount.com- Your online source for cheap OEM, Retail & Cloud products.