Reverse Log shipping Setup

1.) Disable all shipping Logshipping jobs.

 2.) Wait to complete all active transactions to complete.

3.) GO TO primary server and take log backup with no recovery command:-

Backup log DB to disk='d:\DB.trn' with norecovery 

4.) Manually run copy job.

5.) Manually run restore job.

6.) Go to secondary Server and restore log backup that we took in step 3 with NORecovery option:-

 Use [Master]
 Restore log db from disk='d:\DB.trn' with recovery

 7.) Configure logshipping again

New Features in SQL Server 2019

New Features in SQL Server 2019

Support to Persistent Memory (PMEM) Devices

SQL Server 2019 provides support to Persistent Memory (PMEM) devices. SQL Server directly accesses the device, bypassing the storage stack of the operating system for the files placed on the PMEM device.

Columnstore Index Enhancements

SQL Server 2019 also provides enhancements to columnstore index features such as columnstore index maintenance, better metadata memory management, a low-memory load path for columnstore tables, and improved performance for bulk loading to columnstore indexes.

Resumable Online Index Creation

SQL Server 2019 also provides support for resumable online index creation similar to resumable online index rebuilds in SQL Server 2017.  Check out this tip about resumable online index creation.

Up to Five Synchronous Replica Pairs for Availability Groups

We can now configure up to five synchronous replicas in an Availability Groups AG (one primary and up to four secondary replicas) with automatic failover between these replicas.

Enable High Availability Configurations for SQL Server Running in Containers

With SQL Server 2019, we can configure Always on Availability Groups using Kubernetes as an orchestration layer.

Better Scale-out with Automatic Redirection of Connections Based on read/write Intent

In SQL Server 2019, client applications can connect to any of the replicas of the Availability Group. The connection redirects to the primary replica as per the AG configuration and connection string.

SQL Data Discovery and Classification

We have explored this feature in SSMS 17.5. In SQL Server 2019, SQL Data discovery and classification is integrated into the SQL Server engine with new metadata. This enables us to ensure GDPR and other compliance needs for our databases.

Always Encrypted with Secure Enclaves

SQL Server 2019 introduces the secure enclave technology. A secure enclave extends client applications, data trust to the server side. It secures the data from the malware and privileged users.

Certificate Management Functionality in SQL Server Configuration Manager

In SQL Server 2019, certificate management is integrated into the SQL Server Configuration Manager. We can view, validate the certificates being used in SQL Server instance. We can view and validate certificates installed in a SQL Server instance. This also provides detail about certification expiration dates. This feature helps to manage certificates in a better way. We can also deploy certificates for AG instances starting from the primary replica.

UTF-8 Support

SQL Server 2019 provides support for UTF-8 character encoding. We can now create a char or varchar column to store UTF-8 data. This feature improves data compatibility and performance improvements.

Vulnerability Assessment

We can use vulnerability assessment to track compliance of SQL Server instances and Azure SQL Database instances with recognized security best practices. We can implement using the reports shared by this tool. This provides easy to implement security compliance such as GDPR.

Introducing SQL Server 2019: What's New in the Public Preview

Microsoft introduced the public preview of SQL Server 2019 on Monday at the start of the 2018 Ignite conference. While the big news is around SQL Server Big Data clusters, there are a number of major improvements that continue to improve and transform the centerpiece of the Microsoft data platform stack.

You will note most of these improvements are to the relational database engine as development of the BI stack is no longer tied directly to the release of the database engine. Somewhat like SQL Server 2017, this release of SQL Server has a number of minor enhancements.

Steps to Deploying Ola Hallengren IndexOptimize Script

1.       Open browser and go to https://ola.hallengren.com/downloads.html

2.       download IndexOptimize.sql and CommandExecute.sql like below

3.       Create  user database or deploy IndexOptimize.sql and CommandExecute.sql scripts dba maintains database.  

4.       As per your requirement you can execute below scripts .

A. Rebuild or reorganize all indexes with fragmentation on all user databases

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30

B. Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y'

C. Update statistics on all user databases

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = NULL,

@FragmentationHigh = NULL,

@UpdateStatistics = 'ALL'

D. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = NULL,

@FragmentationHigh = NULL,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y'

E. Rebuild or reorganize all indexes with fragmentation on all user databases, performing sort operations in tempdb and using all available CPUs

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@SortInTempdb = 'Y',

@MaxDOP = 0

F. Rebuild or reorganize all indexes with fragmentation on all user databases, using the option to maintain partitioned indexes on the partition level

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@PartitionLevel = 'Y'

G. Rebuild or reorganize all indexes with fragmentation on all user databases, with a time limit so that no commands are executed after 3600 seconds

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@TimeLimit = 3600

H. Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize

@Databases = 'AdventureWorks',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@Indexes = 'AdventureWorks.Production.Product'

I. Rebuild or reorganize all indexes with fragmentation except indexes on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@Indexes = 'ALL_INDEXES, -AdventureWorks.Production.Product'

J. Rebuild or reorganize all indexes with fragmentation on all user databases and log the results to a table

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@LogToTable = 'Y'

Pre-Check list to Migrate SQL Server

• Took the Databases Back Up from old server and moved Backups to New Servers.
• Installation SQL Server on server.
• Restored Databases
• Changed DB owner to SA
• Transferred Logins to new server from old server.
• changed Database Compatibility Level.
• Move jobs to server.
• Move linked servers if any.
• complete DBCC CHECKDB WITH DATA_PURITY (Checks for invalid data values based on column data type).
• Update Statistics (sp_updatestats) (Brings database statistics are up to date to allow the query optimizer to choose the best query plan, take a Full Backup of the Database, make sure you can recover the database with all of the migration efforts)

Checklist to configure SQL Server Database Mirroring.

Checklist to configure SQL Server Database Mirroring.

1. Enable SQL TCP and UDP(for Named instances) in firewall.
2. Start MSSQLSERVER Services with Domain user credentials (instead of Local System Account) on principal database Server and Mirror Database Server.
3. Enable DB mirror Ports in Firewall (Principal, Mirror and Witness).
4. principal DB should be FULL Recover model.
5. Take latest full backup and Logbackup.
6. Restore full and Log backup with no recovery option.
7. The end point should have the status started.

SQL Server Mirroring States

Source :- http://msdn.microsoft.com/en-IN/library/ms189284.aspx

During a database mirroring session, the mirrored database is always in a specific state (the mirroring state). The state of the database reflects the communication status, data flow, and the difference in data between the partners. The database mirroring session adopts the same state as the principal database.

Throughout a database mirroring session, the server instances monitor each other. The partners use the mirroring state to monitor the database. With the exception of the PENDING_FAILOVER state, the principal and mirror database are always in the same state. If a witness is set for the session, each of the partners monitors the witness using its connection state (CONNECTED or DISCONNECTED).

The possible mirroring states of the database are as follows:

Mirroring state	Description
SYNCHRONIZING	The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward. At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
SYNCHRONIZED	When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database. If transaction safety is set to FULLautomatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover. If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
SUSPENDED	The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known asrunning exposed. This is the state after a failover. A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session. SUSPENDED is a persistent state that survives partner shutdowns and startups.
PENDING_FAILOVER	This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role. When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
DISCONNECTED	The partner has lost communication with the other partner.

New Power BI features available for previewabout:blank

Today we are previewing new features for Power BI, our self-service business intelligence solution designed for everyone. Power BI reduces the barriers to deploying a business intelligence environment to share and collaborate on data and analytics from anywhere.

Try what's coming next for Power BI

We are introducing a number of new Power BI features available for preview including dashboards, new visualizations, support for popular software-as-a-service applications, a native iPad app and live “hybrid” connectivity to on-premise SQL Server Analysis Services tabular models.

These preview features are available for customers with a United States address. We’ll incrementally add new country support as we extend the preview globally in the coming months. Existing customers will find a preview option in their current Power BI sites. For those not currently using Power BI, you can sign up for a Power BI preview which includes the new features here.

Now in Preview:

Power BI dashboards

Users can create personalized dashboards to monitor their most important data. A dashboard combines on-premises and cloud-born data in a single pane of glass, providing a consolidated view across the organization regardless of where the data lives. 

Users can easily explore all their data using intuitive, natural language capabilities and receive answers in the form of charts and graphs. They can also explore data through detailed reports that target specific aspects of their business. Visuals from these reports can also be pinned to their dashboards for continuous monitoring. As part of this experience new visualizations have been added including combo charts, filled maps, gauges, tree maps, and funnel charts.  

Out-of-the-box connectors for popular SaaS applications

Power BI now provides “out of the box” connectivity to a number of popular SaaS applications. In addition to the existing seamless connection with Microsoft Dynamics CRM Online, with today’s release customers can also connect to their data in Salesforce, Zendesk, Marketo, SendGrid, and GitHub with many more to come in the months ahead. With an existing subscription to one of these services, customers can login from Power BI. In addition to establishing a data connection, Power BI provides pre-built dashboards and reports for each of these applications.  

Excel BI and the Power BI Designer

Excel 2013, which comes standalone and with Office 365 ProPlus, is Microsoft’s premier business analyst tool – it includes rich business intelligence features (Power Query, Power Pivot, Power View, Power Map) fully integrated with the powerful ad hoc analysis capabilities and familiar features of Excel – like Pivot Tables and Excel Charting. With Excel 2013, analysts can publish Excel Workbooks to Power BI, and share data, analysis and reports with Power BI users.

For Power BI customers who don’t have Excel 2013, the new Power BI Designer provides a solution expressly designed for Power BI report creation. It can be used to import and model data, then author and publish Power BI reports to the Power BI service. 

Connect live to on-premises Analysis Services models

With the new Power BI connector for SQL Server Analysis Services, customers can realize the benefits of a cloud-based BI solution without having to move their data to the cloud. Customers can now create a secure connection to an “on-premises” SQL Server Analysis Services server from Power BI in the cloud. When users view and explore dashboards and reports, Power BI will query the on-premise model using the user’s credentials.

With this hybrid solution, organizations can continue to retain, manage and secure their data on-premises, while securely enabling users to benefit from that data via Power BI.

Stay connected from any device

We are working on a wave of native mobile apps for Power BI. These apps will allow users to access their Power BI dashboards and reports through immersive mobile experiences for iPad, iPhone, and Windows tablets. The first of these - the iPad app is available today and can be downloaded from the Apple App Store, with other platforms following in the coming months. All Power BI mobile apps enable users to share insights and collaborate with colleagues so that they can take immediate action, from anywhere, anytime.

We welcome you to preview the new features for Power BI and look forward to your feedback.

source :-  http://blogs.msdn.com/b/powerbi/archive/2014/12/18/new-power-bi-features-available-for-preview.aspx  

Six Benefits to Planning for SQL Server 2005 and Windows Server 2003 End of Support Now

As the end of 2014 nears, now is the perfect time to review IT infrastructure plans for the coming year.  If you haven’t made supportability a key initiative for 2015, there are some important dates that you should know about:

• Windows Server 2003 extended support ends on July 14, 2015. 
• Extended support for SQL Server 2005 will end just a few months later on April 12, 2016.

After the end of extended support security updates will no longer be available for these products.  Staying ahead of these support dates will help achieve regulatory compliance and mitigate potential future security risks. That means SQL Server 2005 users, especially those running databases on Windows Server 2003, should make upgrading the data platform an IT priority. 

Security isn’t the only reason to think about upgrading. Here are six benefits to upgrading and migrating your SQL Server 2005 databases before the end of extended support:

1. Maintain compliance – It will become harder to prove compliance with the latest regulations such as the upcoming PCI DSS 3.0. Protect your data and stay on top of regulatory compliance and internal security audits by running an upgraded version of SQL Server.
2. Achieve breakthrough performance – Per industry benchmarks, SQL Server 2014 delivers 13x performance gains relative to SQL Server 2005 and 5.5x performance gains over SQL Server 2008.  Customers using SQL Server 2014 can further accelerate mission critical applications with up to 30x transaction performance gains with our new in-memory OLTP engine and accelerate queries up to 100x with our in-memory columnstore. 
3. Virtualize and consolidate with Windows Server – Scale up on-premises or scale-out via private cloud withWindows Server 2012 R2. Reduce costs by consolidating more database workloads on fewer servers, and increase agility using the same virtualization platform on-premises and in the cloud.
4. Reduce TCO and increase availability with Microsoft Azure– Azure Virtual Machines can help you reduce the total cost of ownership of deployment, management, and maintenance of your enterprise database applications. And, it’s easier than ever to upgrade your applications and achieve high availability in the cloud using pre-configured templates in Azure.
5. Use our easy on-ramp to cloud for web applications – The new preview of Microsoft Azure SQL Database announced last week has enhanced compatibility with SQL Server that makes it easier than ever to migrate from SQL Server 2005 to Microsoft Azure SQL Database. Microsoft’s enterprise-strength cloud brings global scale and near zero maintenance to database-as-a-service, and enables you to scale out your application on demand.
6. Get more from your data platform investments - Upgrading and migrating your databases doesn’t have to be painful or expensive. A Forrester Total Economic Impact^TM of Microsoft SQL Server study found a payback period of just 9.5 months for moving to SQL Server 2012 or 2014.

Here are some additional resources to help with your upgrade or migration:

• Understand the upgrade process and plan your migration using the SQL Server 2014 Upgrade Guide.
• Flag potential compatibility issues for your move from SQL Server 2005 to SQL Server 2014 using the SQL Server 2014 Upgrade Advisor. 
• Migrate SQL Server 2005 databases to Azure SQL Database using the Import and Export Service in Azure SQL Database and save on administration and operating costs.
• Read more about the Windows Server 2003 End of Support.

Courtesy By:-  http://blogs.technet.com/b/dataplatforminsider/archive/2014/12/16/six-benefits-to-planning-for-sql-server-2005-and-windows-server-2003-end-of-support-now.aspx?WT.mc_id=Social_SQL_General_DI&WT.mc_id=Social_FB_OutgoingPromotion_20141218_123564271_Microsoft%20SQL%20Server%20-%20sqlserver&linkId=11217484

SQL Server security model

To be able to access data from a database, a user must pass through two stages of authentication, one at the SQL Server level and the other at the database level. These two stages are implemented using Logins names and User accounts respectively. A valid login is required to connect to SQL Server and a valid user account is required to access a database.

Login: A valid login name is required to connect to an SQL Server instance. A login could be:

• A Windows NT/2000 login that has been granted access to SQL Server

• An SQL Server login, that is maintained within SQL Server

These login names are maintained within the master database. So, it is essential to backup the master database after adding new logins to SQL Server.

User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.

During a new connection request, SQL Server verifies the login name supplied, to make sure, that login is authorized to access SQL Server. This verification is called Authentication. SQL Server supports two authentication modes:

• Windows authentication mode: With Windows authentication, you do not have to specify a login name and password, to connect to SQL Server. Instead, your access to SQL Server is controlled by your Windows NT/2000 account (or the group to which your account belongs to), that you used to login to the Windows operating system on the client computer/workstation. A DBA must first specify to SQL Server, all the Microsoft Windows NT/2000 accounts or groups that can connect to SQL Server

• Mixed mode: Mixed mode allows users to connect using Windows authentication or SQL Server authentication. Your DBA must first create valid SQL Server login accounts and passwords. These are not related to your Microsoft Windows NT/2000 accounts. With this authentication mode, you must supply the SQL Server login and password when you connect to SQL Server. If you do not specify SQL Server login name and password, or request Windows Authentication, you will be authenticated using Windows Authentication.

Point to note is that, whatever mode you configure your SQL Server to use, you can always login using Windows authentication.

Windows authentication is the recommended security mode, as it is more secure and you don't have to send login names and passwords over the network. You should avoid mixed mode, unless you have a non-Windows NT/2000 environment or when your SQL Server is installed on Windows 95/98 or for backward compatibility with your existing applications.

SQL Server's authentication mode can be changed using Enterprise Manager (Right click on the server name and click on Properties. Go to the Security tab).

Authentication mode can also be changed using SQL DMO object model.

Here is a list of helpful stored procedures for managing logins and users:

sp_addlogin	Creates a new login that allows users to connect to SQL Server using SQL Server authentication
sp_grantlogin	Allows a Windows NT/2000 user account or group to connect to SQL Server using Windows authentication
sp_droplogin	Drops an SQL Server login
sp_revokelogin	Drops a Windows NT/2000 login/group from SQL Server
sp_denylogin	Prevents a Windows NT/2000 login/group from connecting to SQL Server
sp_password	Adds or changes the password for an SQL Server login
sp_helplogins	Provides information about logins and their associated users in each database
sp_defaultdb	Changes the default database for a login
sp_grantdbaccess	Adds an associated user account in the current database for an SQL Server login or Windows NT/2000 login
sp_revokedbaccess	Drops a user account from the current database
sp_helpuser	Reports information about the Microsoft users and roles in the current database

Now let's talk about controlling access to objects within the database and managing permissions. Apart from managing permissions at the individual database user level, SQL Server 7.0/2000 implements permissions using roles. A role is nothing but a group to which individual logins/users can be added, so that the permissions can be applied to the group, instead of applying the permissions to all the individual logins/users. There are three types of roles in SQL Server 7.0/2000:

• Fixed server roles
• Fixed database roles
• Application roles

Fixed server roles: These are server-wide roles. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server 2000:

Fixed server role	Description
sysadmin	Can perform any activity in SQL Server
serveradmin	Can set server-wide configuration options, shut down the server
setupadmin	Can manage linked servers and startup procedures
securityadmin	Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords
processadmin	Can manage processes running in SQL Server
dbcreator	Can create, alter, and drop databases
diskadmin	Can manage disk files
bulkadmin	Can execute BULK INSERT statements

Fixed database roles: Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server 2000:

Fixed database role	Description
db_owner	Has all permissions in the database
db_accessadmin	Can add or remove user IDs
db_securityadmin	Can manage all permissions, object ownerships, roles and role memberships
db_ddladmin	Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements
db_backupoperator	Can issue DBCC, CHECKPOINT, and BACKUP statements
db_datareader	Can select all data from any user table in the database
db_datawriter	Can modify any data in any user table in the database
db_denydatareader	Cannot select any data from any user table in the database
db_denydatawriter	Cannot modify any data in any user table in the database

Create Login

USE [master]

GO

CREATE LOGIN [test5] WITH PASSWORD=N'Passw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

EXEC master..sp_addsrvrolemember @loginame = N'test5', @rolename = N'sysadmin'

GO

Creating User:-

CREATE USER [test5 ] FOR LOGIN [Test5]

    EXEC sp_addrolemember N'db_owner', N'test5'

END;

GO