'Microsoft is excited to announce a new preview for the next version of SQL Server (SQL Server v.Next). Community Technology Preview (CTP) 1.4 is available on both Windows and Linux. In this preview, we added the ability to schedule jobs using SQL Server Agent on Linux. You can try the preview in your choice of development and test environments now: www.sqlserveronlinux.com.
Key CTP 1.4 enhancements
The primary enhancement to SQL Server v.Next on Linux in this release is the ability to schedule jobs using SQL Server Agent. This functionality helps administrators automate maintenance jobs and other tasks, or run them in response to an event. Some SQL Server Agent functionality is not yet enabled for SQL Server on Linux. To learn more and see sample SQL Server Agent jobs, you can read our detailed blog titled “SQL Server on Linux: Running scheduled jobs with SQL Server Agent” or attend an Engineering Town Hall about “SQL Server Agent and Full Text Search in SQL Server on Linux.”
The mssql-server-linux container image on Docker Hub now includes the sqlcmd and bcp command line utilities to make it easier to create and attach databases and automate other actions when working with containers. For additional detail on CTP 1.4, please visit What’s New in SQL Server v.Next, Release Notes and Linux documentation.
In addition, SQL Server Analysis Services and SQL Server Reporting Services developer tools now support Visual Studio 2017. They are available for installation from the Visual Studio Marketplace providing the option for automatic updates going forward.'
https://blogs.technet.microsoft.com/dataplatforminsider/2017/03/17/sql-server-next-version-ctp-1-4-now-available/?WT.mc_id=AID522292_EML_4958996
'Periodically we are asked how to split an existing filegroup into multiple data files. The quick answer is that SQL Server does not have a built-in way for you to do that automatically, but you can do it yourself. The process is relatively simple and I have provided a script that demonstrates one technique. The script provided is not designed for production and is only provided for illustrative purposes.
There have been many articles in the past that talk about using the ALTER INDEX …. REBUILD option to move objects from one filegroup to another and to “rebalance” that way. This author acknowledges the benefits of that technique, but sometimes the question of “rebalancing” is more driven out of simple “geometry” constraints. For example, if I have a database on a volume that I cannot grow, and I simply want to add new files to the filegroup – but have those files reside on a different volume. Adding the new files is quite simple, but by default, the existing file remains essentially full and there is an imbalance between the old and new files.
The technique proposed here will effectively rebalance and move the data out of the existing file across to the new files in such a way that the original file can be reduced in size and thus free up space on a volume that is filling up. It should be noted that while this technique “moves” data from the original file to the new files in the same filegroup, it does not guarantee that all objects residing in the filegroup are “balanced”. Some objects, depending upon their location in the original datafile, may have some data move, all data move or no data move. Ultimately the total amount of allocated pages will be balanced among the various files in the filegroup, but there could still be some hotspots for certain objects. This article and associated script does not attempt to deal with that issue.'...
https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/03/03/split-a-file-group-into-multiple-data-files/?utm_source=SSC&utm_medium=pubemail
'SQL Server has tools that suggest indexes– and they’ll even auto-create the indexes for you. I’m not a huge fan of these tools because they’ve got some notable flaws: they lead to creating more indexes than you need, and they aren’t super smart about the indexes they recommend.
Two queries that could use some index help
I’m using the free BabbyNames sample database (small version). All the code in this example is also available in a gist for easy access.'...
https://sqlworkbooks.com/2017/03/overindexing-missing-index-dmvs-and-the-database-tuning-advisor/
'It has been a while since I have made a post and figured it was long over due. I figured for my first post in a while, it would be about something I have been working on lately. The automation of installing and configuring of SQL Server.
So the installation of SQL Server is now fairly straightforward. The wizard does a nice job of guiding you along the way. 2016 even includes best practice suggestions for tempdb and instance file initialization. Along the way, Microsoft as given us ways to automate the installation of SQL Server. You can sysprep an instance, but this does not really automate the installation. It just helps create a template of an instance. At the end of the day, you still need to do things manually. You can also use a configuration file to assist here. This is a great step forward, but it does not allow for all of the things you need to do to configure a SQL server.
Powershell does. Desired State Configuration (DSC) is functionality built into Powershell that allows for the installation and configuration of a SQL Server.
First, to get started, you need to have a base understanding of DSC.'...
https://chrislumnah.com/2017/03/07/dsc-install-of-sql-server/
'“How do we know replication is actually delivering transactions? How long were things taking to be delivered to the distributor? Was there a lot of latency between the distributor and the subscriber?” These were the questions our business partners were asking.
It was amazing how much I couldn’t answer. Or more accurately, how much I could quantify. It was time to get personal and just take a peek at what was going on. Traditionally I would just write some queries against the distribution database to see how many transactions were pending but it doesn’t offer much in terms of historical performance. And replication monitor just answers every question with “POTATO.”
So if I wanted monitoring solution, I’d have to make like a dad at a Rush concert and roll my own. It was time to actually start monitoring replication performance, and I was going to use… extended events. Extended events are wonderful diagnostic tools to have in your tool belt for any SQL Server occasion really. If you’ve never used them before and want to learn more about them, I’d highly suggest checking out Erin Stellato’s and Jess Borland’s blogs (or talks) on them. I learned this all from them, and they’re my go-to if I ever have questions.
I like to watch
Extended events are great; they have all the goodness of profiler except you don’t use profiler. Win/win! More to the point, extended events let you quickly and easily view, sort, and aggregate events that occur on your instances. They also have powerful filters (really, a “where” clause) to limit noise. You have way more control over what you monitor, how you store the data, and how you view and use it. This makes them perfect use to track replicated transactions, since we want to measure at both an individual level and the aggregate.
I fired up management studio and went to “New Session” looking for some replication event goodness and I found…'...
http://port1433.com/2017/03/08/monitoring-sql-server-replication-with-extended-events/?utm_source=SSC&utm_medium=pubemail
'SQL Server 2016 introduced dynamic data masking (DDM) as a way to prevent unauthorized users from viewing certain types of sensitive information. The database engine masks the data when it is retrieved from the database, based on masking rules defined on the column schema. The data stored within the database remains unchanged.
When a user queries the database, the database engine determines whether that user account has the permissions necessary to access the data in its unmasked state. If the account does not, the engine applies the masking rules to the data when it is returned as part of the query. In this way, you can mask all or part of sensitive data such as national identification numbers, credit card numbers, birth dates, phone numbers, or other types of information.'...
https://www.simple-talk.com/sql/sql-development/encrypting-sql-server-dynamic-data-masking/
'With the release of SQL Server 2016 SP1, a number of programmability features which were only available in Enterprise Edition are now enabled in Standard, Web, Express and LocalDB editions of SQL Server 2016. These features will allow developers and ISVs with consistent programming experience to build applications which scale across all edition of SQL Server. For more information on what’s new in SQL Server 2016 SP1, please read our SQL Releases blog. In this blog post, I will try to cover few FAQs and information which you need to be aware of related to SQL Server 2016 SP1 if you are running lower editions (Standard, Web, Express and LocalDB).'...
https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-sp1-things-you-should-know/
'Last summer, after SP2 for SQL Server 2014 was released, I wrote about using DBCC CLONEDATABASE for more than simply investigating a query performance issue. A recent comment on the post by a reader got me thinking that I should expand on what I had in mind on how to use the cloned database for testing. Peter wrote:
“I'm mainly a C# dev and whilst I write and deal with T-SQL all the time when it comes to going beyond that SQL Server (pretty much all DBA stuff, stats and the like) I don't really know a lot. Don't even really know how I'd use a clone DB such as this for performance tuning”
Well Peter, here you go. I hope this helps!'...
https://sqlperformance.com/2017/02/sql-performance/clonedatabase-query-store-testing
'I noticed that my backup was taking longer than usual.
I went to Perfmon to look at some counters, more specifically Physical disk:Avg sec/read(orange/yellow line) and Physical disk:Avg sec/write(blue line), yes it was very busy, much busier than normal.
Waits stats was painting the following picture: I was very intrigued with the sleep_bpool_flush so I headed straight over to the waits library.'...
https://blobeater.blog/2017/02/01/my-backup-is-being-blocked/
'Recently on one of our staging servers had a memory consumption issue and
even 32 GB of RAM was looking insufficient and application performance was being impaired. Initially I thought the cause would be poorly designed queries, but after a thorough investigation this turned out not to be the case. I
noticed that whenever the Re-indexing and Update Statistics jobs executed
against the database the memory consumption increased considerably.
The size of the database was 155 GB, the server had 32 GB of RAM as well as 8
CPU’s on it.
While performing the investigation of the system, I decided
to test the performance of the Re-indexing and Update Statistics tasks
separately.
The T-SQL used for performing the Re-indexing of the
database is as shown below...'...
http://www.sql-server-performance.com/2016/trouble-shooting-sql-server-ra-memory-consumption/