A Fine Slice Of SQL Server
Richie Lee has a Powershell snippet to replace text:
Below is a function that will search in a string and replace a character in a variable. Simple enough. It generally saves time in calling this from a helper file in source control for tidying up variables that are passed in to a Powershell function that aren’t quite formatted as required. A good example of this is upstream build variables with spaces in TeamCity.
Click through for the script.
Comments closedEd Elliott has a Visual Studio extension to run tSQLt tests:
What is this?
This lets you use Visual Studio to run tSQLt tests easily. Visual Studio has a built in framework for finding and executing tests so that if you have tSQLt tests in an SSDT project for example, although this just requires you have the .sql files in source control and does not require ssdt – you can easily see and execute your tests via the Visual Studio Test Explorer window. It is also available for use in your favorite build server via the vstest.console.exe tool or if you have vsts then you can use the default test task.
Combined with Visual Studio database tests for integration testing, this gives you a pretty fair start on database testing technology.
Comments closedOne of the most popular posts on this bog describes how to enable bitmap scaling is SSMS on high DPI displays, which is a sign that more and more people are starting to use 4K displays and are unhappy with SSMS’s behaviour at high DPI. The solution described in that post is to enable bitmap scaling, which renders graphic objects correctly, at the price of some blurriness.
The good news is that starting with SSMS 16.3 high DPI displays are finally first class citizens and SSMS does its best to scale objects properly. By default, SSMS will keep using bitmap scaling: in order to enable DPI scaling you will have to use a manifest file.
There is some setup work, but I am pleased that they’re doing this.
Comments closedTomaz Kastrun describes using Resource Governor with Microsoft R Services:
Setting external resource pool for execution of R commands using sp_execute_external_script has proven extremely useful, especially in cases where you have other workers present, when you don’t want to overdo on data analysis and get to much resources from others (especially when running data analysis in production environment) or when you know that your data analysis will require maximum CPU and memory for a specific period. In such cases using and definingexternal resource pool is not only useful but highly recommended.
Resource governor is a feature that enables you to manage SQL Server workload and system resource consumption their limits. Limits can be configures for any workload in terms of CPU, Memory and I/O consumption. Where you have many different workloads on the same SQL Server, resource Governor helps allocate requested resources.
If you’re concerned about R soaking up all of your server’s memory, Resource Governor is a great way of limiting that risk.
Comments closedMax Trinidad reports that Microsoft is making Powershell a cross-platform language:
This is PowerShell Core only, Alpha Version 6.0.0.9 and there’s a lot of work to do. Bugs and feedback are been submitted as the community are contributing for it success.
This version is also available for Windows 10 / Server 2016 and Windows 8.1 / Server 2012 R2. You can have it side-by-side with the current version of PowerShell.
Max has notes on how to install it on Ubuntu. Given that Microsoft is bringing Bash to Windows and Powershell to Linux, these are interesting times.
Comments closedJason Brimhall has a roundup of T-SQL Tuesday #081:
One of the tricks to becoming and staying a top tier data talent or professional is a perpetual cycle to learn, adapt, change, and evolve. We must be in a continual cycle of self evaluation and self modification. Let’s call this by something else – we must be agile. There I said the five letter word. Think about it in broad strokes with your career – it is a development process with perpetual evaluation, review and tweaks.
Now think about the invite and see how that fits with what I just said or with the, cough cough, agile flow. You start (albeit very basically) with a need for enhancement, then you plan which pieces of the enhancement you can accomplish, you then do the work (whether successful or not), then after you deliver the work you conclude with a retrospective (what went well and what needs to change). Yes! I do feel rather dirty for sneaking this on everybody like this. That said, when you think about the model and apply it in broad strokes to your career path – it has merit.
Read on to see who participated this month and Jason’s thoughts.
Comments closedKevin Jacobs has an article comparing Apache Flink to Spark Streaming:
The other type of data are data streams. Data streams can be visualized by water flowing from a tap to a sink. This process is not ending. The nice property of streams is that you can consume the stream while it is flowing. There is almost no latency involved for consuming a stream.
Apache Spark is fundamentally based on batches of data. By that, for all processing jobs at least some latency is introduced. Apache Flink on the other hand is fundamentally based on streams. Let’s take a look at some evidence for the difference in latency.
Read the whole thing.
Comments closedAllison Tharp is starting to learn Powershell:
PowerShell is developed by Microsoft, specifically for task automation and configuration management. A shell is a user interface that gives the user access to services of an operating system. The shell you are likely most familiar with is the Command Prompt. PowerShell is like the Command Prompt in many ways, but it is much more powerful.
PowerShell is based on the .Net framework and is considered a scripting language. Windows also developed the Windows PowerShell ISE (Integrated Scripting Environment). This is a GUI that lets us make scripts without having to type all of the commands into the command line.
I highly recommend learning Powershell if you’re in the Windows administration space. It’s not quite as useful on the development side (where you can quickly throw together a .NET app) but it’s great for scripting out those admin tasks.
Comments closedReza Rad shows how to hook up streaming data sources to Power BI:
As I mentioned before, download the sample project. and then select the project under this path:
samples/consoleapp/getting-started-for-dotnet-real-time/PBIRealTimeStreaming
And open the PBIRealTimeStreaming solution in Visual Studio. This project creates a data set including a datetime value and a numeric value and will pass that through Power BI API (which will be discussed later in another post) to Power BI service.
Real-time dashboards are great for making it look like you’re doing Very Important Things. They can also be useful in other ways too. Read the whole thing.
Comments closedBen Weissman gives us a table-driven process for SSIS package creation through Biml:
Now, that we’ve seen multiple ways on how to use Biml to create SSIS packages that will generate and populate tables, we’ll try to bridge the gap on how to use Biml for packages that will execute stored procedures and other tasks for us. We’ll use a metadata-model for that again, though it will be very minimalistic. This approach makes it very easy to maintain – we’re actually using it in exactly this way in multiple projects.
To get started, let’s create a new database “MyBiml_SimplisticMetaData” and create two tables, a view and some sample data in there:
One area in Biml where I’ve had trouble is finding the “right” mix of SQL Server tables, T-SQL operations, and Biml (e.g., building up sets of tables for a warehouse load by storing them in a metadata table or by going through RootNode). I tend to lean heavily toward this kind of solution, where most of my metadata lives in T-SQL, but when I do that, it always feels like there’s a “purer” way of doing it. Regardless, one reason I like Biml is that even if I am doing it wrong (for some definition of “doing it wrong”), it’s still a huge time-saver.
Comments closed