SQL Server

What Are the Four-Part Names of a SQL Server Object?

By Garth Jones The purpose of this blog post is to help you identify the four possible parts of a SQL Server object. System Center Configuration Manager (SCCM) reports are created by querying the SCCM database. Most often SQL Server Reporting Services (SSRS) is used to display SCCM reports. When querying the SCCM database, SQL Server Query Language, oftentimes called T-SQL, is used. T-SQL queries generally use a two-part SQL Server view name. This is found within the From section of your query. Here’s an example of a two-part SQL Server view name: dbo.v_R_System_Valid. By now you are probably asking yourself, “Why is this important?” The answer is linked servers. Using linked servers will allow you to query a cloud-hosted SQL Server or any remote database server. In order to query a cloud-...

Using SQL Server on Linux for the Tachyon Databases

Sponsored A guide to using SQL Server on Linux for the Tachyon databases. After installation of SQL Server, then what? How can you integrate Tachyon? This guide will help. Why Would I Try This Out? First of all, let’s emphasize that running the Tachyon databases on Linux is not supported by 1E. However, “not supported” doesn’t mean “it won’t work.” Rather, it means “don’t call us if it doesn’t work.” When I found out that Microsoft had released SQL Server on Linux I thought that it would be a valuable experience to spend a small amount of time trying it out, mainly to satisfy my curiosity about how well it would work and how easy or difficult it would be. Installing SQL Server on Linux The documentation pages (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup) describ...

Latest SSRS Report Viewer Now Generally Available

After months of testing, Microsoft has now released its latest update to the Report Viewer for SQL Server generally available. Available here: Microsoft Report Viewer Control for ASP.Net Web Forms applications Note that this supersedes the Report Viewer 2015 version and includes several enhancements we made for SSRS 2016: modern browser support, cross-browser printing, report parameter positioning, and a modern look-and-feel. It also works with SSRS versions 2008-2017.   Looking for an awesome, no-nonsense technical conference for IT Pros, Developers, and DevOps? IT/Dev Connections kicks off in San Francisco in 2017!

Microsoft Releases SQL Server 2017 Reporting Services RC1

Just a couple days after delivering the first Release Candidate for SQL Server 2017, Microsoft has now also made Release Candidate 1 of SQL Server 2017 Reporting Services available. Download SQL Server 2017 Reporting Services (RC) For SSRS 2017, Microsoft has separated the installation from the general SQL Server 2017 package. The licensing has not changed (i.e., SSRS is still included with SQL Server), but the way it installs is different – it is a separate installation.   Looking for an awesome, no-nonsense technical conference for IT Pros, Developers, and DevOps? IT/Dev Connections kicks off in San Francisco in 2017!

RC1 of SQL Server 2017 Released

The SQL Server team at Microsoft has been working at a maddening pace. With SQL Server 2016 in the books, the 2017 version has already hit the first Release Candidate. We are pleased to announce availability of the first public release candidate for SQL Server 2017, Release Candidate 1 (RC1), which is now available for download. This means that development work for the new version of SQL Server is complete along most dimensions needed to bring the industry-leading performance and security of SQL Server to Windows, Linux, and Docker containers. Full announcement: First release candidate of SQL Server 2017 now available SQL Server 2017 provides deeper focus on a cloud-first database platform and brings Linux installations and containerization mainstream. Looking for an awesome, no-nonsense t...

Setting up SQL Server High-Availability between Windows and Linux with SQL Server 2017

Tobias and Slava are back in the studio in this awesome video to show high-availability with SQL Server 2017 between Windows and Linux. After spending a few minutes discussing the background, thought process, discussions that went on trying to determine if this was even possible, and the applicable scenarios, Tobias jumps right into the demo [06:20] to show that yes, Virginia, it is possible, and that it is quite easy to create and configure Availability Groups on both Windows and Linux.   Looking for an awesome, no-nonsense technical conference for IT Pros, Developers, and DevOps? IT/Dev Connections kicks off in San Francisco in 2017!

Five Tips for Query Designer

In Garth Jones’ previous blog post, Query Designer in SQL Server Management Studio (SSMS), he showed you how to access Query Designer (also known as Design Query in Editor and Graphical Query Designer) within SQL Server Management Studio (SSMS). With Query Designer you can create SQL Server queries by using a graphical interface. The benefit of using this tool is that as you change or select items within the interface, the SQL Server query is automatically updated for you! Read more about the five tips that Garth Jones thinks will make your experience with Query Designer even better! For more details about Query Designer, please see these online docs: Graphical Query Designer and Graphical Query Designer User Interface.

Sync On-premises SQL Server Data with SQL Azure Databases

SQL Azure Data Sync is a Microsoft Windows Azure web service that provides data synchronization capabilities for SQL Azure databases. SQL Azure Data Sync allows data to be synchronized between on-premises SQL Server databases and SQL Azure databases; in addition, it can also keep multiple SQL Azure databases in sync. The Data Sync Agent is installed on a local server and runs as a Windows service. It can be installed on a database server or on any server that can communicate with the SQL Server databases. The SQL Server databases to be synchronized are registered with the Agent; it then securely transfers data changes between the SQL Server databases and the SQL Azure Data Sync service. Download: SQL Azure Data Sync Agent Looking for an awesome, no-nonsense technical conference for IT Pros...

How to Set SQL Server Integration Services (SSIS) Protection Levels

By Garth Jones I’m using SQL Server Integration Services (SSIS) to clone a database. I’m doing this for a customer in order to off-load the querying and reporting functions from their main Configuration Manager database server. When I built the SSIS package, I needed to embed a few usernames and passwords. SSIS encrypts these details, so that they are not accessible by anyone. By default, only the user who creates the package can run the package. Unfortunately, in this case, that wasn’t desirable. After following the advice of a SSIS guru, I changed the package to encrypt everything using a password instead. My hope was that this would allow anyone with the password to run the package. When I set the password on the package and executed it, however, the following error occurred: Like any g...

Error: 40 – Could Not Open a Connection to SQL Server

By Garth Jones I’m working on a project to create a SQL Server reporting database; basically it’s a clone of the ConfigMgr database for querying and reporting only. This server will act as a secondary database server where all reporting can be offloaded here and not affect the main SQL Server. By the way, I’m testing different methods of duplicating the database and trying to confirm what is officially supported by Microsoft, but that’s a different story… In this particular case, I created a few VMs to replicate the new reporting database. I ran into problems, however, when I tried to use SQL Server Management Studio (SSMS) to connect the new SQL Server database. Below is the error message:   A network-related or instance-specific error occurred while establishing a connection to SQL Serve...

How to Install SQL Server Integration Services Tools

By Garth Jones I’m working on a project to clone a SQL Server database (aka the ConfigMgr database) because a customer wants all querying and reporting off-loaded from the main ConfigMgr database server. Instead of using SQL Server Replication we’re going to use SQL Server Integration Services (SSIS) to move a backup of the database from one server to another server. In order to accomplish this task, I will install SSIS on my workstation. I find it useful to have SQL Server Data Tools (SSDT) (SSIS is included within the SSDT tools) on my workstation as it allows me to manage SQL Server, create SSIS packages without having to RDP to the server to launch the tool, and to perform simple edits of my SSIS packages. SQL Server Data Tools (SSDT) are NOT installed as part of a SQL install anymore,...

Microsoft Introduces New SQL Server Tools: mssql-scripter and DBFS

Alongside launching SQL Server 2017 Community Technology Preview (CTP) 2.1 today, Microsoft has also announced two new command-line tools: The mssql-scripter tool enables developers, DBAs, and sysadmins to generate CREATE and INSERT T-SQL scripts for database objects in SQL Server, Azure SQL DB, and Azure SQL DW from the command line. The DBFS tool enables DBAs and sysadmins to monitor SQL Server more easily by exposing live data from SQL Server Dynamic Management Views (DMVs) as virtual files in a virtual directory on Linux operating systems.   Looking for an awesome, no-nonsense technical conference for IT Pros, Developers, and DevOps? IT/Dev Connections kicks off in San Francisco in 2017!