Windows 2012 Hosting - MVC 6 and SQL 2014 BLOG

Tutorial and Articles about Windows Hosting, SQL Hosting, MVC Hosting, and Silverlight Hosting

SQL Server 2014 Hosting with ASPHostPortal.com :: How to Restart an Interrupted SQL Server Database Restore

clock August 23, 2014 09:53 by author Kenny

SQL Server is Microsoft's relational database management system (RDBMS). It is a full-featured databse primarily designed to compete against competitors Oracle Database (DB) and MySQL.

Like all major RBDMS, SQL Server supports ANSI SQL, the standard SQL language. However, SQL Server also contains T-SQL, its own SQL implemention. SQL Server Management Studio (SSMS) (previously known as Enterprise Manager) is SQL Server’s main interface tool, and it supports 32-bit and 64-bit environments.

In this article, we will tell you about how to restart an interrupted SQL Server Database Restore.
Have you ever restored a large database on a Failover Cluster Production Server and while the restore was in progress, due to network failure, the restore failed? Once the SQL Server came up on the other node all the databases came up, except for the database which you were restoring prior to the failover. In this tip we will take a look at the command RESTORE DATABASE...WITH RESTART to see how this command can be helpful during such scenarios.

Here is the solution

The RESTORE DATABASE...WITH RESTART command is a very useful command which is available in SQL Server 2005 and higher versions. A Database Administrator can use this command to finish restoring an interrupted database restore operation.

In the below snippet you can see that ProductDB is in a (Restoring...) state once the SQL Server came online after the unexpected failure.

During such scenarios one can execute the RESTORE DATABASE...WITH RESTART command to successfully complete the database restore operation.

Below are two commands.  The first gets a list of the backups on the file and the second does the actual restore with the restart option.

-- get backup information from backup file
RESTORE FILELISTONLY
FROM DISK ='C:\DBBackups\ProductDB.bak'
GO

-- restore the database
RESTORE DATABASE ProductDB
FROM DISK ='C:\DBBackups\ProductDB.bak'
WITH RESTART
GO

Below you can see that after running the RESTORE DATABASE...WITH RESTART command the database was successfully restored allowing user connectivity.



SQL Server 2014 Hosting with ASPHostPortal.com :: New Feature in SQL Server 2014 - Business Intelligence

clock June 16, 2014 07:24 by author Kenny

Business Intelligence - New Feature of SQL Server 2014

SQL Server 2014 includes business intelligence (BI) improvements to help build and support vast databases and data warehouses.

Microsoft has been pouring R&D resourcesinto building out its business intelligence (BI) feature set and the upcoming SQL Server 2014 (SQL2014) release will continue that trend. The new release includes enhancements to make data exploration easier, improvements in BI semantic modeling, new offerings to help build and support massive databases and data warehouses, and tools to ensure the quality and consistency of data. Here’s what SQL2014 will do for you with regards to business intelligence:

BI Semantic Model in SQL Server 2014

Microsoft’s improvements in their BI Semantic Modeling (BISM) enables users new ways to build out BI solutions the scale from small, single-person usage to huge Fortune 500 organizations, focusing on credible and consistent data.

Data Exploration Enhancements in SQL Server 2014

Microsoft has more than 300 million users who think of Excel when they think about manipulating data. It only makes sense to reinforce the relationship between Excel and to a wider extent Microsoft Office, as a front end for data manipulation, exploration, and visualization against a SQL Server back end all through the rich and familiar front end of Excel. Microsoft’s new PowerPivot add-in for Excel makes accessing and analyzing data very easy for end users. The new Power View browser-based add-in for Excel adds new, powerful means of visualizing data, wherever it resides. Other new tools include Power Map (formerly known as Project GeoFlow) and Project Data Explorer, for better mapping and geographic data integration and data import into Excel for heterogeneous data sources, respectively.

Enterprise Information Management (EIM)

Enterprises need help controlling the spread of data silos and ensuring the quality and consistency of data. Microsoft has introduced or enhanced several tools to serve this requirement. The Data Quality Services (DQS) tools help enterprises and data stewards manage end-to-end data management by building a knowledge base of data-quality topics. Master Data Services (MDS) adds new features, such as an MDS add-in for Excel, to map objects, reference data, and control dimensions and hierarchies of data.

Big Data

Big data gets even easier in SQL2014. There are lots of new offerings to help build and support massive databases and data warehouses, such as scaling up to 15k partitions in a data store and up to 640 logical cores on high-end database servers. In addition, Microsoft has fully embraced Hadoop in the form of HDInsight, on Windows Azure and Windows Server, to take advantage of unstructured data and the parallel computational approach common to Hadoop applications. PolyBase, also new in SQL2014, is a feature of the SQL Server Parallel Data Warehouse (PDW) which makes combining nonrelational data and traditional relation data an easy and swift process.

Microsoft has just announced new integrated BI functionality for Office 365 Power BI for Office 365. Previous add-on options Data Explorer and Geoflow have been integrated into the BI Suite and have been renamed as Power Query (Data Explorer) and Power Map (Geoflow). Power Query enables users to pull in information from the web or external sources and merge it with local data, providing for an easier method for enriching your internal data. Power Map provides enhanced mapping capabilities and the ability to interact with any geographic data you may have.



SQL Server 2014 Hosting with ASPHostPortal.com :: Important New Features in SQL Server 2014

clock June 11, 2014 11:19 by author Kenny

Good news, Microsoft release SQL Server 2014 recently. SQL Server 2014 has several compelling new features that can definitely justify an upgrade. For OLTP, the most exciting new feature in SQL Server 2014 is In-Memory OLTP (aka "Hekaton"), which allows you to move individual tables to special in-memory structures. The performance boost can be as huge as 30x.

New In-Memory OLTP Engine

SQL Server 2014 enables memory optimization of selected tables and stored procedures. The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays. Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2012 when using this new feature.

Enhanced Windows Server 2012 Integration

SQL Server 2014 provides improved integration with Windows Server 2012 R2 and Windows Server 2012. SQL Server 2014 will have the ability to scale up to 640 logical processors and 4TB of memory in a physical environment. It can scale up to 64 virtual processors and 1TB of memory when running on a virtual machine (VM).

SQL Server 2014 also integrates with several new and improved features in Windows Server 2012 R2 and Windows Server 2012. For example, SQL Server 2014 supports the OSs' new Storage Spaces feature. With Storage Spaces, you can create pools of tiered storage to improve application availability and performance. In addition, SQL Server 2014 can take advantage of the OSs' Server Message Block (SMB) 3.0 enhancements to achieve high-performance database storage on Windows Server 2012 R2 and Windows Server 2012 file shares. Many enhancements were made to SMB 3.0, with the most notable being SMB Transparent Failover and SMB Direct. The new SMB Transparent Failover feature provides highly reliable SMB storage that's fully supported for applications like SQL Server and Hyper-V. With the new SMB Direct feature, you can leverage the NIC's Remote Direct Memory Access (RDMA) feature to provide access speeds for SMB file shares nearing the access speed for local resources.

Enhancements to AlwaysOn Availability Groups

AlwaysOn Availability Groups provides an effective solution for high availability for SQL Server 2012 / 2014 in on-premises deployments as well as for Windows Azure VM deployments. SQL Server 2014 also contains multiple enhancements for AlwaysOn Availability Groups. The maximum number of secondary replicas is increased from 4 to 8. When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads. There are also additional supportability enhancements.  Note that another enhancement is the Add Azure Replica Wizard. This will not be covered, since it requires VPN hardware etc. that is currently not easily available.

Updateable Columnstore Indexes

Things are changing now with SQL Server 2014, because Microsoft has resolved the above mentioned issues – with some magic and illusion: SQL Server 2014 provides you an Updateable Clustered ColumnStore Index! Let’s have a more detailed look on how this magic and illusion happens internally in SQL Server.
The first most important fact is that an underlying direct update of a ColumnStore Index is not possible! It would be too time consuming to do the complete decompress and compress on the fly during your INSERT, UPDATE, and DELETE transactions. Therefore SQL Server 2014 uses help from some magic: Delta Stores and Delete Bitmaps. Let’s have a more detailed look on both concepts.

SQL Server Data Tools for Business Intelligence

The BI aspects of SQL Server have also been improved. Data Tools for Business Intelligence are used to create SQL Server Analysis Services (SSAS) models, SQL Server Reporting Services (SSRS) reports, and SQL Server Integration Services (SSIS) packages. SSDT BI is based on Microsoft Visual Studio 2012. The database also supports Power BI for Office 365, with Power Pivot and Power View for data analysis and visualization.

Power View has been extended to provide support for multidimensional models, and for queries written using Data Analysis Expressions (DAX). A new Excel add-in, Power Query, lets you integrate data into Excel, and Power Map provides 3D mapping.

Why Should Upgrade?

Upgrading to a new release is usually a difficult decision for most organizations. However, SQL Server 2014's new In-Memory OLTP engine, with its promise of significantly improved application performance, offers a very compelling reason to upgrade for customers using SQL Server to support OLTP applications. One great way to find out the type of performance improvement that you might get out of SQL Server 2014's In-Memory OLTP capability is to download and install the SQL Server 2014 Evolution Edition and use the AMR tool to analyze your production workload. The AMR tool supports collecting data on SQL Server 2008 and later instances. This will give you a good idea of the type of performance improvements that you might expect to get using the new In-Memory OLTP engine as well as the changes that you might need to make to implement it. SQL Server 2014's In-Memory OLTP support promises to boost your database application performance to the next level



About ASPHostPortal.com

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Sign in