Friday, February 11, 2022

Azure: Cloud Shell


Azure and Powershell - Round 1

 Install-Module -Name Az -Verbose

(did you try Install-Module -Name -Az -Verbose -Force ?  Hello, Azure for Dummies readers.  It is an irresponsible typo.)

PS C:\WINDOWS\system32> Install-Module -Name -Az -Verbose -Force

NuGet provider is required to continue

PowerShellGet requires NuGet provider version '' or newer to interact with NuGet-based repositories. The NuGet

 provider must be available in 'C:\Program Files\PackageManagement\ProviderAssemblies' or

'C:\Users\XXXXXX\AppData\Local\PackageManagement\ProviderAssemblies'. You can also install the NuGet provider by running

 'Install-PackageProvider -Name NuGet -MinimumVersion -Force'. Do you want PowerShellGet to install and

import the NuGet provider now?

[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

VERBOSE: Valid authenticode signature found in the file 'Az.Accounts.psd1' for the module 'Az.Accounts'.

PackageManagement\Install-Package : The following commands are already available on this

system:'Login-AzAccount,Logout-AzAccount,Resolve-Error,Send-Feedback'. This module 'Az.Accounts' may override the

existing commands. If you still want to install this module 'Az.Accounts', use -AllowClobber parameter.

At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\\PSModule.psm1:1809 char:21

+ ...          $null = PackageManagement\Install-Package @PSBoundParameters

+                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (Microsoft.Power....InstallPackage:InstallPackage) [Install-Package],


    + FullyQualifiedErrorId : CommandAlreadyAvailable,Validate-ModuleCommandAlreadyAvailable,Microsoft.PowerShell.Pack



Update-Help: Failed to update Help for the module(s) 'ConfigDefenderPerformance, PSReadline, WindowsUpdateProvider' with UI culture(s) {en-US} : One or more errors occurred. (Response status code does not indicate success: 404 (The specified blob does not exist.).).
English-US help content is available and can be installed using: Update-Help -UICulture en-US.
PS C:\Windows\System32> Update-Help -UICulture en-US
Update-Help: Failed to update Help for the module(s) 'ConfigDefenderPerformance, PSReadline, WindowsUpdateProvider' with UI culture(s) {en-US} : One or more errors occurred. (Response status code does not indicate success: 404 (The specified blob does not exist.).).
English-US help content is available and can be installed using: Update-Help -UICulture en-US.
PS C:\Windows\System32>

PS C:\Windows\System32> Connect-AzAccount
WARNING: Both Az and AzureRM modules were detected on this machine. Az and AzureRM modules cannot be imported in the same session or used in the same script or runbook. If you are running PowerShell in an environment you control you can use the 'Uninstall-AzureRm' cmdlet to remove all AzureRm modules from your machine. If you are running in Azure Automation, take care that none of your runbooks import both Az and AzureRM modules. More information can be found here:

Sunday, February 6, 2022

Wednesday, February 2, 2022

SQL Server Replication - overview

You cannot use SQL Server Express as a publisher for all types of replication including merge. SQL Server Express can only act as a subscriber. 

Basically, you have to create two things:

Secondly, a SUBSCRIPTION (to the aforementioned PUB).

But behind both of those, you need a DISTRIBUTOR.

When you are on a virgin SQL Server instance, and use the Create Publication Wizard, the first thing it will do for you is offer to create the DISTRIBUTOR.  

The other thing behind all this is the agents.




Other important concepts:




Publication Types


Publisher sends a snapshot of the published data to Subscribers as scheduled intervals.


Publisher STREAMS TRANSACTIONS to the Sub after they receive an INITIAL SNAPSHOT of the published data.


Publisher STREAMS TRANSACTIONS to all the PEERS.(in the topology)
All peers (called nodes) can read and write changes and the changes are propagated to all the others.


Publisher and Subscribers can update the published data independently after the Subscribers receive an INITIAL SNAPSHOT of the published data.
Like GIT.
Changes are merged periodically.
NOTE: Compact Edition can only subscribe to these kinds of publications.

You can only select Tables for replication that have a PRIMARY KEY COLUMN.
All tables in transactional publications must have PRIMARY KEY COLUMNS.

New Publication Wizard:

Snapshot Agent
Specify when to run the SNAPSHOT AGENT
Subscriptions are initialized with a SNAPSHOT of a publication schema and data. The SNAPSHOT AGENT creates the SNAPSHOT.

x? Create a SNAPSHOT immediately and keep it available to initialized subscriptions.
? Schedule it to run at certain times.  Why would you want this?

Agent Security
For two agents: SNAPSHOT Agent and LOG READER Agent.
-selected Windows account, need to provide password, can be on another domain
-or Run under the SQL SERVER AGENT Service Account (not a rec'd best practice)

Connect to the Publisher:
- by impersonating the process account
????? what is the PROCESS account????????
- or using a SQL SERVER login (password must be provided)

Assuming you chose to CREATE SNAPSHOT IMMEDIATELY, the SNAPSHOT agent will run and create a bunch of files in your select backup directory (ie, G:\ReplData)

Folders and paths:


Then I guess the SNAPSHOT agent sleeps.  But if you manually activate it (like by using the MONITOR: on where it says "Completed" for status, right-click and select START) it will do another snapshot starting with the new YYYMMDDsssss folder.

Meanwhile, the Log Reader agent will be started and still running.

In the Replication Monitor, assuming you selected the machine, the tabs are:
Subscription WatchList

On Agents, in addition to Snapshot and LogReader, there is "Maintenance Jobs"
This is a reminder that SQL AGENT JOBS were probably created after the Publication was craeated.

The ones that ran:
Agent history clean up: distribution
Distribution clean up: distribution
Replication: agents checkup
Monitor and sync replication agent jobs. every 5 or 2 minutes....Distribution cleanup is every 10 minutes.

... and back in the ReplData folder, some of the created snapshots folders (oldest one) disappeared!
Maybe one of those jobs did that.
There can be at least 4 or 5 snaps so far without deleting.  I think the Distribution Cleanup might be the one deleting folders.  After it ran there was only two snap folders left, three older ones deleted.

One of the left-click menu options on the Publication is to RE INITIALIZE ALL PUBLICATIONS.  
"This action marks each subscription to be reinitialized from a snapshot the next time its Distribution Agent runs.  What snapshot should be used to reinitialize the subscription(s)?
- Use the current snapshot
- Use a new snapshot --------? Generate the new snapshot now.

[Mark for Reinitialization]
My guess is, if you don't have any SUBSCRIPTIONS, this doesn't really do anything.
EXCEPT if you select "Use a new snapshot and generate now", then you see the SNAPSHOT AGENT run immediately and you have a new snap folder.

Now let's see what happens when you go create a SUBSCRIPTION!
Two types of SUBS: 

In the Wizard it is called Distribution Agent Location.  Choose WHERE to run the DISTRIBUTION AGENT:
Run all at the Distributor (current machine):  PUSH
"easier to admin..."

Run each agent at its own particular Subscriber: PULL
"relieves some processing overhead on the Distributor"

Subscribers: You have to pick the SUBSCRIPTION DATABASE, which is where the data is going to go....  I wonder what happens if you select the same database as the source?  For now using an empty REPL_TESTER database.

Either "Run continuously" or "Run on demand only". (also "<<<define schedule>>>)

"....needs to be done...unless it has already been "specially prepared" for the subscription"
INITIALIZE WHEN: Immediately or "At First Synchronization"

If you go immediately to the Replication Monitor, the "My Publishers"- machine name, underneath shows a warning yellow and ! symbol. But then goes away.

Might be .... If on SUB WATCH LIST tab I pick SHOW TRANSACTIONAL SUBS, it now shows something there (odd b/c I thought we just were doing snapshots) ....
... and after a minute or so, it goes green.
I got it to perform this when I did an INITALIZE immediately (now that we have a SUB).