Cloud Service Models:
IAAS: Infrastructure as a Service  ( Microsoft Azure ): can select OS, middleware, run time

PAAS: Platform as a Service  ( Microsoft SQL Azure ): can select data and applicationa
SAAS: Software as a Service: like outlook and servicenow, just use it through http or https
DBaaS: Database As A Service: provide specific database ( db vendor or db type )

Azure SQL Database is a managed database platform as a service (Paas) 

Waterfall vs. Agile ( Iteractive, continuous, heavily used in DevOPS ): 

Processes: Agile, Scrum, CMMI
Scrum and Kanban are two of the most widely used Agile methodologies.

Scrum: 
strictly assigned time and person for each task ( Scrum Master )

Kanban: 
Task time limit and assigned person are not very strict, 
only ensure go to next step only after current step completed.

1):
Scrum process framework requires the use of development cycles called Sprints, 


2):
A sprint is a set period of time during which specific work has to be completed 
and made ready for review.

-- portal.azure.com
http://portal.azure.com
-- create azure sql database through portal

https://manage.windowsazure.com

-- Azure DB Server Naming Convention
mydatabaseserverid.database.windows.net:1433
eg: gardening.database.windows.net,1433
-- select @@VERSION;
-- check server property: SELECT SERVERPROPERTY(N'Collation');

-- Azure Storage, Azure Blob storage, Microsoft BLOB storage account
http://myaccount.blob.core.windows.net/mycontainer/myblobname
eg 1: BACKUP DATABASE AdventureWorks2016 TO URL = 'https://.blob.core.windows.net//AdventureWorks2016.bak;

eg 2: BACKUP DATABASE AdventureWorks2016
TO URL = 'https://.blob.core.windows.net//AdventureWorks2016.bak'
WITH FILE_SNAPSHOT;

-- report server URL
http://myvmname.cloudapp.net/reportserver
http://myvmname.cloudapp.net/reports

-- ADF: Azure Data Factory
datafactory.azure.com

-- DevOps
https://dev.azure.com
https://dev.azure.com/myid/project01

-- Setup Firewall Rules
1): IP firewall rules
2): Virtual network firewall rules
-- to change firewall rules: sp_set_firewall_rule

-- Azure web cloud
winecloudweb.azurewebsites.net

azure cloud shell: powershell in cloud

azure portal -- Cloud Shell -- Bash or Powershell

1): powershell
PS Azure:\> get-command -module PSCloudShellUtility

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Function        Disable-AzVMPSRemoting                             0.9.3      PSCloudShellUtility
Function        Dismount-CloudDrive                                0.9.3      PSCloudShellUtility
Function        Enable-AzVMPSRemoting                              0.9.3      PSCloudShellUtility
Function        Enter-AzVM                                         0.9.3      PSCloudShellUtility
Function        Export-File                                        0.9.3      PSCloudShellUtility
Function        Get-AzCommand                                      0.9.3      PSCloudShellUtility
Function        Get-CloudDrive                                     0.9.3      PSCloudShellUtility
Function        Get-CloudShellTip                                  0.9.3      PSCloudShellUtility
Function        Get-Help                                           0.9.3      PSCloudShellUtility
Function        Invoke-AzVMCommand                                 0.9.3      PSCloudShellUtility

2): bash
Type "az" to use Azure CLI
Type "help" to learn about Cloud Shell

-- ------------------------
The following az command works in both bash and powershell

az --version

get-module -ListAvailable

get-command -module AZ*

Get-AzVM 

get-help get-azvm
help get-azvm

Get-PSDrive

cd Azure

cd $home

azure cli

az -version
az webapp list
az webapp list --output table
az login
az account set --subscription 
az group list --output table
az group create --name myResourceGroup --location eastus

Azure Database Service Tiers
basic
standard
Premium

Azure SQL Database: Single database
Azure SQL Managed Instance

-- DTUs
http://dtucalculator.azurewebsites.net

DTU vs. vCore 


DTUs: 
a blended measure of CPU, memory, and data I/O and transaction log I/ 
( Database Transaction Unit,  similar to CPUs )

-- roughly 10 Reads/sec = 1 DTU

Reads/sec
Writes/sec 	DTUs 	Service Tier
2000 	        250 	Premium P2
4000-6000 	500 	Premium P4
8000-12000 	1000 	Premium P6
14000-22000 	1750 	Premium P11
24000-30000 	4000 	Premium P15

-- ---------------------------------------

roughly 1MB flushed/sec = 50 DTUs

Log 
Megabytes 
flushed
/sec 	DTUs 	Service Tier
5 	250 	Premium P2
10 	500 	Premium P4
15-25 	1000 	Premium P6
30-40 	1750 	Premium P11
45-75 	4000 	Premium P15

azure sql server database:
1. don't have server option, replication, alwayson option
2. cannot create linked server on azure sql database
   but can create an odbc data source on on-premise DB server, then create linked server on on-premise db. 
3. don't have agent option ( but can use Azure automation: run powershell to the Azure sql server database )

sql server managed instance support the following: 
1): Linked servers  
2): database mail 
3): service brokers, queue and messages
4): SQL agent
5): Native backup and restore
6): Cross-database queries and transactions
7): Transactional Replication ( Azure SQL Database can only be a subscriber )
8): CLR modules

Azure SQL Managed Instance 

Azure SQL Managed Instance 

Azure SQL Database vs. SQL Server Managed Instancei 

Migrate certificate of TDE protected database to Azure SQL Database Managed Instance 

Quickstart: Restore a database to a Managed Instance 
Azure Blob storage

When create an Azure sql databases
1): Need to fill out or select a): subscription information b): Resource group information 

2): there is no sa account, but it will create a server level principal account, similar to sa account

3): must set firewall rules at server level or database level, otherwise outsiders will not be able to access
-- at server level
use master
go
select * from sys.firewall_rules;
go

exec sp_set_firewall_rule @name=N'MyFirewallRule1',
   @Start_ip_address = '192.168.100.10',
   @end_ip_address = '192.168.100.10'

exec sp_delete_firewall_rule @name=N'MyFirewallRule1'

-- at database level
select * from sys.database_firewall_rules;
go

exec sp_set_database_firewall_rule @name=N'MyDBFirewallRule1'
   @Start_ip_address = '192.168.100.20',
   @end_ip_address = '192.168.100.20'
go

/* master */

CREATE LOGIN WineCloudDbLogin WITH PASSWORD='p@ssw0rd'
GO
--
CREATE USER WineCloudDbUser FROM LOGIN WineCloudDbLogin
GO
EXEC sp_addrolemember 'loginmanager', 'WineCloudDbUser'
EXEC sp_addrolemember 'dbmanager', 'WineCloudDbUser'
GO

--
loginmanager: permission to create login user at server level
dbmanager: permission to create database at server level
--
CREATE LOGIN WineCloudDbReadonlyLogin WITH PASSWORD='p@ssw0rd'
GO

/* WineCloudDb */

CREATE USER WineCloudDbReadonlyUser FROM LOGIN WineCloudDbReadonlyLogin
GO
EXEC sp_addrolemember db_datareader, WineCloudDbReadonlyUser
GO

GRANT SELECT ON SCHEMA::Test to ApplicationUser

SELECT r.name role_principal_name, m.name AS member_principal_name
FROM sys.database_role_members rm
JOIN sys.database_principals r

CREATE USER WineCloudDbReadonlyUser FROM LOGIN WineCloudDbReadonlyLogin
GO
EXEC sp_addrolemember db_datareader, WineCloudDbReadonlyUser
GO

GRANT SELECT ON SCHEMA::Test to ApplicationUser

SELECT r.name role_principal_name, m.name AS member_principal_name
FROM sys.database_role_members rm
JOIN sys.database_principals r

CREATE USER WineCloudDbReadonlyUser FROM LOGIN WineCloudDbReadonlyLogin
GO
EXEC sp_addrolemember db_datareader, WineCloudDbReadonlyUser
GO

GRANT SELECT ON SCHEMA::Test to ApplicationUser

SELECT r.name role_principal_name, m.name AS member_principal_name
FROM sys.database_role_members rm
JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
WHERE r.name IN ('loginmanager', 'dbmanager');

-- Contained Users:
USE MYDB
create user MyUser1 with password = 'Password123'
ALTER AUTHORIZATION ON SCHEMA::[db_owner] to [MyUser1];
ALTER ROLE [db_owner] ADD MEMBER [MyUser1];

option 3): create active directory login

create user [myusername@xxx.onmicrosoft.com] from external provider;

-- tuning:
How SQL Server DMVs Can Be a Savior in SQL Azure, Too 

monitoring overview 

dmvs 

-- tuning DMVs

sys.dm_exec_cached_plans
sys.dm_exec_query_plan
sys.dm_exec_query_stats

sys.dm_os_performance_counters

SELECT * FROM sys.dm_os_schedulers;

SELECT * FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE';

-- Will return the Server Name we are running on
SELECT @@Servername

-- DMV: dm_exec_connections gets the connection information
SELECT getdate() as "RunDateTime", c.* 
FROM sys.dm_exec_connections c
Go
 
-- DMV: dm_exec_sessions gives the current sessions
SELECT getdate() as "RunDateTime", s.*
FROM sys.dm_exec_sessions s
Go

--DMV: dm_exec_requests gives the active sessions/spids currently
SELECT getdate() as "RunDateTime", st.text, r.* 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st
GO

-- Get top 10 queries by total_logical_reads
SELECT TOP 10 getdate() AS "Today",
    st.text, qp.query_plan, 
    (qs.total_logical_reads/qs.execution_count) AS avg_logical_reads,
    (qs.total_logical_writes/qs.execution_count) AS avg_logical_writes,
    (qs.total_physical_reads/qs.execution_count) AS avg_phys_reads,
    qs.*
	FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
	ORDER BY qs.total_logical_reads DESC
GO

-- Get top 10 queries by total_logical_writes
SELECT TOP 10 getdate() AS "Today",
     st.text, qp.query_plan, 
    (qs.total_logical_reads/qs.execution_count) AS avg_logical_reads,
    (qs.total_logical_writes/qs.execution_count) AS avg_logical_writes,
    (qs.total_physical_reads/qs.execution_count) AS avg_phys_reads,
    qs.*
	FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
	ORDER BY qs.total_logical_writes DESC
GO

-- SQL Statement which are blocking
SELECT wait_type
      , wait_time
      , wait_resource
      , database_id
      , blocking_session_id
	  , r.command
	  , qt.dbid
      ,SUBSTRING(qt.text
    , CASE WHEN (r.statement_start_offset IS NULL OR r.statement_start_offset <= 1) 
            THEN 1 ELSE (r.statement_start_offset/2) END
    , CASE WHEN r.statement_end_offset IS NULL 
            THEN len(qt.text) ELSE ((r.statement_end_offset-r.statement_start_offset)/2) END
        )  AS stmt
       , qp.query_plan
FROM sys.dm_exec_requests r
      CROSS APPLY sys.dm_Exec_sql_text(sql_handle) qt
      CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE wait_type <> '';


SELECT 
r.command, 
query = a.text, 
start_time, 
percent_complete,
eta = dateadd(second,estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
-- WHERE 
-- r.command IN ('BACKUP DATABASE','BACKUP LOG')
GO

-- top 10 currently running query
PRINT '--top 10 Active CPU Consuming Queries by sessions--';
SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms', 
OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName', SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, 
(req.statement_start_offset / 2)+1, 
((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) 
ELSE req.statement_end_offset END 
-req.statement_start_offset)/ 2)+1), CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
     CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY cpu_time DESC;
GO

-- top 10 cpu query
PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--';
SELECT TOP 10 GETDATE() runtime, *
FROM(SELECT query_stats.query_hash, SUM(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', 
SUM(logical_reads) 'Total_Request_Logical_Reads', MIN(start_time) 'Earliest_Request_start_Time', 
COUNT(*) 'Number_Of_Requests', SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), 
CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS "Statement_Text"
FROM(SELECT req.*, SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, 
((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END
-req.statement_start_offset)/ 2)+1) AS statement_text
          FROM sys.dm_exec_requests AS req
               CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats
     GROUP BY query_hash) AS t
ORDER BY Total_Request_Cpu_Time_Ms DESC;

-- missing index:
SELECT
   CONVERT (varchar, getdate(), 126) AS runtime
   , mig.index_group_handle
   , mid.index_handle
   , CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans)) AS improvement_measure
   , 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + '
        (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL
        AND mid.inequality_columns IS NOT NULL
        THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')'
        + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
   , migs.*
   , mid.database_id
   , mid.[object_id]
FROM sys.dm_db_missing_index_groups AS mig
   INNER JOIN sys.dm_db_missing_index_group_stats AS migs
      ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details AS mid
      ON mig.index_handle = mid.index_handle
 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

-- resource stats
SELECT TOP 1000 *
FROM sys.resource_stats
WHERE database_name = 'resource1'
ORDER BY start_time DESC;

SELECT end_time, avg_data_io_percent, avg_log_write_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

SELECT  TOP 10  
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p 
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs 
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

sys.dm_db_resource_stats

sys.resource_stats

sys.query_store_wait_stats

sys.dm_db_wait_stats 

sys.dm_os_waiting_tasks 

sys.dm_exec_requests 

-- restrictions

-- cannot make cross database calls in sql azure unless you are using elastic pool;
-- even if you are using elastic pool, you still cannot make cross database calls to master database
-- cannot use "use master" or "use mydb"

-- cannot use "sql profiler" in sql azure database
-- use querystore to mimic ( cached sql )

1):
extended event 

ring_buffer or file target 
1): ring_buffer target

2): file_target 

event_file 
... ...
ADD TARGET
package0.event_file
(
-- TODO: Assign AzureStorageAccount name, and the associated Container name.
-- Also, tweak the .xel file name at end, if you like.
SET filename =
 'https://gmstorageaccountxevent.blob.core.windows.net/gmcontainerxevent/anyfilenamexel242b.xel'
)

-- query the result
SELECT
*, 
'CLICK_NEXT_CELL_TO_BROWSE_ITS_RESULTS!' as [CLICK_NEXT_CELL_TO_BROWSE_ITS_RESULTS],
CAST(event_data AS XML) AS [event_data_XML]  
-- TODO: In ssms.exe results grid, double-click this cell!
FROM
sys.fn_xe_file_target_read_file
-- (
-- TODO: Fill in Storage Account name, and the associated Container name.
-- TODO: The name of the .xel file needs to be an exact match to the files in the storage account Container 
-- (You can use Storage Account explorer from the portal to find out the exact file names or you can retrieve 
-- the name using the following DMV-query: 
-- select target_data from sys.dm_xe_database_session_targets. 
-- The 3rd xml-node, "File name", contains the name of the file currently written to.
-- )
'https://gmstorageaccountxevent.blob.core.windows.net/gmcontainerxevent/anyfilenamexel242b',
null, null, null
);
GO

SELECT * FROM [sys].[database_firewall_rules];

select @@version;

select @@servername;


select database_id, db_name(database_id) db_name, 
edition, service_objective,elastic_pool_name
from sys.database_service_objectives;

--Edition, Standard, Business

-- ALTER DATABASE MyDb MODIFY (EDITION='business', MAXSIZE=10GB);

1): Azure Database Service Tiers
basic
standard
Premium

-- --------------------------

# Server Properties
select SERVERPROPERTY ('Edition');
-- output: SQL Azure

select SERVERPROPERTY('EngineEdition');  
-- output: 5 ot 8

select @@SERVERNAME, SERVERPROPERTY ('ServerName');


select name, value_in_use, description 
from sys.configurations 
order by configuration_id;

-- --------------------------
-- sessions
dbcc tracestatus;
go

-- --------------------------

select * from sys.database_scoped_configurations;
GO

-- --------------------------

select name,compatibility_level, 
snapshot_isolation_state_desc, 
is_read_committed_snapshot_on,
is_auto_update_stats_on, 
is_auto_update_stats_async_on, 
delayed_durability_desc 
from sys.databases;
GO

-- -----------------------------

-- ALTER DATABASE [db1] MODIFY  (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15');

-- ALTER DATABASE [DBName] MODIFY(EDITION='Standard', SERVICE_OBJECTIVE='S2');

SELECT Edition_Or_Tier = DATABASEPROPERTYEX('Garden', 'Edition');

Select ServiceObjective_ComputeGen = DATABASEPROPERTYEX('Garden', 'ServiceObjective');


SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS Collation;
-- SQL_Latin1_General_CP1_CI_AS

SELECT * FROM sys.fn_helpcollations();

SELECT  d.name,
s.database_id,
s.edition,
s.service_objective,
(CASE WHEN s.elastic_pool_name  IS NULL
  THEN 'No Elastic Pool used'
  ELSE s.elastic_pool_name
  END) AS [Elastic Pool details]
FROM sys.databases d JOIN sys.database_service_objectives s ON d.database_id = s.database_id;

SELECT * FROM sys.dm_operation_status ORDER BY start_time DESC;

-- Rename a database:
-- ALTER DATABASE  mydb1 Modify Name = mydb2 ;

-- ---------------------------------------------------------------------


select * FROM sys.databases;

select schema_name(schema_id) schema_name,
 a.* FROM sys.objects a;

select * FROM sys.objects WHERE schema_id = SCHEMA_ID('sys');

select name, type_desc from sys.objects 
WHERE type in ( 'C', 'D', 'F', 'L', 'P', 'PK', 'RF', 'TR', 'UQ', 'V', 'X' ) 
union
select name, type_desc from sys.indexes
order by name;

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 --WHERE  '.' + m.definition + '.' LIKE '%[^a-z]employeeid[^a-z]%'
 order by type_desc, object_name;

SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024 AS DbSizeInMB FROM sys.dm_db_partition_stats；

clustered vs. non-clustered index
index reorganization (online by default) vs. rebuild (offline by default)

Fill Factor: percent filled, (change default in database setting at server level, sp_configure )

trace flag:
1222: for deadlock: write information to ERRORLOG

--
Insufficient Indexing
Deadlock
Memory Usage ( also CPU Usage )
Security Breaches

--
ERRORLOG in Azure SQL: diagnostic information in Azure Portal
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-metrics-diag-logging

select * from sys.event_log;
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-event-log-azure-sql-database?view=azuresqldb-current
--
turn off: autoshrink db: performance killer
shrink use truncate only
--

SQL Server Profiler Log: *.trc

-- 

DML DDL DCL ( control: grant, revoke) TCL (transaction: commit, rollback )

In Azure Portal -- Select Database -- Click "Export" --- (Enter your storage account information )

SQL Data-Tier Applications (DACs) : a single DAC package (a .dacpac file)

SQL Data-Tier Applications (BACPAC): DACPAC with data
BACPAC is similar to DACPAC, 
but in addition to the database objects (schema),
it also includes the actual data from the database in the package.

Also in SQL Server Management Studio:
SSMS -- Export Datatier Application
SSMS -- Import Datatier Application

SQL Server Bulk Copy (bcp): bulk copy (bcp)

-- -----------------------

REM bcp export:
bcp MyDb.dbo.MyTableName out MyTableName.dat -S .\mysqlserverhost -T -n -q

-T: Windows Authentication
-U sql_login -P sql_password
-n : between sql servers
-q : Allow to use database name, owner, table name, 
     view name contains a space or Single quoation mark
    Similar to SET QUOTED_IDENTIFIERS ON

-- -----------------------
REM bcp import:
bcp myclouddb.dbo.MyTableName in MyTableName.dat `
-S tcp:mycloudservername.database.windows.net `
-U login_id@myserver -P my_password -n

-b batch_size : number of rows to process
-F first_row in the batch file
-L last_row in the batch ile
-h hints  ( eg: -h "TABLOCK" )

-- The newst module called az
--  Install-Moduel Az

Get-Command -Module Az*

-- the old powershell module is called: AzureRM, 
# AzureRM: Azure Resource Manager Module AzureRM 6.13.1 

# To check which version(s) of AzureRM you have installed 
Get-InstalledModule -Name AzureRM -AllVersions

# Enable AzureRM compatibility aliases
Enable-AzureRmAlias -Scope CurrentUser

# Disable AzureRM compatibility aliases
Disable-AzureRmAlias

# To login to Azure Resource Manager
Login-AzureRmAccount

-- powershell
https://www.windowsazure.com/en-us/downloads

-- ----------------------------------------

Install-Module -Name Az
Import-Module Az.Accounts
Import-Module Az.Sql

Install-Module -Name Az.Sql
Update-Module -Name Az.Sql

-- ----------------------------------------
Get-Module -ListAvailable

Import-Module Azure

$env:PSModulePath

Get-AzurePublishSettingsFile

Import-AzurePublishSettingsFile <.publishsettings file>

-- Create New Server
New-AzureSqlDatabaseServer -Location "East US" -AdministratorLogin "" -AdministratorLoginPassword ""

-- setup firewall rule
New-AzureSqlDatabaseServerFirewallRule -ServerName  -RuleName  -StartIpAddress  -EndIpAddress

-- Create New Database
$creds = new-object System.Management.Automation.PSCredential ("", ("" | ConvertTo-SecureString -asPlainText -Force))
$context = New-AzureSqlDatabaseServerContext -ServerName  -Credential $creds

1):
--  -Edition, -MaxSizeGb, and -Collation
New-AzureSqlDatabase -Context $context -DatabaseName MyNewDb

2):
New-AzureSqlDatabase -Context $context -DatabaseName MyBigDb -Edition Business -MaxSizeGB 150

2a):
New-AzSqlServer -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
    -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

3): or change database edition
Set-AzureSqlDatabase -Context $context -DatabaseName MyNewDb -Edition Business -MaxSizeGB 20

4): delete database 
Remove-AzureSqlDatabase -Context $context -DatabaseName MyNewDb

5):
-- List all databases available on the server
Get-AzureSqlDatabase -Context $context
$database = Get-AzSqlDatabase -ResourceGroupName myResourceGroup -ServerName mynewserver-20171113 -DatabaseName mySampleDataWarehouse
$database | Select-Object DatabaseName,Status

6):
Suspend-AzSqlDatabase -ResourceGroupName "myResourceGroup" `
-ServerName "newserver-20171113" -DatabaseName "mySampleDataWarehouse"

-- 
SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024 AS DbSizeInMB FROM sys.dm_db_partition_stats

6): suspend all databases, output a list

$database = Get-AzSqlDatabase -ResourceGroupName "myResourceGroup" `
-ServerName "newserver-20171113" -DatabaseName "mySampleDataWarehouse"
$resultDatabase = $database | Suspend-AzSqlDatabase
$resultDatabase

7): resume a database
Resume-AzSqlDatabase -ResourceGroupName "myResourceGroup" `
-ServerName "newserver-20171113" -DatabaseName "mySampleDataWarehouse"

8): resume all database

$database = Get-AzSqlDatabase -ResourceGroupName "ResourceGroup1" `
-ServerName "Server01" -DatabaseName "Database02"

$resultDatabase = $database | Resume-AzSqlDatabase

$resultDatabase

1): Project Management: requirement, tasks, bugs, progress and reporting

2): Source Control: 1): Change history 2): team development environment

3): Build and Deployment: Managing release cycles


Azure DevOps Tutorial For Beginners | Azure DevOps CI/CD Pipeline | Edureka
https://www.youtube.com/watch?v=MOZMw5_fBFA


Azure Pipeline Tutorial | Azure Pipeline Deployment | Azure DevOps Tutorial 
Azure DevOps  ( continuous deploy, integrate ) ( Developers and Operators )

Azure Pipeline  ( continuous deploy, integrate )

devops:  https://dev.azure.com

combine: project (progress), itsm, service now, source control, code promotion

Azure DevOps:Developers, Operators
components:
1. Azure Boards
2. Azure Artefacts (Artifacts)
3. Azure Pipelines ( YAML or .yml file: configuration )
4. Azure Repos
5. Azure Test plans
-- -----------------------------------

dev.azure.com --- dev ops

https://dev.azure.com/myid/project01
https://dev.azure.com

project

overview

summary

Dashboards

Wiki

Boards: Project Management: 1): Documenting requitements/tasks and bugs 2): Monitoring progress and Reporting

Backlog  Work needs to be done, a backlog is a collection of work items which will be used for furture development
         A list of set tasks that must be completed before the product is released.

Product backlog: Product backlog is an ordered list of everything that is known to be needed in the product.

Sprint backlog: Sprint backlog is the collection of work items which are in (delayed?) state
                this list finalizes and defines what the development team will complete during the sprint.

Repos: Repository

Piplelines: 
Azure Pipelines can deploy artifacts that are produced 
by a wide range of artifact sources, 
and stored in different types of artifact repositories.
https://docs.microsoft.com/en-us/azure/devops/pipelines/release/artifacts?view=azure-devops

Test Plans


Artifacts: An artifact (Artefact ) is a deployable component of your application, it has various code types
https://azure.microsoft.com/en-us/services/devops/artifacts/

https://www.azuredevopslabs.com/labs/azuredevops/packagemanagement/

-- -----------------------------------

Deploying Databases using Azure DevOps Pipelines
DACPAC and BACPAC
MERGE STATEMENT
STORED PROCEDURE
-- Confirm Your Script Is Pre- Or Post-Deploy
https://www.danylkoweb.com/Blog/deploying-databases-using-azure-devops-pipelines-NQ

https://dzone.com/articles/deploying-databases-using-azure-devops-pipelines

Continuous Integration and Deployment for SQL Database using SSDT and VSTS
https://mohitgoyal.co/2018/01/10/continuous-integration-and-deployment-for-sql-database-using-ssdt/

-- --------
Azure Pipelines | Azure DevOps Server 2019 | TFS 2018 | TFS 2017
https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml


eg 1:
- task: SqlAzureDacpacDeployment@1
  displayName: Execute Azure SQL : DacpacTask
  inputs:
    azureSubscription: ''
    ServerName: ''
    DatabaseName: ''
    SqlUsername: ''
    SqlPassword: ''
    DacpacFile: ''
-- --------------------------

eg 2:
variables:
  AzureSubscription: ''
  ServerName: ''
  DatabaseName: ''
  AdminUser: ''
  AdminPassword: ''
  SQLFile: ''

steps:
- task: AzurePowerShell@2
  displayName: Azure PowerShell script: FilePath
  inputs:
    azureSubscription: '$(AzureSubscription)'
    ScriptPath: '$(Build.SourcesDirectory)\scripts\SetAzureFirewallRule.ps1'
    ScriptArguments: '$(ServerName)'
    azurePowerShellVersion: LatestVersion

- task: CmdLine@1
  displayName: Run Sqlcmd
  inputs:
    filename: Sqlcmd
    arguments: '-S $(ServerName) -U $(AdminUser) -P $(AdminPassword) -d $(DatabaseName) -i $(SQLFile)'

- task: AzurePowerShell@2
  displayName: Azure PowerShell script: FilePath
  inputs:
    azureSubscription: '$(AzureSubscription)'
    ScriptPath: '$(Build.SourcesDirectory)\scripts\RemoveAzureFirewallRule.ps1'
    ScriptArguments: '$(ServerName)'
    azurePowerShellVersion: LatestVersion

-- --------------------------------
sqlpackage.exe /Action:Script /?

-- ---------------------------------

DevOps using SQL Server ( Architecture )
Build
CI/CD Integration
Container-based Deployment
https://www.microsoft.com/en-us/sql-server/developer-get-started/sql-devops/

www.docker.com

a Jenkins project

git and github

which git

-- ---------------------

RStudio

-- ---------------------

rpm -q git

# to install git,

1):
#yum install curl-devel expat-devel gettext-devel \
#  openssl-devel zlib-devel perl-devel asciidoc xmlto

2):
$ tar -zxf git-1.7.2.2.tar.gz
$ cd git-1.7.2.2
$ make prefix=/usr/local all
$ sudo make prefix=/usr/local install

# git clone git://git.kernel.org/pub/scm/git/git.git
# yum install git

-- ---------------------------------------------

$EDITOR 
export EDITOR=vim
export EDITOR=emacs

/etc/gitconfig
~/.gitconfig

cat .gitignore

git config --global core.editor

git config --list
git config user.name

git config --global user.name "Roger Smith"
git config --global user.email "rsmith@abc.com"

git help config

# git help 
# git  --help
# man git-

# git diff --cached

# git help config

# git status

# git log
# git log --pretty=oneline

# git log --pretty=format:"%h - %an, %ar : %s"

# git log -p -2
# git log -U1 --word-diff

# git log --stat

# initializing

# git init

# git add *.c
# git add README

# git rm README
# git rm --cached readme.txt

# rename a file
# git mv README README.txt

# git commit -m 'initial project version'
# notes: -m: the -m flag means message

# git clone git://github.com/schacon/grit.git
# git clone git://github.com/schacon/grit.git mygrit

# git commit

# git commit --amend
# to commit again

# git last
# to see the last commit

# git diff
# See the difference between staged uncomitted changes and the most recent commit
$ diff --git a/benchmarks.rb b/benchmarks.rb

# git checkout -- benchmarks.rb

# git tag

# git show v1.4

# --------------------------

# git alias
# git config --global alias.co checkout
# git config --global alias.br branch
# git config --global alias.ci commit
# git config --global alias.st status

# ---------------------------

# git branch
# git branch -v
# git branch -a

# git checkout -b patch1 
# Create a branch called "patch1" from the current branch and switch to it.

# git branch testing
# to create a branch

# git checkout testing

# ---------------------------

# git fetch

# --------------------------

git stash:  	Save uncommitted changes in a temporary version and revert to the most recent commit