A Fine Slice Of SQL Server
Claudio Silva takes us through a change to several cmdlets in dbatools:
I wondered why and asked the Windows team if they could provide any insight. A colleague explained to me that I needed to change three things to make my remoting commands work on our network:
1. Use the FQDN on
-ComputerNameand/or-SqlInstanceparameters
2. Use-UseSSLparameter on theNew-PSSessioncommand
3. Use-IncludePortInSPNparameter for theNew-PsSessionOptioncommand
Read the whole thing.
Comments closedMine Tokus covers the benefit of Azure Dedicated Hosts:
Recently introduced, Azure Dedicated Host Preview provides single-tenant physical servers that can host one or more virtual machines. With this new hosting model, physical server is dedicated to your organization and capacity isn’t shared with other customers. Physical server-level isolation helps to address security and compliance requirements, brings visibility and control over the server infrastructure and enables significant cost savings and licensing flexibility for SQL Server workloads on Azure VMs.
I think this might get some recalcitrant large companies to be willing to adopt cloud technologies.
Comments closedAmit Banerjee announces SQL Server 2019 Release Candidate 1:
Today we’re announcing the availability of the first public release candidate for SQL Server 2019, which is now available for download. SQL Server 2019 brings the industry-leading performance and security of SQL Server to Windows, Linux, and containers and can tackle any data workload from business intelligence to data warehousing to analytics and AI over all your data both structured and unstructured.
Amit’s update covers the span of what we’ve seen in all of the CTPs. I went through the release notes and did not find a huge amount of detail on what went into RC1 versus CTP 3.2. But the fact that they’re up to RCs means that SQL Server 2019 is getting close to release.
Comments closedGilbert Quevauvilliers shows how we can connect to Amazon Redshift from Azure Analysis Services:
I am busy working with a customer and had a challenge when using Azure Analysis Services to connect to Amazon Redshift via an ODBC connection.
The first issue that I encountered was the following error: OLE DB or ODBC error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application; AWS PROD. This lead me to a few websites and the one that got me to my solution was Tabular: Error while using ODBC data source for Importing Data
Below are the steps on how I installed, configured and got the connection and refresh working.
Read on for those steps.
Comments closedJan Fischer takes us through Mean Absolute Percentage Error as a measure of forecast quality:
Particular small actual values bias the MAPE.
If any true values are very close to zero, the corresponding absolute percentage errors will be extremely high and therefore bias the informativity of the MAPE (Hyndman & Koehler 2006). The following graph clarifies this point. Although all three forecasts have the same absolute errors, the MAPE of the time series with only one extremely small value is approximately twice as high as the MAPE of the other forecasts. This issue implies that the MAPE should be used carefully if there are extremely small observations and directly motivates the last and often ignored the weakness of the MAPE.
Jan also points out a couple of things people criticize MAPE for incorrectly, but several things for which it is actually guilty. It’s not a bad measure if you can make certain data assumptions, but Jan has a few alternatives which tend to be better than MAPE.
Comments closedEd Elliott continues a series on spark-dotnet:
There are two approaches, one I have used for years with dotnet when I want to debug something that is challenging to get a debugger attached – think apps which spawn other processes and they fail in the startup routine. You can add a
Debugger.Launch()to your program then when spark executes it, a prompt will be displayed and you can attach Visual Studio to your program. (as an aside I used to do this manually a lot by writing an__asm int 3into an app to get it to break at an appropriate point, great memories but we don’t need to do that anymore luckily :).The second approach is to start the spark-dotnet driver in debug mode which instead of launching your app, it starts and listens for incoming requests – you can then run your program as normal (f5), set a breakpoint and your breakpoint will be hit.
Read on to see how it’s done, as well as a possibly-accidental benefit to this.
Comments closedClaudio Silva shares some tips on configuring dbatools:
Set a new configuration value
To update a value you need to use theSet-DbatoolsConfigcommand. Unfortunately, you will not find documentation for this command on our docs page. This is a known issue and it happens because that command is a cmdlet so the help is in the dbatools library itself.For this particular case, you can and should rely on the
Get-Helpcommand.
Get-Help -Name Set-DbatoolsConfig -Full
Claudio has a half-dozen or so settings and there are more available to you.
Comments closedJames Serra takes us through a new product announcement:
A brand new product by Microsoft called Azure Data Share was recently announced. It is in public preview. To explain the product in short, any data which resides in Azure storage can be securely shared between a data provider and a data consumer. It does this by copying a snapshot of the data to the consumer’s subscription (called snapshot-based copying, and in the future there will be in-place sharing). It currently supports ADLS Gen1, ADLS Gen2, and Blob storage, and eventually will support Azure Data Explorer, SQL DB, and SQL DW. Check out the Documentation and a video, and then go try it out.
You can share the data with a few clicks as long as the user you are trying to share with has access to an Azure Subscription and storage account. The copying and updating of the data is handled for you using the Microsoft backbone for best performance, and is encrypted during transit. You can specify the frequency at which the data consumers receive updates. It also is a simple way to control, manage, and monitor all of your data sharing.
This is a smart idea. Sharing data between companies is a key requirement in a lot of B2B solutions, yet methods for sharing range from high-development and medium-friction (create your own API) to low-development and high-friction (FTP, e-mail).
Comments closedMeagan Longoria shares thoughts on a change to the Azure portal:
Once your Azure SQL resources are created, you can use the new centralized management hub to administer them. Locate the Azure SQL resources blade to see a list of all of your single databases, database servers, elastic pools, managed instances, and virtual machines running SQL.
If your subscription has a lot of resources, this can be a timesaver.
Comments closedRandolph West shows a change to Accelerated Database Recovery in SQL Server 2019 CTP 3.2 and later:
ADR makes use of a per-database version store, instead of putting everything in the transaction log and TempDB. In most cases, the payoff more than makes up for valuable transaction log and TempDB storage. In my testing, enabling ADR requires around 10% additional storage in your database file.
The reason for this post, however, is to mention that SQL Server 2019 CTP 3.2 introduced the ability to choose which filegroup you want to use for the version store, which will help with performance.
Read on to see how you enable this.
Comments closed