Import/Export Wizard Simply Explained

Import/Export Wizard is an utility program you find in SQL Server (almost all versions) that can be used for Exporting or Importing data between SQL Servers; between SQL Server and a number of other data sources; or import/export data between data formats including databases, spreadsheets and text files.

DTSW_00 and DTSW_01.png

Export/Import utility can be launched from a database node in a SQL Server or from command line by typing in DTSWizard at command prompt.
The Welcome screen of the Wizard is displayed as shown.

DTSW_02

Let us consider a simple case of copying a database from SQL Server 2012 to SQL Server 2014. I am choosing this pair as I have the two servers on my computer.

DTSW_03
In particular, I will be exporting the pubs database in SQL Server 2012 to the named instance of SQL Server 2014.

DTSW_04

In the SQL Server Import Export Wizard (DTSW) I launched from command-line earlier, I click Next.
The Choose a Data Source page is displayed as shown.

DTSW_05

Click on the drop-down handle for Data source: as shown in the next image. You will see the various data sources handled by this wizard.

DTSW_06

We shall use the default data source, in this case the SQL Server Native Client 11.0. Note that the program automatically chose the named instance REGENCYPARK (SQL Server 2012). However, both the SQL Servers are accessible to this program. Since we are exporting from SQL Server 2012 to SQL Server 2014, RegencyPark server is correct. We will export to Everest server.

DTSW_07

Since both servers were originally installed to work with Windows Authentication accept the default. There is a <default> Database. Click on the handle and set it to pubs by choosing it in the drop-down. Do not worry if you do not have pubs. It should work with any other choice.

DTSW_08
With all the choices made so far, the Choose a Data Source window should appear as shown.

DTSW_09

Click Next to display the Choose a Destination screen as shown.

DTSW_10

Enter the following on this screen:
Destination: SQL Server Native Client 11.0
Server Name: Hodentek8\EVEREST
Authentication: Windows
Click on the handle for Database. The following drop-down is displayed.

DTSW_11

Click on the button New... to open the following Create Database window.

DTSW_12

Insert a name at the top. Herein pubs2. Accept all other defaults, and the database files that are going to be created are displayed inside the screen The OK button at the bottom becomes active. Click OK.
 The Choose a Destination window appears as shown.

DTSW_13

Click Next> to display the Specify Table Copy or Query as shown.

DTSW_14

Accept the default, Copy data from one or more tables or views. Click Next> to display the Select Source Tables and Views with check boxes for each of the tables or views in the pubs database.

DTSW_15

Place check mark for the checkbox Source: and all the tables will be selected as shown.

DTSW_16

We will not edit the mappings (how the tables should go into the destination). Of course you can click Help to get to the help menu.

Click Next. The Save and Run Package is displayed. We accept the first option (Default) Run immediately. The other option is to save it as a SQL Server Information Services (SSIS) package.

DTSW_17

Click Finish>>| (you could also click Next> and look at some more details of what you have chosen to do so far). Complete the Wizard screen is displayed as shown:

DTSW_18

Click Finish on this screen. The program enters an execution stage and finally comes up with a screen announcing success and the number of rows transferred etc. as shown.

DTSW_19

Click on the Report button to display this drop-down list. You may choose an action you want to take.

DTSW_20
Now, go to the EVEREST server in SQL Server Management Studio for version 2014 and verify that pubs2 has been created and it has tables and data.

DTSW_21

That's it. Thanks for reviewing this post. 

Creating a Scalar-valued function in SQL Server 2014

First of all what is a Scalar-valued function?

A scalar function in SQL Server is similar to the generally accepted meaning function, a function is defined by the variables (parameters) with some operation on them and the resulting value is the return value of the function.

• If x and y are the variables(parameters) than the
  function(x,y)=x+y defines a scalar function that returns a value which is the sum of x and y.
• The scalar function is not limited to just two parameters and can have more.

Where do you find the Scalar-valued function in the Object Explorer?

You will find the Scalar-valued function node in all the databases in the Programmability node as shown here for AdventureWorks2012 database.

Sway5_01

How do you create a Scalar-Valued Function in SQL Server Management Studio?

Watch this Swaytorial.

https://docs.com/jayaram-krishnaswamy/9721/creating-a-scalar-valued-function-in-sql-server

Creating a Scalar-valued function in SQL Server—Jayaram Krishnaswamy

Docs.com

Problem with SQL Server 2014 Report Builder 3 and the fix

This problem surfaced while installing SQL Server 2104 Report Builder on a Windows 10 (x64) Pro machine which has a SQL Server 2014 (x64) Developer Edition installed and a properly configured Report Server.

The Report Builder software was downloaded from this Microsoft Site:
https://www.microsoft.com/en-us/download/details.aspx?id=42301

ReportBuilder_01

Double clicking the downloaded MSI file above started the installation. The program looks for a target report server and it was provided as shown:

ReportBuilder_02

The Report Server and the Report Manager both do not have any problem. However this ends up with an unsuccesful installation with this message:

ReportBuilder_03

The work around for this is to not provide a target report server and ccontinue with the installation. The installation succeeds this time. After a little while an gets added to the All Icons group as shown.

ReportBuilder_04

SQL Server 2005 nears EOL

It is sunset time for SQL Server 2005 as it reaches end of life  (EOL). While EOL may be an extreme word to use, it is going to be End of Support (EOS) by Microsoft beginning April 2016.
SQL Server 2005 was the beginning of a new era for SQL Servers and according to information available on the Internet, SQL Server 2005 is still used extensively tied up with Windows Server 2003.
There are a couple of upgrade options and probably most go for either SQL Server 2012 or SQL Server 2014. Perhaps it may be better to go the whole 9 yards and get SQL Server 2016 which is in CTP right now.

The good news (for me) is that SQL Server 2005 will continue to stay for few more years and I can still sell my book.

https://www.packtpub.com/networking-and-servers/beginners-guide-sql-server-integration-services-using-visual-studio-2005

Overview of what you can learn from Database and Analytics sessions at Visual Studio Live!

The field of Data, big and small,l made a great leap forward from mere file based data to relatiional database systems and remained there for a long time and it is still very much used. However, the advent of Internet and social media created new needs not addressed by RDBMS and gave rise to NoSQL databases. On the other hand the enormous amount of data brought in by media made it look for other tools bringing Big Data, now a byword. The sessions treat the enormous devveloping field in this fascinating area.

If you do manage to attend you can get some new information and brush up some old stuff. It should be interesting to track the developments.

These are the session details in the Database and Analytics area.

SQL Server of course is center stage. Topics under SQL Server 2014 covers the following:

• SQL Server Data Tools (SSDT)
• Reporting Services for Developers
• Memory-Optimized Tables and Columnstore Indexes in SQL Server 2014
• Microsoft Azure SQL Database

TH03 Implementing Data Warehouse Patterns - Attendees Choose
10/01/2015   8:00am - 9:15am
You will learn:
•Techniques for Populating Fact Tables and Dimension tables using T-SQL MERGE statements
•Special considerations for managing Type 2 Slowly Changing dimensions
•Tips for implementing many of the patterns found in the Kimball methodology
 You will also get to understand the following:
Type 2 changing dimensions
Confirmed dimensions
Dimension outriggers
Role-playing relationhsips
Many-tomany bridge table relationships
Factless Fatc tables
etc.

TH07: Power BI 2.0: Analytics in the Cloud and Excel
10/01/2015   9:30am - 10:45am
Get to speed quickly on what Power BI has done since version 1 and what's new in Version
Try to read up on my posts in blogs and you might have already learnt a lot!
TH11: Busy Develoepr's Guide to NoSQL
10/01/2015   11:00am - 12:15pm

You will learn:
•About NoSQLs
•What NoSQLs offer that the RDBMS don't
•When and how (and when not) to use them

TH15  Big Data and Hadoop with Azure HDInsight

10/01/2015   1:30pm - 2:45pm

 

• Learn what Hadoop is, how it works, what to do to get it running on the Azure cloud and how to integrate it.
• Learn how to integrate it with SQL Server BI, Excel and third party tools

TH19  Predictive Analytics and Azure Machine Learning
10/01/2015   3:00pm - 4:15pm

You will learn:
•Learn the fundamentals of predictive analytics, including which algorithms work best for specific scenarios
•Learn how to build Azure Machine Learning experiments and models
•Get exposed to the R programming language and see how to integrate R code into Azure ML experiments

W19  SQL Server Reporting Services - Attendees Choose Topics
[This presentation takes some of the more complicated topics and offers them as choices that attendees can pick for the session.]
You will learn:
•About advanced SSRS features "in action", as implemented in actual SSRS client applications
•The implementation of mass-automated report delivery of data driven subscriptions in SSRS (and also see .NET code used in SSIS scripts for customized delivery of reports)
•A general understanding of Microsoft's current and future offerings for SSRS in the Cloud

W15  Transact-SQL for Application Developers - Attendees Choose Topics
09/30/2015 3:00pm - 4:15pm
[Attendees choose from 25 possible topics - plus you'll walk away with all 25 code samples.]
You will learn:
•About the new language enhancements in SQL 2012
•Where sub-queries are necessary
•To gain some appreciation of performance optimization

W11  Real World SQL Server Data Tools
09/30/2015   1:30pm - 2:45pm
You will learn:
•Techniques for importing existing legacy SQL Server schemas with old, dead code. Basically, how do you take the kind of messy, not-well-maintained databases that I see at customer sites and bring it into SSDT without losing your sanity and giving up?
•About real enterprise application databases that often have references across database boundaries, and how to identify and variable-ize these database objects and deal with cross-database and cross-SSDT project references.
•How to handle incremental automated deployment scenarios from the command line or TFS Build and manage 'lookup' data as part of these deployment scenarios.

M01  Workshop: Big Data, Analytics and NoSQL: Everything You Wanted to Learn But Were Afraid to Ask
09/28/2015  9:00am - 6:00pm
"Who has time to learn about Big Data, Analytics and NoSQL? "
Perhaps you can learn a little bit of all the following:
Big Data
NoSQL
HBase
Cassandra
MongoDB
DynamoDB
DocumentDB
HDInsight

Read more here:
https://vslive.com/Events/New-York-2015/Tracks/Database-and-Analytics.aspx

Manage your investments with SQL Server 2014 backend using Abacus

Manage your investments with SQL Server 2014 backend using Abacus
Prism Cybersoft has launched an Azure Cloud based investment accounting software with SQL Server 2014 backend. It will cost you $600/yr.

The novelty is it is cloud based. Is security a problem, Perhaps?
With abacus you can monitor your investment portfolio across various asset classes like equities, derivatives, mutual funds etc. and generate various kinds of reports. Managing it is easy from manual to file uploads or direct integration with stock brokers back office.
According to the CEO of Abacus,
“Abacus allows investors to monitor their financial assets across various asset classes like equities, derivatives, mutual funds, and bank fixed deposits. It also generates various financial statements like balance sheet and profit and loss account,”
More here:
http://www.prism.in/services.html
http://www.infotechlead.com/cloud/microsoft-azure-powers-investment-software-abacus-28750

SQL Server 2014 and Scalability Improvements with Windows Server 2012 R2

SQL Server 2014 now takes advantage of NIC teaming feature provided by Windows Server 2012 R2 to improve enterprise-class scalability resulting in reduced infrastructure cost and predictable
performance, the magic words (mantras) you love to hear.

But what is NIC Teaming?

NIC teaming also known as Load Balancing Failover(LBFO) is Load Balancing Failover(LBFO)- a feature that allows multiple network adapters on a computer to be placed in a team (group) for the following express purposes:

*Bandwidth aggregation
*Prevent connectivity loss in a network failure event

NIC Teaming architecture
Teaming architectures are pretty much the same across vendors. Baiscally two or more physical networks adpaters(nic)are connected to a NIC Teaming solution to multiplex them to one or more virtual adapters called team network adpaters (tnic) as shown.

Here is a teaming solution from VMWare.

There are two swithching algorithms used in NIC Teaming:

• Switch dependent mode: Switch participates in teaming
• Switch idnependent-mode: Switch makes teaming possible but does not participate

There are two common choices for teacming in switch-dependent mode:

• Generic or static teaming (IEEE 802.3ad draft v1) - prone to errors in configuration as it is static
• Dynamic teaming (IEEE 802.1ax, LACP)-Uses Link Aggregation Control Protocol and leverages automatic team creation.

Features of NIC Teaming in Windows Server 2012

Traffic distribution:

Hyper-V swithc port
Hashing Algorithms

There is both NIC Teaming Managment Interface as well as Powershell commandlets to manage teaming.

Your master reference to Microsoft NIC teaming is found here:
http://technet.microsoft.com/en-us/library/hh831648.aspx

Also read up on this:
http://www.vmware.com/files/pdf/support/landing_pages/Virtual-Support-Day-Best-Practices-Virtual-Networking-June-2012.pdf
https://learningnetwork.cisco.com/docs/DOC-3729

SQL Server 2014 is ready to download. Download and Install now

SQL Server 2014 has lots of new stuff. If you worked with SQL Server  2012 it is time to upgrade. If you experimented with  SQL Server 2014 CTP1, you need to completely uninstall it.

 

http://hodentek.blogspot.com/2014/03/you-need-to-uninstall-sql-server-2014.html

 

Here are some highlights of this edition:

http://hodentekmsss.blogspot.com/2014/03/sql-server-2014-will-be-available-on.html

 

 

 

 

You can download SQL Server 2014 from the Technet Evaluation Center here:
http://technet.microsoft.com/en-US/evalcenter/dn205290.aspx

You can also download the express version here:
http://msdn.microsoft.com/en-US/evalcenter/dn434042.aspx

You get the following programs in the express edition:

• LocalDB (SqlLocalDB)
• Express(SQLEXPR)--Database Engine only
• Express with Tools(SQLEXPRT)---Install & Configure database server+ SSMS
• SQL Server Management Studio Express(SQLManagementStudio)
• Express with Advanced Services(SQLEXPRADV)--includes Full Text Search & Reporting Services

Summary of some basic requirements:

• .NET 3.5 SP1 is a requirement and is not installed by set up.

Download the above from here:

http://www.microsoft.com/en-us/download/details.aspx?id=22

• .NET 4.0 is also a requirement but the installaiton program downlaods and installs it.
• PowerShell 2.0 is also a prerequisite and you have to install it if it is not present.

Operating Systems on which you can install:

Full list can be accessed here:
http://msdn.microsoft.com/en-us/library/ms143506(v=sql.120).aspx#hwswr

• Enterprise and Business Intelligence versions installs only on Windows Servers (2012 and 2008 R2)
• Standard Edition can be installed on Windows 7(x32 and x64) and windows 7 SP1(Ultimate to Professional)
• SQL Developer covers the widest Windows operating Systems.
• SQL Express can be installed on above mentioned Servers as well Windows 8, Windows 8.1 and windows 7 SP1

 

In SQL Server 2014 you can use Multidimensional model to create Power View

This feature is new in SQL Server 2014.

In SQL Server 2012 Reporting Services you had the option to create Power Views using the Reporting Services Service in SharePoint 2010. In order to create the Power Views you needed to create tabular data model in SQL Server Analysis Services 2012. You needed a separate instance of SQL Server Analysis Services installed to support Tabular Data Model as the default was Multi-dimensional.

With SQL Server 2014 Power Views can be created using Multidimensional models, the same model used in OLAP Cubes. Just as Tabular Data Models supported Data Analysis Expressions(DAX), multi-dimensional models also support querying with DAX.

Power View is not exclusive to SharePoint but can be created using Excel 2013 although somewhat limited in features compared to those created using SharePoint.

Chapter 6: Power View and Reporting Services

This chapter in my book describes how to create Power Views from scratch.

Hekaton becomes SQL Server 2014

Blocking and Locking will be a thing of the past

In-memory OLTP Processing:
SQL Server 2014 (former codename Hekaton) has built-in, in-memory capability for OLTP Transactions with not so expensive hardware requirements. The release date of SQL Server 2014 was annonuced at its TechEd conference in New Orleans this week.

The ink is still fresh on my new book, Learning SQL Server Reporting Services 2012 and Microsoft has announced another version already. Well, Microsoft is known for its relentless offerings. Transactions can be disk/memory based which does not require expensive boxes and with this feature Microsoft tries to distinguish itself from the likes of SAP Hana (Oracle Database with its Exadata and Exalytics). The performance is very fast but only 50 times faster but not Hekaton faster (Hekaton means 100!!!).

In-memory Business Intelligence:
This is already a reality. Look at the terrific features of Power View. You can go two ways with Power View: Power Pivot via EXCEL or Tabular Model with SSAS.

Get a jump start from scratch on your acquaintance  with Power Views with my forthcoming book,
Learning SQL Server Reporting Services 2012

http://hodentek.blogspot.com/2013/05/some-details-of-my-book-on-sql-server.html


Read more on SQL Server 2014 here->

http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/03/sql-server-2014-unlocking-real-time-insights.aspx
http://searchsqlserver.techtarget.com/news/2240185188/SQL-Server-2014-due-out-in-late-2013-in-memory-OLTP-a-big-feature
http://www.techweb.com/news/240062566/microsoft-in-memory-move-challenges-sap- tiwht wyaoracle.html.