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 witrh ASPHostPortal :: New Feature of SQL Server 2014 - SSD Buffer Pool Extension

clock June 11, 2014 07:54 by author Kenny

In this article I'm going to talk about one of the new feature of SQL Server 2014. As you know SQL Server 2014 contains some interesting new features. Although SQL Server Standard Edition is limited to 128GB of memory, teams deploying on Standard Edition have an option to fit more of their working set in low latency storage Buffer Pool Extensions that are introduced with SQL Server 2014. Well, the Buffer Pool is one of the main memory consumers in SQL Server. When you read data from your storage, the data is cached in the Buffer Pool. SQL Server caches Execution Plans in the Plan Cache, which is also part of the Buffer Pool. The more physical memory you have, the larger your Buffer Pool will be (configured through the Max Server Memory setting).

sql server 2014 with asphostportal.com

Creating a buffer pool extension for SQL Server 2014 is like being able to define a different page file in Windows. As data pages move into memory, they begin to fill up the buffer pool. If the buffer pool fills up, the less frequently used pages will be paged to disk. Then when they're needed again, they'll be swapped with something else in the buffer pool and moved back into memory. The buffer pool extension option allows you to define an SSD as a buffer file location. Because SSD is so much faster than spinning disk, the paging is considerably quicker, which increases performance dramatically in some cases. You can define a buffer pool extension file up to 32 times the size of your memory.

Prior to SQL Server 2014, the management of data and index pages in the buffer pool was handled by the buffer manager. This has not changed with the release of SQL Server 2014, a change in the architecture has allowed for creation of a hybrid buffer pool. The hybrid buffer pool now consists of the existing buffer pool plus an extension that resides on nonvolatile storage or an SSD. The buffer manager still deals with identifying those pages in the buffer pool that can be expunged, when required pages for a request are not residing in memory but on disk. This practice is still handled and undertaken by the buffer manager. In addition to the buffer manager deciding on which pages should be in the buffer pool or not, it is now identifying those pages which are considered as “clean pages” and migrating those pages out of the buffer pool onto the BPE to allow for even more data to reside in the buffer pool. This practice is providing the following benefits to your SQL Server environment (as stated in the Microsoft BPE article):

  • Increased random I/O throughput
  • Reduced I/O latency
  • Increased transactional throughput
  • Improved read performance with a larger hybrid buffer pool
  • A caching architecture that can take advantage of present and future low-cost memory drives


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