164 How to solve WLW can not connect to blogger

If you get the error while trying to post blog from windows live writer to Google Blogger service and get the login error:

or

Windows Live Writer was not able to log in to the remove server using the username and password.

This is because Google has enhance the Google has enhance the authentication security with oAuth 2.0.

To work around the issue we need to go to Google account setting to allow ”Less secure app”.

24 Install Vagrant on Windows

Download the latest version of Vagrant from https://www.vagrantup.com/

57 Enable PostgreSQL for remote connection in Centos 7 with firewall-cmd

Enable client authentication in pg_hba.conf

host    all             all             0.0.0.0/0               md5

Allow TCP/IP connection in postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on;

Need to restart the service after edit the postgresql.conf

systemctl status postgresql-inst1

Configure firewall-cmd

firewall-cmd --zone=public --add-port=7001/tcp --permanen

firewall-cmd --reload

Test connecting with pgadmin3

17 Install/configure multiple postgreSQL instances on Centos 7 with systemctl

In this post, I am going to create three postgreSQL instance on the same host. I can not seem to find right twiki for how to do it therefore I create this article  in case if other people may want to do the same.

Install Centos 7

8 SQL Server vNext

Despite many people may expect that Microsoft will announce some sort of roadmap for the next release of SQL Server in the PASS SUMMIT held in Seattle last week ( Nov 4th – Nov 7th ).

There is not really much new stuff being show in the conference . However, if you pay attention on the keynote and sessions, there are still some clues for what may be part of new feature in the next release.

For instance, below is one of the slide that high light the new feature for the Azure SQL Database. Since I don’t use Azure SQL DB, it is not good enough for me.

But wait, there is more in the key note and sessions for database new feature that I like to share with you: 

• Non clustered column store index for memory optimized table
• Stretch table to Azure
• Query store

non clustered column store index for memory optimized table

In SQL 2014 memory optimized table, the table structure is defined during the table creation time. We can not change the table structure nor alter index afterward.  During the keynote day 1 demo, the Microsoft has combine column stored index and memory optimized table. 

Stretch table to Azure

Stretch table allow we move some of the data for particular table to azure. In the event of the disaster recovery, SQL Server can sync the data state between on premises and the cloud .

Here is the syntax

Query store

I could not remember how many times I have asked developer did you keep the old query plan so we can compare the existing query plan .

The SQL 2014 already has the extend event for the query store but it is empty. The code probably is not  ready for production but now there is a light .

In the future release, SQL Server would keep all the query plan in the meta data table. The table is special memory optimized table and we can control how often the table would flush to the disk. The table would be durable which means it would not get reset after server bounce like DMV.  we will be able to easily force the plan and there would be no need to use the plan guide anymore.

In Conor Cunningham’s word “ Query store do ~~

For those who miss the SUMMIT, you can watch some of the recording from here http://www.sqlpass.org/summit/2014/PASStv.aspx?watch=7Pum0vfYtSk

14 How to create copy of the Windows Azure Database

To create the copy of the database in Azure, we don’t need to do backup and restore .It is new syntax with AS COPY OF

create database AzureDB3 as copy of AzureDB1

Reference

http://msdn.microsoft.com/en-us/library/dn268335.aspx

14 SQL2014:AlwaysOn High availability group-Readable secondary

With AlwaysOn availability group, we can create the readable secondary to offload the query load on the primary. This post is the walk through how to set up the readable seconday.

10 SQL2014:AlwaysOn High availability group-Add additional replica

Below is the walk through for how to add another replica to the existing high availability group.

prerequisites

Back up the primary database and log , then restore to the new replica with NORECOVERY

 

From SSMs Availability replicas –> Add Replica

Connect to the existing replica

Above steps can be replaced with below script . However, the scripts needs to be executed under SQLCMD mode.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect SGC8\SGC8SQL2014

USE [master]

GO

CREATE LOGIN [Stargate\sqlprodid] FROM WINDOWS

GO

:Connect SGC4,8001

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [STARGATE\sqlprodid]

GO

:Connect SGC5\SGC5SQL2014

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [STARGATE\SQLPRODID]

GO

:Connect SGC8\SGC8SQL2014

USE [master]

GO

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END

GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Stargate\sqlprodid]

GO

:Connect SGC8\SGC8SQL2014

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect SGC4,8001

USE [master]

GO

ALTER AVAILABILITY GROUP [pandaAlwaysOnDemo]
ADD REPLICA ON N'SGC8\SGC8SQL2014' WITH (ENDPOINT_URL = N'TCP://SGC8.stargate.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

GO

:Connect SGC8\SGC8SQL2014

ALTER AVAILABILITY GROUP [pandaAlwaysOnDemo] JOIN;

GO

:Connect SGC8\SGC8SQL2014

-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes

if (serverproperty('IsHadrEnabled') = 1)
    and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
    and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'pandaAlwaysOnDemo'
    select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
    while @conn <> 1 and @count > 0
    begin
        set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
        if @conn = 1
        begin
            -- exit loop when the replica is connected, or if the query cannot find the replica status
            break
        end
        waitfor delay '00:00:10'
        set @count = @count - 1
    end
end
end try
begin catch
    -- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [AdventureWorks2014] SET HADR AVAILABILITY GROUP = [pandaAlwaysOnDemo];

GO

GO

28 SQL2014: Create AlwaysOn High availability group

Below is my note to create Always on High availability group in SQL 2014.

All SQL Server participate alwayson availability group must in the same WSFC.

Enhancement for SQL2014 AlwaysOn

• Support Azure as replica.
• The maximum number of secondary replicas is increased from 4 to 8.
• When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
• A new system function, sys.fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.
• The following DMVs were enhanced and now return FCI information: sys.dm_hadr_cluster, sys.dm_hadr_cluster_members, and sys.dm_hadr_cluster_networks.

Environment

• SQL Server standalone 1: SGC4,8001
• SQL Server standalone 2: SGC5,8001

69 How to fix the “The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node. “ error

During creation the Availability group in SQL 2014, I have encounter the below error when try to add the 2nd replica.  

The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)

The local node is not part of quorum and is therefore unable to process this operation. This may be due to one of the following reasons:

•   The local node is not able to communicate with the WSFC cluster.

•   No quorum set across the WSFC cluster.

This prompt me that maybe the 2nd instance does not know itself its HADR enable. So I qery sys.dm_hadr_cluster_members and sys.dm_hadr_cluster for both nodes.

On node1, it does return correct information. On node2, the result is empty.

SELECT *

FROM sys.dm_hadr_cluster_members;

Go

select * from sys.dm_hadr_cluster

The way to fix it is disable the HADR from SQL Server configuration manager . Bounce SQL Server and SQL agent. Rentable HADR and bounce SQL server and SQL agent.

The issue was resolved after 2nd bounce.