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

SQL Server Gets PHP Support, Java Support on the Way

Microsoft today has announced new interoperability for SQL Server and Azure SQL Database through the delivery of a driver for PHP.

The driver, in preview, allows developers to use the PHP scripting language (version 5.5) to connect to SQL Server and Azure SQL Database. And, as part of the company's recent openness campaign, the full source code for the driver is also available.

This is just another step in Microsoft's interoperability movement. The company expects to release a JDBC Driver for SQL Server compatible with JDK7 in the coming weeks. Once available, the JDBC driver will allow developers to architect their applications with full connection capabilities to SQL Server against Java 7.

The preview driver is available here: Microsoft Drivers for PHP for SQL Server

The full source code is available on GITHUB here: Microsoft Drivers for PHP for SQL Server project

Suggestions, bug reports, and general feedback is being filtered through the Microsoft Connect site: SQL Server or SQL Database in Windows Azure

 courtesy by :-http://sqlmag.com/database-development/sql-server-gets-php-support-java-support-way.

The MidnightDBAs release Minion Reindex

The MidnightDBA team is announcing the release of a new, free index maintenance solution for SQL Server: Minion Reindex.

Database professionals need a reindex solution that is fully automated out of the box, intuitive to use, simple to customize, deeply configurable, and easy to report from. Existing index maintenance solutions fall short in ease of deployment, configurability, reporting, and more. The new Minion Reindex by MidnightDBA was designed by database administrators, for database administrators, to solve every one of these headaches.

Download Minion Reindex

Minion Reindex is a powerful and elegant tool that we are proud to have in our toolbox.  It makes our jobs easier, makes our clients’ maintenance operations more streamlined, and overall just makes the world a shinier, happier place to live in. 

Download Minion Reindex now for free at www.MidnightSQL.com/Minion.  And, join us at our Minion Reindex webinar on Monday October 27 at 12:00pm CT.  Check www.MidnightSQL.com/Minion for webinar information and links to documentation and feature tutorials.  

Features

We’re excited about a LOT of the features in Minion Reindex. Why is this particular solution worth your notice? Well…

·         Super easy install and removal, of course.

·         Easy MASS installation. One little Powershell script, and you can install Minion Reindex on hundreds of instances just as easily as on one.

·         Extreme granular configuration. Configure extensive settings at the default, database, and/or table levels with ease. And I mean every setting you can think of, from Fill Factor to sort in TempDB.

·         Order index operations by database and by table. This feature is very powerful; there are 2 levels of ordering at the database level, and another 2 levels for table ordering.

·         Flexible include and exclude – Reindex only the databases you want, using specific database names, and/or LIKE expressions. There’s even an advanced option to use regular expressions (regex) for excludes.

·         Live insight – Minion Reindex lets you see what’s happening while the routine is still running. See what Minion Reindex is doing every step of the way, and how much further it has to go.

·         Maximized maintenance window – Spend the whole maintenance window on index maintenance, not on gathering fragmentation stats. (Oh yeah, we let you schedule the frag stats gathering separately, if that’s what you want.)

·         Extensive, useful logging – Use the Minion Reindex log for estimating the end of the current reindexing run, troubleshooting, planning, and reporting.

·         Built in manual runs – Choose to only print reindex statements, and run them individually as needed.

·         Integrated help –Get help on any Minion Reindex object without leaving Management Studio.