Infrastructure at your Service

SQL Server 2016 Archives - Blog dbi services

David Barbarin

SQL Server AlwaysOn – Distributed availability groups, read-only with round-robin capabilities

By | Database Administration & Monitoring | No Comments

  This blog post comes from a very interesting discussion with one of my friends about the read-only capabilities of secondary replicas in the context of distributed availability groups. Initially, distributed availability groups are designed to address D/R scenarios and some migration scenario types as well. I already discussed about of one possible migration scenario here. However, we may also take advantage of using secondary replicas as read-only in Reporting Scenarios (obviously after making an…

 
Read More
David Barbarin

MS Cloud Summit Paris 2017

By | Database management | No Comments

Une nouvelle année commence et 2017 devrait être un bon cru dans le domaine de la base donnée notamment en prédiction des effets d’annonces faites par Microsoft cette fin d’année 2016. En effet, il a été notamment été question de la prochaine vNext de SQL Server qui sera porté sous Linux ainsi que des nouvelles fonctionnalités prometteuses. A l’habitude, l’équipe dbi services tentera couvrir les différents sujets au cours cette année. N’hésitez pas à revenir de temps en temps…

 
Read More
Stéphane Haby

Live from SQL Saturday Slovenia 2016!

By | Technology Survey | No Comments

After a little trip, just 1-hour flying from Zürich to Ljubljana yesterday, the SQL Saturday Slovenia 2016 begins this morning at the Faculty of Computer and Information Science of the University of Ljubljana… I needed to wake up very fast because my session was the first of the day at 9:00 AM. I also very happy to meet and to share my expertise with Slovenian and other SQL Server experts. My session was about the…

 
Read More
David Barbarin

SQL Server AlwaysOn availability groups and statistic issues on secondaries

By | Database Administration & Monitoring | No Comments

I would like to share with you an interesting issue you may face while using SQL Server AlwaysOn availability groups and secondary read-only replicas. For those who use secondary read-only replicas as reporting servers, keep reading this blog post because it is about update statistics behavior on the secondary replicas and as you may know cardinality estimation accuracy is an important part of the queries performance in this case. So a couple of days ago,…

 
Read More
Stéphane Haby

SQL Server 2016 – R Services Tips: How to find installed packages using T-SQL?

By | Development & Performance | No Comments

If you have restricted access to the server and you do not know if your packages are installed on the SQL Server with the R Services, you have the possibility to do it by T-SQL. The R command/function to use is “installed.packages()”. As you can read in the R Documentation for installed.packages(),  this function scans the description of each package. The output is a table with 16 columns with basically these information: Package LibPath Version…

 
Read More
Stéphane Haby

SQL Server 2016: New useful function STRING_SPLIT()

By | Development & Performance | No Comments

Now, in the latest version of SQL Server, you have one of the most expected function as well as for developers as for administrators, splitting a string natively in T-SQL: STRING_SPLIT(<character expression>,<separator>) This function has 2 parameters: The character expression with a data type of nvarchar,varchar,nchar or char The separator with a data type of nvarchar(1), varchar(1), nchar(1) or char(1) The function return a table of one column with all splitting string A first test…

 
Read More
David Barbarin

SQL Server 2016: Distributed availability groups and Cross Cluster migration

By | Database Administration & Monitoring | No Comments

How to migrate an environment that includes availability groups from one Windows Failover Cluster to another one? This scenario is definitely uncommon and requires a good preparation. How to achieve this task depends mainly of your context. Indeed, we may use a plenty of scenarios according to the architecture in-place as well as the customer constraints in terms of maximum downtime allowed for example. Among all possible scenarios, there is a process called “cross-cluster migration…

 
Read More
David Barbarin

SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios

By | Database Administration & Monitoring, Development & Performance | No Comments

Some time ago, I had to deal with a new partitioning scenario that included sliding windows stuff for mainly archiving purpose. Regarding the customer context, I used some management scripts that include this time dropping oldest partition. We didn’t care about data oldest than 2 years. Usually in this case, I use a method that consists in dropping data by switching first the oldest partition to a staging table and then truncate it. Finally we may…

 
Read More
Stéphane Savorgnano

SQL Server 2016: Dynamic Data Masking and database role

By | Database Administration & Monitoring, Technology Survey | 2 Comments

Last week, dbi services organized an event named “SQL Server 2016: what’s new?” in Lausanne, Basel and Zurich. I would take the opportunity to say again a big thank you to everyone which joined us. During my session some questions concerning the new functionality Dynamic Data Masking were asked. In fact data are masked for some roles and not for some others. Let’s try to clarify that. I will use the same script I used…

 
Read More
David Barbarin

SQL Server 2016 : Basic availability groups may fit to your HA needs

By | Database Administration & Monitoring | No Comments

In this blog let’s talk about SQL Server and availability groups in standard Edition. I had some customer questions about existing limitations and this is a good opportunity to review them on this blog post. First of all, let’s say that Microsoft has already documented the restrictions here and from my side, I already written a blog post on the subject at the time of the CTP version of SQL Server 2016. In the meantime,…

 
Read More