'In this article, we'll explore a less used feature of SQL Server: filegroup restore (know also as piecemeal restore or partial restore), feature that has an interesting consequence because database can become inconsistent without any warning and standard checks will not reveal any issue.'...
http://www.sqlservercentral.com/articles/SQL+Server/158068/?utm_source=SSC&utm_medium=pubemail
'In last three years, I presented two times on the topic Transaction Log File Architecture. During the sessions, when I discussed the undocumented command DBCC LOGINFO, one of the common questions which I got – ‘How the Initial VLF sequence number gets decided of the default transaction log file’? In this tip, I will show you how the first VLF sequence numbers get chosen.'...
'Sometimes there is a need to drop a SQL Server table from a database that is published for transactional replication. In this tip we will walk through the steps to do this and an easier way with SQL Server 2014 SP2 and later.'...
https://www.mssqltips.com/sqlservertip/4491/drop-table-from-a-published-sql-server-database/
'Exporting data from SQL Server to Excel seems like a reasonably simple request. I just need to write out a few reports for users on a regular basis, nothing too fancy, the same basic report with a few different parameters. What native SQL Server options are available to do so? Do I need to learn another tool or can I use some T-SQL commands? Does SQL Server 2005 offer any new options to enhance this process?'...
https://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/
'In the Channel 9 studios this week is the amazing Sasha Nosov, Principal PM on the SQL team. Sasha is on the Data Exposed show today to talk about how to do Disaster Recovery in Azure SQL Database using auto-failover groups.
Sasha was in the studio a while ago to talk about Azure SQL DB geo-replication, a feature that is quite popular. This session builds on his first session by discussing and showing additional disaster recovery capabilities in Azure SQL DB.'...
'Today during the Microsoft Data Insights Summit, James Phillips, corporate vice president of Microsoft’s Business Applications, Platform and Intelligence (BAPI) organization, announced the general availability of Power BI Premium.
Power BI Premium was first announced on May 3 as the next step in our commitment to empower people and organizations with access to critical intelligence. The offering enables the distribution of reports broadly across an enterprise and externally, without requiring recipients to be individually licensed. And since Power BI Premium consists of capacity in the Power BI service exclusively dedicated to an organization, the offering provides the flexibility to customize performance based on the needs of a team, department, or the organization itself.
Power BI Premium is available in a range of capacity sizes, each with different numbers of virtual cores and memory sizes that can scale as requirements change – read the Power BI Premium whitepaper for more information and explore the calculator to help with Power BI Premium deployment planning. Visit documentation for details on purchasing.'...
'Have you ever wanted to be able to see the actual transactions that are contained in the transaction log file? Well there is a way to read the transaction log file using an undocumented function. In this article, I will show you how to browse the transaction log using this undocumented function.
To read the transaction log you I will be exploring using the undocumented function named “fn_dblog”. This function requires two parameters to be passed. The first parameter is the starting LSN (Log sequence number) and the second parameter is the ending LSN. If you wanted to read all the data in the transaction log then you will need to pass null values for both of these parameters like so:
SELECT * FROM sys.fn_dblog(NULL,NULL)'...
http://www.databasejournal.com/features/mssql/how-to-read-the-transaction-log-file.html
'Problem:
One important task as a DBA is to manage security in our SQL Server database server environments. Part of this task is to administer the permissions for each user in each database. Sometimes there are orphaned users (no corresponding login exists for the user) in a database and we may want to drop these orphaned users to keep the SQL Server database security clean. Unfortunately, there are several items that can cause a drop user script to fail and in this tip I will share a simple and complete script to drop orphaned users successfully.'...
https://www.mssqltips.com/sqlservertip/3439/script-to-drop-all-orphaned-sql-server-database-users/
'Customer planning for migration to SQL Server 2016 asked for clarification on running mixed version of SQL Servers in their Replication Topology. I searched for “mixed version”, but was unable to find our posted topic. My colleague George Mason tracked it down under “upgrade”. I’m posting key paragraph along with quick reference examples.
Upgrade Replicated Databases
https://msdn.microsoft.com/en-us/library/ms143699.aspx
SQL Server 2016 supports upgrading replicated databases from previous versions of SQL Server; it is not required to stop activity at other nodes while a node is being upgraded. Ensure that you adhere to the rules regarding which versions are supported in a topology:
A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).
A Publisher can be any version as long as it less than or equal to the Distributor version.
Subscriber version depends on the type of publication:
A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server 2012 Publisher can have SQL Server 2014 and SQL Server 2016 Subscribers; and a SQL Server 2016 Publisher can have SQL Server 2014 and SQL Server 2012 Subscribers.
Key point Publisher is within 2 builds of the Subscriber and the Distributor is same or higher build then Publisher.
These are supported:
Publisher (2008/R2) -> Distributor (2016) -> Subscriber (2012)
Publisher (2014) -> Distributor (2016) -> Subscriber (2008/R2)
Publisher (2016) -> Distributor (2016) -> Subscriber (2012)
Not supported:
Publisher (2016) -> Distributor (2016) -> Subscriber (2008/R2)
Publisher (2008/R2) -> Distributor (2016) -> Subscriber (2016)
In chart above SQL Server 2008/R2 means either SQL Server 2008 or SQL Server 2008R2. For all versions, ensure you’re running on most recent SQL Server service pack to take advantage of software fixes and enhancements. Check out Microsoft Release Services blog for updates on service packs and cumulative updates.'...
'Plan regression happens when SQL Server starts using the sub-optimal SQL plan to execute some T-SQL query. Usually you will see that some T-SQL query is executing really fast, but then it gets slower without any obvious reason. In this post you will see how can plan regression happen.'...