Just a short one here, a couple of year ago I was given a years support license for all of ApexSQL's tools....
Some never saw the light of day, that was just down to what I needed, but others were heavily used, a few of my favourites included:
ApexSQL Diff / Data Diff - a complementary pair of tools with such a broad scope of use, Diff can compare the structure and objects of a database, against another database and produce synchronisation scripts to bring both to the same. Whilst Data Diff is similar in principle but compares rows in tables, and again allows you to synchronise them as required.
ApexSQL Trigger - Data auditing gone wild .... will create triggers to gather all data changes against 1 or more tables, absolutely fabulous and with a bit of tweaking I can store audits for tables that change structure without really worrying either.
ApexSQL Doc -A nice tool, generates some very detailed documentation on which ever databases you point it at. With a bit of command line work you can even automate it to re-generate the documentation on a regular basis to pick up any changes that have been made
These few tools alone have saved me so much time and pain, even with out the others.
So, Good Job ApexSQL!
Life & Dev
Monday 16 October 2017
Tuesday 5 September 2017
Matched Betting - First week
Well its only half a week really.
Prior to starting I had read the intro guides on Oddsmonkey to get an understanding of backing and laying bets
Started early on a Wednesday morning - 45 ish minutes in total whilst having my morning coffee before leaving for work.
Signed up with Oddsmonkey Premium and started following the new bookmaker offer guides for Coral and William Hill - nice easy start, majority of my time was filling in sign up forms and making first deposits - then I signed up for Betfair exchange - then it was time to qualify for my freebets.
This was done with the Oddsmonkey Odds Matcher tool- found 2 bets to back that after laying them on Betfair would net me about 50p AND more importantly qualify me for £50 worth of freebets
Coral was no bother, but I made a mistake with the William Hill sign up where Id picked the wrong code :-( after making the bet I had no free bets! So I had to contact their customer services, turned out that the code I used needed the qualifying bet to be made on a Boxing Match!!! This had gone very wrong!
So, deep breath, and had to try and find a qualifying match manually! Using the calculator on OM I found a combo that would lose me about 80p but would let me qualify for the £30 bets from WH... crisis averted!
After all the freebets done (via the oddsmatcher tool) Id made about £40 profits from Coral and WH
This week I also signed up for offers from
Ladbrokes due to another mistake on my part I only got about £15 from the £50 free bet
SunBets - I only qualified my bet in this time - so have a £10 free bet ready for week 2
First weeks (Well 5 days running to sunday) returns : £111.88 for probably around 4 hours worth of effort! And thats AFTER paying for oddsmonkey too!
Prior to starting I had read the intro guides on Oddsmonkey to get an understanding of backing and laying bets
Started early on a Wednesday morning - 45 ish minutes in total whilst having my morning coffee before leaving for work.
Signed up with Oddsmonkey Premium and started following the new bookmaker offer guides for Coral and William Hill - nice easy start, majority of my time was filling in sign up forms and making first deposits - then I signed up for Betfair exchange - then it was time to qualify for my freebets.
This was done with the Oddsmonkey Odds Matcher tool- found 2 bets to back that after laying them on Betfair would net me about 50p AND more importantly qualify me for £50 worth of freebets
Coral was no bother, but I made a mistake with the William Hill sign up where Id picked the wrong code :-( after making the bet I had no free bets! So I had to contact their customer services, turned out that the code I used needed the qualifying bet to be made on a Boxing Match!!! This had gone very wrong!
So, deep breath, and had to try and find a qualifying match manually! Using the calculator on OM I found a combo that would lose me about 80p but would let me qualify for the £30 bets from WH... crisis averted!
After all the freebets done (via the oddsmatcher tool) Id made about £40 profits from Coral and WH
This week I also signed up for offers from
Ladbrokes due to another mistake on my part I only got about £15 from the £50 free bet
SunBets - I only qualified my bet in this time - so have a £10 free bet ready for week 2
First weeks (Well 5 days running to sunday) returns : £111.88 for probably around 4 hours worth of effort! And thats AFTER paying for oddsmonkey too!
Friday 29 April 2016
SQL Server 2012 - AlwaysOn - Notifications
As we've grown and developed our database system further since we first (hurriedly) introduced AlwaysOn. I have realised that some of my suggestions in earlier posts are not the best way of identifying changes.
Whilst a previous post mentioned storing when the primary server had changed, this didn't help with us knowing it had changed so we had to look at SQL server Agent notifications
Whilst a previous post mentioned storing when the primary server had changed, this didn't help with us knowing it had changed so we had to look at SQL server Agent notifications
USE [msdb] GO EXEC msdb.dbo.sp_update_alert @name = N'AlwaysOn - Role Change' ,@message_id = 1048 ,@severity = 0 ,@enabled = 1 ,@delay_between_responses = 0 ,@include_event_description_in = 1 ,@database_name = N'' ,@notification_message = N'' ,@event_description_keyword = N'' ,@performance_condition = N'' ,@wmi_namespace = N'' ,@wmi_query = N'' ,@job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_update_notification @alert_name = N'AlwaysOn - Role Change' ,@operator_name = N'Andrew' ,@notification_method = 1 GO
This (assuming you have your notification service set up with an Operator called Andrew and SQL agent running) will email you whenever there is a change of AG role on the server, such as Primary failing to secondary or secondary becoming primary. Realistically your more likely to get the secondary to primary as if its an unexpected failover your old primary might not be there anymore.
Thursday 28 May 2015
SQL Server 2012 AlwaysOn - Job Management
Continuing on from my last post about AlwaysOn User Management..
Managing jobs so that they only ran on the primary or secondary replicas as required was something we desperately needed before we could migrate, this was due to a fairly large number of processing jobs that run on various schedules.
SQL Server manage jobs exactly the same as SQL logins in terms of they are not transferred or maintained through AlwaysOn as they are at instance level not DB level.
To get around this issue we decided to expand the failover detection method in my first post to also do a quick scan and set enabled/disabled on jobs as required.
To do this we added a new table to our management database as follows
job_id,
CASE
WHEN JS.[enabled] = 1
AND JS.onsecondary = 1 THEN 1
ELSE 0
END AS ToEnable
FROM [dbo].[tbljobstates] JS
INNER JOIN msdb.dbo.sysjobs
ON NAME = [job name]
DECLARE @JobName VARCHAR(200)
DECLARE @jobID UNIQUEIDENTIFIER
DECLARE @Enable INT
OPEN enablejobs
FETCH next FROM enablejobs INTO @JobName, @jobID, @Enable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @jobID= (SELECT job_id
FROM msdb.dbo.sysjobs
WHERE NAME = @JobName)
PRINT @JobName
PRINT 'EXEC msdb.dbo.sp_update_job @job_id='
+ Cast(@jobID AS CHAR(36)) + ',@enabled='
+ Cast (@Enable AS CHAR(1))
FETCH next FROM enablejobs INTO @JobName, @jobID, @Enable
END
CLOSE enablejobs
DEALLOCATE enablejobs
Managing jobs so that they only ran on the primary or secondary replicas as required was something we desperately needed before we could migrate, this was due to a fairly large number of processing jobs that run on various schedules.
SQL Server manage jobs exactly the same as SQL logins in terms of they are not transferred or maintained through AlwaysOn as they are at instance level not DB level.
To get around this issue we decided to expand the failover detection method in my first post to also do a quick scan and set enabled/disabled on jobs as required.
To do this we added a new table to our management database as follows
We then expanded the failover stored procedure to cycle through the table and enable / disable each job based on the 3 flags, using a cursor based approach
DECLARE enablejobs CURSOR local forward_only FOR
SELECT [job name], job_id,
CASE
WHEN JS.[enabled] = 1
AND JS.onsecondary = 1 THEN 1
ELSE 0
END AS ToEnable
FROM [dbo].[tbljobstates] JS
INNER JOIN msdb.dbo.sysjobs
ON NAME = [job name]
DECLARE @JobName VARCHAR(200)
DECLARE @jobID UNIQUEIDENTIFIER
DECLARE @Enable INT
OPEN enablejobs
FETCH next FROM enablejobs INTO @JobName, @jobID, @Enable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @jobID= (SELECT job_id
FROM msdb.dbo.sysjobs
WHERE NAME = @JobName)
PRINT @JobName
PRINT 'EXEC msdb.dbo.sp_update_job @job_id='
+ Cast(@jobID AS CHAR(36)) + ',@enabled='
+ Cast (@Enable AS CHAR(1))
FETCH next FROM enablejobs INTO @JobName, @jobID, @Enable
END
CLOSE enablejobs
DEALLOCATE enablejobs
We also decided that all jobs should only be created/edited on a particular replica - one we called HA1 and created a simple SSIS package to transfer jobs from the primary job box to each secondary on a nightly basis. Not the most efficient , but effective.
Labels:
AlwaysOn,
Jobs,
Logins,
SQL,
SQL Server,
Synchronisation
Friday 8 May 2015
SQL Server 2012 AlwaysOn - User Management
Continuing on from my last post about AlwaysOn..
User Management and maintaining was of critical importance in the scenario we found ourselves in, our systems relied mostly on SQL logins.
Always on databases maintain users exactly as expected, however, as each replica is an independent SQL server creating a SQL login on one replica does not create it on the others.
It was decided that our creation process should be altered to create every login on each replica at creation, but we also knew there was a danger that one would be missed or would be created whilst a replica was down for updates etc etc
So once again we had to weigh up the cost/risk of how often it should be checked regularly, this is the basis for yet another job that runs on each replica once an hour, they are offset on each box also so on the hour, 10 past, 20 past etc etc
We have been careful to ensure that SIDs are also copied over so that logins get linked to database users correctly. We also use the password hash in the creation so that it is easily transferable.
Basically add an extra cursor chunk for each replica, the below example assumes 2 replicas only
CREATE PROCEDURE [dbo].[Agsp_getnewusers]
AS
DECLARE @name VARCHAR( 150 );
DECLARE @ssql VARCHAR( 2500 );
DECLARE @sid VARCHAR( 250 );
DECLARE @pass VARCHAR( 250 );
IF( @@SERVERNAME = '<REPLICA1>' )
BEGIN
DECLARE users CURSOR FOR
SELECT [name],
CONVERT(VARCHAR( 200 ), sid, 2),
CONVERT(VARCHAR( 400 ), [password_hash], 2)
FROM [] .master.sys.sql_logins
WHERE NAME + 'CHECK' NOT IN(SELECT [name] + 'CHECK'
FROM master.sys.sql_logins);
OPEN users;
FETCH next FROM users INTO @name, @sid, @pass;
WHILE( @@FETCH_STATUS = 0 )
BEGIN
SET @ssql = 'CREATE LOGIN [' + @name
+ '] WITH PASSWORD=0x' + @pass
+ ' hashed, sid=0x' + @sid
+
', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
EXEC Sp_executesql
@ssql;
FETCH next FROM users INTO @name, @sid, @pass;
END;
CLOSE users;
DEALLOCATE users;
END;
IF( @@SERVERNAME = '<REPLICA2>' )
BEGIN
DECLARE users CURSOR FOR
SELECT [name],
CONVERT(VARCHAR( 200 ), sid, 2),
CONVERT(VARCHAR( 400 ), [password_hash], 2)
FROM [] .master.sys.sql_logins
WHERE NAME + 'CHECK' NOT IN(SELECT [name] + 'CHECK'
FROM master.sys.sql_logins);
OPEN users;
FETCH next FROM users INTO @name, @sid, @pass;
WHILE( @@FETCH_STATUS = 0 )
BEGIN
SET @ssql = 'CREATE LOGIN [' + @name
+ '] WITH PASSWORD=0x' + @pass
+ ' hashed, sid=0x' + @sid
+
', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
PRINT @ssql;
EXEC Sp_executesql
@ssql;
FETCH next FROM users INTO @name, @sid, @pass;
END;
CLOSE users;
DEALLOCATE users;
END;
You may have noticed the +'CHECK' on the name, this was a bit of a fudge as I created a few test users whilst testing this method of Testuser1,Testuser2,Testuser3 and noticed that for some reason Testuser2 would not come over to replica1 where I had created TestUser1, so I'm wondering if this is an odd comparison issue with strings ending in numbers, anyone know??
User Management and maintaining was of critical importance in the scenario we found ourselves in, our systems relied mostly on SQL logins.
Always on databases maintain users exactly as expected, however, as each replica is an independent SQL server creating a SQL login on one replica does not create it on the others.
It was decided that our creation process should be altered to create every login on each replica at creation, but we also knew there was a danger that one would be missed or would be created whilst a replica was down for updates etc etc
So once again we had to weigh up the cost/risk of how often it should be checked regularly, this is the basis for yet another job that runs on each replica once an hour, they are offset on each box also so on the hour, 10 past, 20 past etc etc
We have been careful to ensure that SIDs are also copied over so that logins get linked to database users correctly. We also use the password hash in the creation so that it is easily transferable.
Basically add an extra cursor chunk for each replica, the below example assumes 2 replicas only
CREATE PROCEDURE [dbo].[Agsp_getnewusers]
AS
DECLARE @name VARCHAR( 150 );
DECLARE @ssql VARCHAR( 2500 );
DECLARE @sid VARCHAR( 250 );
DECLARE @pass VARCHAR( 250 );
IF( @@SERVERNAME = '<REPLICA1>' )
BEGIN
DECLARE users CURSOR FOR
SELECT [name],
CONVERT(VARCHAR( 200 ), sid, 2),
CONVERT(VARCHAR( 400 ), [password_hash], 2)
FROM [
WHERE NAME + 'CHECK' NOT IN(SELECT [name] + 'CHECK'
FROM master.sys.sql_logins);
OPEN users;
FETCH next FROM users INTO @name, @sid, @pass;
WHILE( @@FETCH_STATUS = 0 )
BEGIN
SET @ssql = 'CREATE LOGIN [' + @name
+ '] WITH PASSWORD=0x' + @pass
+ ' hashed, sid=0x' + @sid
+
', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
EXEC Sp_executesql
@ssql;
FETCH next FROM users INTO @name, @sid, @pass;
END;
CLOSE users;
DEALLOCATE users;
END;
IF( @@SERVERNAME = '<REPLICA2>' )
BEGIN
DECLARE users CURSOR FOR
SELECT [name],
CONVERT(VARCHAR( 200 ), sid, 2),
CONVERT(VARCHAR( 400 ), [password_hash], 2)
FROM [
WHERE NAME + 'CHECK' NOT IN(SELECT [name] + 'CHECK'
FROM master.sys.sql_logins);
OPEN users;
FETCH next FROM users INTO @name, @sid, @pass;
WHILE( @@FETCH_STATUS = 0 )
BEGIN
SET @ssql = 'CREATE LOGIN [' + @name
+ '] WITH PASSWORD=0x' + @pass
+ ' hashed, sid=0x' + @sid
+
', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
PRINT @ssql;
EXEC Sp_executesql
@ssql;
FETCH next FROM users INTO @name, @sid, @pass;
END;
CLOSE users;
DEALLOCATE users;
END;
You may have noticed the +'CHECK' on the name, this was a bit of a fudge as I created a few test users whilst testing this method of Testuser1,Testuser2,Testuser3 and noticed that for some reason Testuser2 would not come over to replica1 where I had created TestUser1, so I'm wondering if this is an odd comparison issue with strings ending in numbers, anyone know??
Labels:
AlwaysOn,
Jobs,
Logins,
SQL,
SQL Server,
Synchronisation,
Users
Thursday 30 April 2015
SQL Server 2012 AlwaysOn - Tracking Primary Replica
As I've mentioned I'm what could be called an accidental DBA, and I have recently had the chance to work with AlwaysOn.
One of the things I had problems with was job synchronisation, to this end I came up with a rather odd method of ensuring that scheduled jobs were run on the boxes that we needed them running on even after fail-overs. Lots of posts suggested altering every job to determine whether it needed to run within the first step, but with others writing jobs and approximately 100 to be moved across I felt this was impractical.
But, first I needed to know when a fail-over occurred and also enable or disable jobs as necessary when that happened - preferably with the minimum amount of effort on my part!!!
To do this I created a new administration database and added it to the availability group, then I created a table for tracking the primary replica.
One of the things I had problems with was job synchronisation, to this end I came up with a rather odd method of ensuring that scheduled jobs were run on the boxes that we needed them running on even after fail-overs. Lots of posts suggested altering every job to determine whether it needed to run within the first step, but with others writing jobs and approximately 100 to be moved across I felt this was impractical.
But, first I needed to know when a fail-over occurred and also enable or disable jobs as necessary when that happened - preferably with the minimum amount of effort on my part!!!
To do this I created a new administration database and added it to the availability group, then I created a table for tracking the primary replica.
With this I approached the somewhat more awkward thought of how quickly did we need to be able to identify a fail-over for job purposes, after a lot of discussion with members of the company, it was decided that a 5 minute notification period would be ok.
Next to create a SProc that would check, now I imagine plenty of people will pick holes with this , and please do I always want to improve it
CREATE PROCEDURE dbo.Checkagstate
AS
DECLARE @LastPrimary VARCHAR(80)
DECLARE @CurrentPrimary VARCHAR(80)
DECLARE @JobName VARCHAR(200)
DECLARE @jobID UNIQUEIDENTIFIER
DECLARE @ssql NVARCHAR(500)
SELECT TOP 1 @LastPrimary = primaryagserver FROM tblagstate ORDER BY id DESC
SELECT @CurrentPrimary= primary_replica FROM sys.dm_hadr_availability_group_states WHERE group_id = (SELECT group_id FROM sys.availability_groups WHERE NAME = '<Availability Group Name Here>')
IF (((@LastPrimary <> @CurrentPrimary) AND (@CurrentPrimary = @@SERVERNAME)))
BEGIN
INSERT INTO [dbo].[tblagstate] ([primaryagserver],[changenoted])
VALUES (@CurrentPrimary ,Getdate())
END
Then I added a job to both replicas scheduled for 5 minute intervals. now I know 'roughly' when fail overs have occurred
I'll cover how I expanded on this approach to do job and user management in my next posts! Probably in a day or two
User Management
AS
DECLARE @LastPrimary VARCHAR(80)
DECLARE @CurrentPrimary VARCHAR(80)
DECLARE @JobName VARCHAR(200)
DECLARE @jobID UNIQUEIDENTIFIER
DECLARE @ssql NVARCHAR(500)
SELECT TOP 1 @LastPrimary = primaryagserver FROM tblagstate ORDER BY id DESC
SELECT @CurrentPrimary= primary_replica FROM sys.dm_hadr_availability_group_states WHERE group_id = (SELECT group_id FROM sys.availability_groups WHERE NAME = '<Availability Group Name Here>')
IF (((@LastPrimary <> @CurrentPrimary) AND (@CurrentPrimary = @@SERVERNAME)))
BEGIN
INSERT INTO [dbo].[tblagstate] ([primaryagserver],[changenoted])
VALUES (@CurrentPrimary ,Getdate())
END
Then I added a job to both replicas scheduled for 5 minute intervals. now I know 'roughly' when fail overs have occurred
I'll cover how I expanded on this approach to do job and user management in my next posts! Probably in a day or two
User Management
Wednesday 29 April 2015
Well it's got to start somewhere...
First post!...enough of the Slashdot approach.
This is going to be my semi-regular blog of "Life and Dev".
Some background, I'm a software developer / accidental DBA, but that's not all there is, no one is just their job (hopefully anyway). So I'll be putting up anything I think is interesting, from technology related bits to life related bobs.
I hope that something in all this will be interesting or useful to someone.
I'll be hoping to add one or two posts a month to start, hopefully ramping up a little as I get more used to writing.
If you ever want to contact me for advice, you can reach me at blog@andrewsexton.co.uk and I'll endeavour to get back to you as soon as possible.
If you ever want to contact me for advice, you can reach me at blog@andrewsexton.co.uk and I'll endeavour to get back to you as soon as possible.
Subscribe to:
Posts (Atom)