MSSQLTREK

neo4j – Basic configuration.

In my previous post we have seen how to Install neo4j 4.4 on a Linux machine. Well, that was pretty simple, so what’s next? In this blog post let’s dig a little further and explore configuration parameters, basic tools, default values etc.

Tools used to manage and interact with neo4j:
“Cypher-shell” and “neo4j browser” are the two tools you need to be extremely comfortable with, out of many that are made available by neo4j. Cypher-shell is the go-to tool for database admins to perform all administrative tasks and neo4j browser is a GUI tool primarily used by Devs and admins to visualize schema and or data.

Now that we have installed neo4j on our server, let’s connect to neo4j from cypher-shell. Well, what user name and password should I use to connect for the very first time to neo4j? If you remember, we haven’t specified any user/password during installation. The answer is user:neo4j and pwd:neo4j. When you connect to your neo4j instance for the very first time using neo4j/neo4j username/pwd combination, it will ask to change the password. See below screenshot to understand what exactly I am referring to….

Quick Tip: Use neo4j-admin utility to setup admin password for the first time if you want to avoid logging in using default pwd and reset manually.
neo4j-admin set-initial-password Mysuper$eCret$Pwd

So what databases are available/installed by default?
‘SYSTEM’ and ‘neo4j’ are two databases which will be made available by the installation process.

‘SYSTEM’ database is the one which stores meta data and users/authentication/authorization etc etc. Think of it like master database in sqlserver.
‘Neo4j’ is the default database and this is where you would create nodes and relationships (basically your graphs) or you can create your own user databases as long as you are using enterprise edition.

How to list all databases in neo4j?
SHOW DATABASES;

//use yield keyword to list only properties(columns) that you are interested in as shown below.

How to switch database/change database context?
:USE DATABASE;
How to create a new database in neo4j?
CREATE DATABASE DBNAME;
How to stop and start a database in neo4j?
STOP/START DATABASE DBNAME;

So what exactly got created on my file system when I created this new database and where? ‘/var/lib/neo4j/data‘ is the default path for all databases in neo4j which can be changed by modifying neo4j.conf file. See below for all the files neo4j created behind the scenes when I created a new database.

But what if I have a dedicated mount for hosting my databases and I want to use that path instead? Well, that’s where neo4j.conf files comes into play where you can modify parameter values as needed.

Now coming to the other tool (neo4j browser) that I mentioned in the beginning, on the host, just open the browser and navigate to localhost:7474 and voila….here you go.

Happy learning, cheers!

How to Install Neo4J in RHEL/CENTOS?

In this blog post, Let’s see how to Install Neo4J on a Linux machine. I am using CentOS 8.0 for this exercise, but the process is exactly the same for RHEL as well. For instructions regarding SUSE, Ubuntu and/or Offline mode install using tar ball, go to neo4j official documentation.

Before proceeding any further, below is my OS build level.

Step1: Install Java 11 (Prerequisite)
sudo yum install https://dist.neo4j.org/neo4j-java11-adapter.noarch.rpm –skip-broken

Step 2: Time to setup our repo file in repos.d folder.

Step 2.1:
// Import the signing key file
sudo rpm –import https://debian.neo4j.com/neotechnology.gpg.key

Step 2.2:
create /etc/yum.repos.d/neo4j.repo file as shown below.
[neo4j]
name=Neo4j Yum Repo
baseurl=http://yum.neo4j.com/stable
enabled=1
gpgcheck=1

FYI, this is my repo file contents.

Now that we have all the prerequisites in place, the final step is to Install Neo4j.

Step 3: Install neo4J Enterprise
sudo yum install neo4j-enterprise

Accept license as shown below when asked.

Or if you don’t want that extra spalsh screen where it asks you to accept license terms, you can perform silent/non-interactive install by issuing below command.

NEO4J_ACCEPT_LICENSE_AGREEMENT=yes yum install neo4j-enterprise

Thats it folks! That’s all it takes to Install neo4J on a linux machine. Before wrapping up let me show you how to check the service status and start neo4J deamon, look at the below screenshot and focus on the blocks, you will get an idea 🙂

have fun exploring Graph databases!

How to change IP Addresses in an Always On Availability group.

In this blog post, let’s see how to change all the IP addresses involved in a typical Always on Availability group configuration. In my setup, I have an AG with two replicas and a listener. See below to get an idea of my current environment on which I am going to change all the underlying IP addresses.

Okay, so there you have it. Two IPs for my two nodes, One IP for my windows cluster and one IP for my availability group listener. Nothing fancy really….I am using host names in my endpoints, no dedicated IPs in my case.

Step 1: Change the IP addresses of both replicas.
For W16SQL2019A node, I am changing IP address from 192.168.1.182 to 192.168.1.151.
For W16SQL2019B node, I am changing IP address from 192.168.1.182 to 192.168.1.152.

Go to the NIC on the respective nodes and make the change, very straight forward.

3_node1IP

Step 2 : Change windows cluster core resource IP (Am changing from 192.168.1.185 to 192.168.1.153)

Right click, Go to properties and edit the IP address as shown below.

Well, That’s a nice little warning, once this is done, check the core cluster resource status and bring it online if it is in offline status at this point. Moving on…

Step 3: Change the IP address of the Always on availability group listener. (Am changing from 192.168.1.186 to 192.168.1.154)

Go to the AG in failover cluster manager, locate the server name in the bottom pane, right click and go to properties and change the static IP address as needed as shown below.

Verify health status and validate in SSMS….

There we go, we have successfully changed the IP address of our Always on Availability group.

Things to remember:

Expect nodes loosing connectivity with each other when changing IP addresses and going offline as a result in Cluster.
The process remains same even if you are moving to a different subnet, Just make sure all the IPs involved are changed reflecting new subnet and cluster resource and all other things are showing online.
If you are using dedicated NICs for AOAG traffic, you should change the IP addresses for your Endpoints as well on all the replicas.

SQL Server Read Scale Availability Groups (AKA Cluster less AGs)

What are Read scale Availability Groups ? (Also referred as Cluster less availability groups many times)

In this blog post let’s see what read scale AGs are and few scenarios when they can be useful for us and how to actually create them. So what exactly is a read scale AG? Well, Microsoft introduced these AGs in SQL 2017 just to serve a single purpose which is to scale our read work loads. These AGs do not offer HA capabilities which we get with a typical AG deployment running on a failover cluster. Since there is no cluster behind the scenes, there is no concept of health checks and hence no automatic failovers. To keep it very simple, consider them as Not Always On AGs 🙂

Well, when can I use Read Scale AGs?

Think of a scenario where all you want is to just isolate your read workload for a given database(s) which doesn’t have any HA requirements and your users and applications are okay connecting directly to your secondary replica for read/reporting work loads, do you really need clustering here? If you are absolutely sure that your AG is not being used for making your database highly available, why to have a cluster and why deal with it?

Note: Read scale AGs do not offer high availability capabilities, This is not for your mission critical database(s) which require HA with automatic failover capabilities. If you have to throw HA in the mix, just stick to traditional AGs.

If not for HA, How about Disaster recovery?

we can certainly get the DR capabilities with Read scale AGs. The reason I say that is, we can have synchronous commit setup which pretty much guarantees the same RPO as a traditional AG would do. Even in a traditional setup when ever I think DR, it’s a manual failover for me….YMMV.

Aaaalright, let’s get into action. I have two standalone SQL Server 2019 Instances joined in the same domain which are not participating in any kind of clustering…you know, just two simple VMs joined to my domain running Windows/SQL, nothing fancy.

First thing first, I enabled AG feature by going to SQL config manager(I did this on both servers), you can see it says “This computer is not participating in a failover cluster”. Duuuh!

I have a database (Not_AO_DB) on which I want to scale my read/reporting work load. well, I went ahead and restored the database manually with norecovery on my second Instance as part of preparing the DB to join in my AG later on.

Nothing new so far right, Now….from SSMS, I choose to use AG creation wizard, gave my AG a name and notice the Cluster type selection in below screenshot, that should be NONE.

Proceed further and select the database(s) that you want to join in AG, I have “Not_AO_DB” database in my case.

Now notice the highlighted Failover mode, you can see there is only one option(Manual) which can’t be changed(Remember, no clustering means no built in Automatic failover). Set your desired Availability mode to either Synch or Asynch.

It is important to remember, All the good old rules for endpoints like TCP port communication and account permissions etc etc still needs to be in place. The actual SQL Server AG mechanics are same here with read scale AGs.

Time for rules check…Next…Next…Finish!

That’s it folks! That’s pretty much what it takes to create a read scale Availability group. Below is how my dashboard looked like after successful creation of my AG.

In the next post, let’s see how to failover a read scale availability group, Listener GOTCHAS and learn how it is different from a traditional AG.

Happy holidays!

Configure/Manage Error logs – SQL Server on Linux.

How to configure and manage SQL Server Error logs in Linux environment:

In this blog post let’s see how to configure and manage SQL Server error logs on an Instance running in Linux environment. I am sure most of you are already recycling (re-init) your error logs (exec sp_cycle_errorlog) on regular basis by scheduling a job and controlling the number of logs as needed on SQL@Windows. Well, If you are not doing so, please go ahead and do yourself a favor…configure your error logs. By default on SQL@windows, it keeps 6 archives (128 in SQL@Linux). Imagine where your SQL got restarted multiple times(let’s say 6 times) in short duration for whatever reason, Oops! your precious logs are already gone. See below screenshots on how to do that for SQL@windows,
Using object explorer in SSMS:


Using T-SQL:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GO

Now let’s move on to SQL@Linux. If you notice the above T-SQL code, SSMS is basically updating windows registry values behind the scenes which isn’t possible in Linux. (Registry is an exclusive windows OS thing…Duh…..!) See below screenshot, you don’t even get configure option in SSMS under SQL Server logs (BTW, Did you notice the tiny Penguin!).

On my lab server with default settings, You can notice that I have 82 error logs as of now.(By Def, max 128 in SQL@Linux and 6 in SQL@Windows)!

Let’s say I want to set it to 20, what I have to do is, use mssql-conf utility and specify the desired number.

sudo /opt/mssql/bin/mssql-conf set errorlog.numerrorlogs 20

The changes will take effect after the service restart and you can validate by reading the contents of mssql.conf file as shown below.

Note: Old error log files should be manually removed or moved as needed.

Hope this helps, Happy Monday!