1 Plan and Implement Data Platform Resources Flashcards

Question

What are the security aspects of SQL Server on Azure VM?

Answer 1

- Auditing works at the server level - Events are stored in the file system or Windows event logs. - Can use Azure Defender for SQL, which includes: Vulnerability assessment and threat detection (costs around .02/instance/hour) - Data encryption, sing Transport Layer Security (TLS), Transparent Data Encryption (TDE), and Always Encrypted. Firewalls - Windows or SQL Server authentication

Answer 2

- Scalability - There hardware limits, but if you divide data into partitions on separate servers, it can be scaled out. - Performance - Smaller amounts of data in a single partition, and multiple data stores can be accessed at the same time. - Security - Apply different security to sensitive and non-sensitive partitions. - Administration - Different strategies of monitoring, management, and backup per partition. Backups for a single partition can be quicker than the entire DB. - Different hardware or services - Premium or Standard where needed - Availability - If once instance fails, only that partition is unavailable.

Answer 3

- All partitions have the same schema - Each partition, or shard, holds a subset of the data (rows) - If some data is fairly static or small, consider replicating it in all partitions to reduce cross-partition access.

Answer 4

- Each partition holds a subset of the columns Some columns may be needed less often, and they could be separated away, and used only when needed. - Some columns may also be more sensitive, and could be separated away. - All partitions would need to be capable of being joined - for instance, by the same primary key in each.

Answer 5

- Different tables in each partition relating to function - Store data could be in one partition and employee data in another. - Some tables could be more sensitive, and could be separated away into another partition.

Answer 6

- Horizontal, vertical, and functional partitioning can be combined - Keep the data, where possible, geographically close to the users. - Consider the backup, archiving (including deleting) and HA/DR requirements for each partition.

Answer 7

- Storage space limitations - Maximum storage capacity can be reached on a server - Computing resource limits - Exceeding this may result in time outs - Network bandwidth- Exceeding this can result in failed requests.

Answer 8

- Add disk capacity, processing power, memory, and network connections - You may reach the same problem later.

Answer 9

- Each shard has its own subset of data - It runs on a server acting as a storage node

Answer 10

- Scale out by adding additional shards - Use off-the-shelf hardware for each storage node - Balance the workload across shards - Locate shards near to the users who will use it.

Answer 11

- Have a shard key (an ID), an a map which shows where the data is stored. - Offers more control - Requires additional overhead.

Answer 12

- Use sequential shard keys in ranges (e.g. one per month) - Similar data is kept on the same storage node, so it can retrieve multiple items in a single operation. - Doesn’t necessarily provide optimal balancing - Rebalancing shares is difficult

Answer 13

- Data distributed evenly among the shards. Reduces hotspots (high loads for an individual server) by using some random element for distribution. - More even data and load distribution - Computing the hash might increase the overall requirements - Rebalancing shards is difficult

Answer 14

- Single database - Elastic pool - Compute tier

Answer 15

- Provisioned - For regular usage patterns, or multiple databases with elastic pools - Serverless compute - on/off usage with a relative low average compute. This supports automatic pausing and resuming. When paused, you only pay for storage

Answer 16

- Specify separate amount of Number of vCores, memory, and amount/speed of storage - Look at * Data maximum memory size * Log size (30% of the data max size) * IOPs * Concurrent workers * Backup retention

Answer 17

- 80 vCores at Gen5 - 4 TB memory - 4 TB database size - Hyperscale has 100TB database size

Answer 18

- Azure Hybrid benefit allowed you to bring in your existing on-prem licenses to the cloud - Reserved capacity is paying in advance at a discount

Answer 19

Local SSDs provisioned in 1 GB increments

Answer 20

- For most business workloads - Storage latency of 5-10ms (about the same as SQL Server on a VM)

Answer 21

- When you need low latency I/O (1-2 ms) or frequent communications between app and database - Large number of updates, or long running transactions that modify data - Higher resiliency, availability, and fast geo-recovery and recovery from failures, and advanced data corruption protection. - Free-of-charge secondary read-only replica

Answer 22

- On-demand scalable storage - Only for Azure SQL database - You cannot subsequently change out of Hyperscale. Costs the same as Azure SQL Database

Answer 23

- GP_S_Gen5_1 - 512 GB - GP_S_Gen5_2, 4, 6 - 102 GB - GP_S_Gen5_10 - 1536 GB - GP_S_Gen5_12, 14, 16, 18, 20 - 3072 GB - GP_S_Gen5_24, 32, 40 - 4096 GB

Answer 24

- Creates 1 file per vCore with 32GB per file - Caps of up to 32 files for serverless computing only

Answer 25

- For light to heavy workloads - Offers bundles of maximum number of compute, memory, and I/O (reads/writes) resources for each class. You cannot separate them. - Uses Azure Premium disks. Provisioned in increments of 250 GB to 1GB, and 256 GB thereafter.

Answer 26

- Basic - For less demanding workloads - Standard - For typical performance - Premium - For I/O intensive workloads - Note - Basic and Standard S0, S1, and S2 have less than 1 vCore, and cannot use “change data capture” - Consider Basic, S0, and S1, where database files are stored in Azure Standard Storage (HDD) for development, testing, and infrequently accessed workloads.

Answer 27

When you need to use more than 300 DTUs Might reduce costs and there’s no downtime to converting

Answer 28

https://dtucalculator.azurewebsites.net

Answer 29

- Don’t do it when there’s a long job running - For the DMVs to have accurate figures, you may need to flush the Query Store after rescaling - EXEC sp_query_store_flush_db

Answer 30

- A server is a logical server, which includes logins, firewall and auditing rules, policies, and failover groups. - The server name must be unique in Azure - Don’t need to manage the instance - Cannot use “USE” to change database context Vs - Serverless bills for compute per second - Pauses databases and billing in inactive periods

Answer 31

- No access - Public/private endpoint. You can add current client IP address - Choose whether to "Allow Azure services and resources to access this server" (for other Azure services)

Answer 32

- Proxy - Uses Azure SQL Database gateways - Redirect - Establish directly to the database node - Default - Redirect if connection originates inside Azure, and Proxy if outside Azure

Answer 33

You can have sample data, or data based on the restore from a geo-replicated backup

Answer 34

CS - Case Sensitive CI - Case Insensitive AS - Accent Sensitive AI - Accent Insensitive

Answer 35

Identifies and mitigate potential database vulnerabilities and threat detection

Answer 36

Full and it should not be changed

Answer 37

General Purpose Business Critical Hyperscale

Answer 38

Budget oriented balanced compute and storage options

Answer 39

OLTP applications with high transaction rate and low IO latency. Offers highest resilience to failures and fast failovers using multiple synchronously updated replicas.

Answer 40

Not for Managed Instance Most business workloads. Auto-scaling storage size up to 100 TB, fluid vertical and horizontal compute scaling, fast database restore.

Answer 41

1 to 80 vCores

Answer 42

1 to 128 vCores

Answer 43

1 to 80 vCores

Answer 44

Remote storage

Answer 45

Local SSD storage

Answer 46

Tiered remote and local SSD storage

Answer 47

Single databases: 4.5 MB/s per vCore (Max 50 MB/s) Elastic pool: 6 MB/s per vCore (max 62.5 MB/s)

Answer 48

Single databases: 12 MB/s per vCore (max 96 MB/s) Elastic pools: 15 MB/s per vCore (max 120 MB/s)

Answer 49

99.99% 99.995% with zone redundant single database

Answer 50

99.95% with one secondary replica 99.99% with more replicas

Answer 51

- Available in Business Critical - Partially supported in Hyperscale. Memory-optimized table types, table variables, and natively compiled modules are supported - Not available in General Purpose

Answer 52

- General Purpose - 0 built-in; 0-4 using geo-replication - Business Critical - 1 built-in, included in price; 0-4 using geo-replication - Hyperscale - 0-4 built-in

Answer 53

1 GB - 4 TB

Answer 54

40 GB - 100 TB

Answer 55

32 GB per vCore at no extra cost

Answer 56

32 GB per vCore

Answer 57

RA-GRS (read-access geo-redundant storage), 1-35 days (7 days by default)

Answer 58

RA-GRS (read-access geo-redundant storage), 7 days, fast point-in-time recovery (PITR)

Answer 59

- vCore, reserved storage, and backup storage are charged - IOPS is not charged

Answer 60

- vCore for reach replica and used storage are charged - IOPS not yet charged

Answer 61

- Reserved instances - Azure Hybrid Benefit (not available on dev/test subscriptions) - Enterprise and Pay-As-You-Go Dev/Test subscriptions

Answer 62

- Azure Hybrid Benefit (not available on dev/test subscriptions) - Enterprise and Pay-As-You-Go Dev/Test subscriptions

Answer 63

- General purpose - Most workloads (default option) - Business Critical ** Low-latency workloads ** High resiliency to failures ** Fast Failovers

Answer 64

- Compute and memory limits - Up to 80 vCores - 400 GB Memory - Up to 100 databases - Up to 16 TB database size

Answer 65

- Cross-database queries - CLR (common language runtime - SQL Agent - msdb database - You can manually make a copy-only backup of a database

Answer 66

- SQL Managed Instances does not support the DTU-based purchased model - Cannot use Hyperscale in Azure SQL MI

Answer 67

A dedicated ring for data

Answer 68

12 regardless of the number of vCores

Answer 69

Budget oriented balanced compute and storage options

Answer 70

OLTP applications with high transaction rate and low IO latency. Offers highest resilience to failures and fast failovers using multiple synchronously updated replicas

Answer 71

4, 8, 16, 24, 32, 40, 64, 80 vCores 2 vCores also available in pools

Answer 72

4, 8, 16, 24, 32, 40, 64, 80 vCores

Answer 73

Remote storage

Answer 74

Local SSD storage

Answer 75

32 GB - 8 TB

Answer 76

32 GB - 4 TB

Answer 77

24 GB per vCore (max about 2 TB)

Answer 78

Up to 4 TB - limited by storage size

Answer 79

3 MB/s per vCore (max 22 MB/s)

Answer 80

4 MB/s per vCore (max 48 MB/s)

Answer 81

99.99% 99.995% with zone redundant single database

Answer 82

Business Critical only

Answer 83

0 built-in 0-4 using geo replication

Answer 84

1 built-in, included in price 0-4 using geo replication

Answer 85

RA-GRS (read access geo-redundant storage), 1035 days, 7 days by default

Answer 86

- vCore, reserved storage, and backup storage is charged - IOPS is not charged

Answer 87

- Reserved instances - Azure Hybrid Benefit (not available on dev/test subscriptions) - Enterprise and Pay-As-You-Go Dev/Test subscriptions

Answer 88

- 95% (18 days) - Standard HDD Managed Disks - 99.5% (1.8 days) - Standard SSD Managed Disks - 99.9% (8 hours) - Premium SSD or Ultra Disks - 99.95% - 2+ instances in the same Availability Set (Different computers in the same datacenter) - 99.99% - 2+ instances in 2+ Availability Zones in the same Azure region (different physical datacenters with independent power, cooling, and networking)

Answer 89

- When you need an older version of SQL Server or access to a Windows Operating System - When you need SSAS, SSIS, or SSRS - When you need features not available in Azure SQL Database or Azure SQL Managed Instance

Answer 90

- At least 4 vCPUs - Memory optimized VMs are best for SQL Server workloads - Higher memory-to-vCore ratio for mission critical or data warehouses. - Azure VM marketplace images are configured for optimal SQL Server performance.

Answer 91

- Place data, log, and tempdb files on separate drives - Data drives should be on Premium P30 and P40 disks for cache support - Log drive should be on Premium P3o to P80 disks, or Ultra disks for sub millisecond latency. - Tempdb should be placed on the D drive on the SSD

Answer 92

- Stripe using Storage Spaces to increase I/O bandwidth. 3+ drives form a storage pool. Which is done by 1. Creating the individual disks 2. Creating the storage pool 3. Creating a single virtual disk from 3 resiliency types - Simple, Mirror, or Parity 4. Creating a volume

Answer 93

- Needs at least 1 physical disk - Stripes data on physical disks - Maximizes disk capacity and increases throughput - No resiliency (does not protect from disk failure) - Use for high-performance where resilenccy is not required by striping

Answer 94

- Needs at least 2 physical disks to protect from single disk failure - 2-3 copies of the data - Increases the reliability, but reduces capacity - Greater data throughput and lower access latency - Use for most deployments

Answer 95

- Needs at least 3 physical disks to protect from single disk failure - Stripes data and parity information across disks - Increases reliability, but reduced capacity - Increases resiliency - Use for archive and backups

Answer 96

Use Local Redundant Storage, not Geo-redundant storage

Answer 97

- General Purpose - Balanced CPU-to-memory; good for dev/test, small to medium DBs or traffic web servers - Compute Optimized - High CPU; good for medium traffic web servers, network appliances, batch processes, and application servers - Memory Optimized - High Memory (up to 4 TB); Good for relational database servers, medium to large caches, and in-memory analytics. - Storage Optimized - High disk throughput; Good for Big Data, SQL, NoSQL DBs, data warehousing and large transactional databases. - GPU - Specialized VMs; Heavy graphic rendering and video editing, as well as model training and inferencing (ND) with deep learning. - High performance compute - Fastest VMs; Most powerful CPU machines

Answer 98

- Enable database page compression where appropriate - Enable backup compression and instant file initialization - Limit auto growth and disable auto shrink - Use one tempdb data file per core, up to 8 files - Apply any CUs for your version of SQL Server - Register with the SQL IaaS Agent extension for automated backup, patching, Key Vault integration, Portal reporting, and more. - Enable auto shutdown for dev/test environments

Answer 99

- When creating the VM, the "SQL Server settings - Change configuration" show the storage - Under "Configure storage" - Select "transactional processing" or "data warehousing". This will change your stripe configuration, optimizing it for OLTP or analytic/reporting workloads - After setting the VM, when using disk caching for Premium SSD, you can select the disk caching level (Settings > Disks)

Answer 100

- ReadOnly for SQL Server data files, as this improves reads from cache (VM memory and local SSD), which is much faster than from disk (Azure Blob storage) - None for SQL Server log files, as the data is written sequentially - ReadWrite caching shouldn't be used for SQL Server files as SQL doesn't support data consistency with this cache type. But it could be used for the OS drive, but isn't recommended to change the OS caching level.

Answer 101

Go to Settings > Compute + storage

Answer 102

- A package of compute, storage, and IO resources - Simple, pre-configured

Answer 103

- Independent scaling of compute, storage, and IO resources - Flexible, control, transparency - Use with Azure Hybrid Benefit for cost savings - Business Critical service tier includes 3 replicas, but is 2.7x the price

Answer 104

- They can be moved in and out of elastic pools - They are isolated from others and are portable - They can be dynamically (aka manually) scaled (but not auto scaled) up and down.

Answer 105

- You assign resources which are shared by all pool databases - You can dynamically scale or auto scale resources up and down. - This is for multiple databases, good when they have variable usage patterns - Can add databases by going to the pool and clicking on "+Add databases"

Answer 106

Based on the amount provisioned, that's the maximum database size

Answer 107

https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single

Answer 108

- Max storage bytes (for all databases in the pool) - Average DTU utilization x number of databases - Number of concurrently peaking databases x peak DTU utilization per database - Unit price for eDTU pools is 1.5x the DTU unit price for a single database - Unit price for vCore pools is the same unit price for single databases.

Answer 109

- Reduced space. Useful for data which is infrequently used - You can compress at the row level, page level, or none - Compression doesn't affect backup and restore - Requires extra time and CPU to compress and retrieve data

Answer 110

- Numeric types will be reduced, maybe down to 1 byte. - Some data types will be compress: datetime, datetime2, and datetimeoffset. Others do not benefit from compression - char and nchar will be compressed, up to 50% in English, German, Hindi, and Turkish, but only up to 40% in Vietnamese and 15% Japanese. - varchar and nvarchar do not benefit.

Answer 111

Page compression is 3 operations in order - Row compression - Prefix compression - Dictionary compression - Pages are uncompressed at first. Page compression is only used when additional rows can be fit on a full page.

Answer 112

- If values in the same column start with the same characters, this can be optimized. - A common prefix per column is moved to the Compression information structure immediately after the page header. - A reference is made in that value to the prefix, and the number of characters which are in common.

Answer 113

- If values after prefix compression in ANY column are the same, this can be optimized. - Common values are moved to the Compression Information structure and a reference is made.

Answer 114

- Azure SQL Database - Azure SQL Managed Instance - SQL Server on VMs **from SQL Server 2016 SP1 in all editions **Enterprise only before

Answer 115

- Tables stored with a clustered index or without - A complete nonclustered index - A complete indexed view

Answer 116

System tables

Answer 117

sp_estimate_data_compression_savings (Not available in Azure SQL Database) - 'SchemaName' - 'TableName' - Index_ID - either 0 for a heap, 1 for a clustered index, or >1 for nonclustered index. NULL if a table - PartitionNumber (Or NULL) - 'ROW' (or 'PAGE', 'NONE')

Answer 118

1. Right click on table/index, and go to Storage > Manage Compression 2. Click next, and select the compression type for each partition. * You can also click "User same compression type for all partitions" * You can also click on "Calculate" to calculate space requirements (not in Azure SQL DB) 3. Select whether to run immediately or create a script (to file, clipboard, or new query window) **If running on VM, you may also be able to schedule

Answer 119

ALTER TABLE schema.tablename REBUILD PARTITION = 1 | ALL WITH (DATA_COMPRESSION = PAGE | ROW | NONE);

Answer 120

ALTER INDEX IndexName ON schema.tablename REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE | ROW | NONE);

Answer 121

- Initially used in data warehouses, but then expanded - Columns are always compressed - Indexes work best when you scan large amounts of data, like fact tables in DWs - Generally clustered. Nonclustered only used when you have a data type not supported by a clustered index. - Can be further compressed using archival compression

Answer 122

- When the data is not often read, but needed to be retained for regulatory or business reasons. - Saves space, but there is a high CPU cost to uncompressing it, which is more than any IO saving.

Answer 123

- What workloads you intend to migrate? - Actual resource requirements. - Downtime allowances - Dependencies between databases, and between databases and applications - Security requirements - Backup and restore requirements - Current and future limitations - Location for data storage (GDPR, California Consumer Privacy Act, or similar)

Answer 124

- Use Azure Migrate - It can discover and assess SQL data estate at scale (across your data center) - Get Azure SQL deployment recommendations, target sizing, and monthly estimates

Answer 125

Use SQL Server Migration Assistant

Answer 126

- Use Data Migration Assistant (DMA) - Can help migrate to Azure SQL Database, or to a VM or to another on-prem server - Can also discover and assess SQL data estate, and recommend performance and reliability improvements for your target environment - Detect compatibility issues between your current DB and a target version of SQL Server or Azure SQL. - Move you schema, data, and uncontained objects.

Answer 127

- Use Database Experimentation Assistant (DEA) - Capture the workload of a source SQL Server environment - Identify compatibility issues

Answer 128

- Use Azure Database Migration Services (DMS) - Minimal downtime (especially if online using the Premium pricing tier). Good for large migrations.

Answer 129

- Allow outbound port 443 (HTTPS), possibly 1434 (UDP) - Enable TCP/IP protocol - Create an Azure SQL Database instance, have a server-level firewall rule to allow DMS access, and have CONTROL DATABASE permission on the target database. - Have CONTROL SERVER permissions on the source. - This doesn't initiate any backups, and uses existing full and log backups (not diffs)

Answer 130

SQL Server to Azure SQL MI MongoDB to Azure Cosmos DB PostgreSQL to Azure DB for PostgreSQL

Answer 131

SQL Server to Azure SQL MI SQL Server to Azure DB SQL Server to Azure SQL VM MongoDB to Azure Cosmos DB MySQL to Azure DB for MySQL

Answer 132

- Enable TCP/IP on your SQL Server instance - Download the Data Migration Assistant - Create a Virtual Network for the Azure Database Migration Service using either ExpressRoute or VPN. - Enable outbound port 443 of ServiceTag for ServiceBus, Storage and AzureMonitor. - Allow database engine access in Windows firewall, and open the Windows firewall to TCP port 1433 (unless you have changed it). You may also need to have UDP port 1434. - Create a server-level IP firewall rule to allow Azure Database Migration Service access. - Your credentials need CONTROL SERVER on the SQL Server instance, and CONTROL DATABASE on Azure SQL.

Answer 133

- In Data Migration Assistant, select +New and Assessment, and enter a project name. - Select Database Engine, SQL Server and Azure SQL Database, and either/both: ▪ Check database compatibility – identifies partially supported or unsupported features which may block migration. There will be recommendations. ▪ Check feature parity – recommendations, different approached, and mitigating steps. - Click Next and provide connection details to SQL Server. - Select databases, click Add and Start Assessment.

Answer 134

- In the DMA, click +New, Project Type, Migration. - Add a project name, SQL Server, and Azure SQL Database - Select “Schema only” in “Migration scope”. - Click Create, and enter your SQL Server details. - Click Next, then enter your Azure SQL details. - Click Connect, and select the relevant database. - Click Next, and specify the schema objects to be deployed. By default, all of them are selected. - Click ”Generate SQL script” then “Deploy schema”.

Answer 135

- In the Azure portal, go to Subscriptions. - In the Azure Database Migration Service subscription, click on Resource providers. - Find “Microsoft.DataMigration” and Register it.

Answer 136

- In the Azure portal, go to this service and click Create, and select: ▪ Subscription, Resource Group, Name ▪ Location, ”Azure” as Service mode, and ▪ Pricing tier (Standard is free) - On ”Create Migration Service”, select an existing VN or create a new one.

Answer 137

- Standard is free for offline (one-off) migration only. - Premium is about $1 for 3 hours. Allows for online (continuous migration) and offline migrations, and faster speeds. - You can have the 4 vCore Premium DMS free for 6 months. You can use it for a total of 1 year, and create 2 DMS services per subscription.

Answer 138

- In the Azure portal, go to “Azure Database Migration Services”, select the relevant instance, and select ”New Migration Project”. - Add a project name, SQL Server, Azure SQL Database, and Data migration. - Click on “Create and run activity”.

Answer 139

- Bulk Copy Program (BCP) can be used from on-prem or a VM to Azure SQL - BULK INSERT - loading data from Azure Blob storage - SSIS packages - ETL - Spark or Azure Data Factory

Answer 140

1. Check for blocking issues 2. Migrate sample schema if necessary 3. Register the Microsoft.DataMigration resource provider 4. Create an instance of the Azure Database Migration Service 5. Create a migration project 6. Enter source settings 7. Enter target settings 8. Run the migration

Answer 141

1. Create validation tests using SQL queries against source and target DBs 2. Create performance tests and compare between source and target DBs 3. Check applications (e.g. references to target DB) 4. Investigate what effect the DB compatibility level may have had 5. Check for missing features.

Answer 142

- Azure SQL DB and Azure SQL MI will always use the latest version - Are existing queries using the best plan under the new compatibility level? - Is there query performance regression? Force the last known good plan if there is - Is there parameter sniffing? Do stored procedures need to be recompiled? - Look for features which work better in the source DB but not the target DB - Look for missing indexes - Look for new features in the latest DB compatibility level.

Answer 143

- Allows you to synchronize data across multiple databases - Tables need to have a primary key, which cannot be changed (rows can be deleted/recreated instead) - Doesn't work with Azure SQL MI - Azure SQL DB must be the Hub, but VMs can be members

Answer 144

- An Azure SQL DB - One is defined

Answer 145

- Contains the metadata and log for Data Synch. - Azure SQL DB in the same region as the Hub DB - It should be empty. Data Synch creates tables and runs a frequent workload

Answer 146

- Either Azure SQL or on-prem/VM (not MI) - You must install a local sync agent

Answer 147

- Sync Schema data shows what data is synchronized - Sync Direction can be both ways or only one direction - Sync Interval is how often synchronization happens - Conflict Resolution Policy is "Hub wins" or "Member wins", but whichever member syncs first wins

Answer 148

- Hybrid Data Synchronization - Distributed Applications, including Globally Distributed Applications

Answer 149

1. Go to Azure portal, SQL Databases 2. Go to the Hub database 3. Go to "Sync to other databases" 4. Go to "New Synch Group" 5. Add Sync members

Answer 150

1. Open the sync group - Database 2. Click on "Add an Azure Database" 3. Select the Sync Member Name, subscription, Azure SQL Server and DB, Sync directions, existing username and password for the member db, and use private link

Answer 151

1. Select "Choose the Synch Agent Gateway" 2. Select "Existing Agent" or "Create new agent" - If new, download the Azure SQL Data Sync Agent, enter an Agent Name, select Create and Generate key, and copy the key to the clipboard 3. On the on-prem SQL Server, run the Client Sync Agent app 4. Click "Submit Agent Key" 5. In the "Sync Metadata Database Configuration", enter credentials for the metadata db server. If automatically created, this will be the same server as the hub db. 6. Click Register. 7. Select either SQL Server or Windows authentication. 8. Click test connection and Save. 9. In the portal, in "Configure On-Premises page", select "Select the Database", and provide a name for the new sync member and the sync directions.

Answer 152

Go to the Sync Group page, tables, and click refresh schema. It may take a while for data to be refreshed.

Answer 153

Exactly the same way as migrating from SQL on Prem

Answer 154

- Export the data - Use SSMS Data tier applications (DAC) - Use SQLPackage CLI, which will also create a backpac - Use the Azure Portal - Use Powershell - Azure CLI

Answer 155

1. Right click on the DB in SSMS. Tasks > Export Data. 2. Select source and destination. 3. This will copy data, but not objects

Answer 156

1. Right click the source DB and got to Tasks > Export Data-tier application. This will create a bacpac, an archive containing schema and data. 2. On the destination server in SSMS, right click the word "Databases" and select tasks > import data-tier application.

Answer 157

1. Open a command prompt and run cd C:\Program Files\Microsoft SQL Server\160\DAC\bin 2. Execute this command sqlpackage.exe /a:Export /SourceServerName:servername.database.windows.net /SourceDatabaseName:dbname /SourceUser:username /SourcePassword:password /TargetFile:C:\Users\backup.bacpac 3. To upload it, assuming you're still in the command prompt, run sqlpackage.exe /a:Import /TargetServerName:MIname.appname.database.windows.net /TargetDatabaseName:dbname /TargetUser:username /TargetPassword:password /SourceFile:C:\Users\backup.bacpac

Answer 158

- Azure SQL MI is not supported - Uses bacpac - Go to the DB, and click on export database - Select a previously created standard storage - You can check the export status by going to the Azure SQL Server (not the DB) and go to Import/Export history - When complete, you can use this for importing into MI using SSMS, or creating a new Azure SQL DB using the portal

Answer 159

Use the New-AzSqlDatabaseExport cmdlet - $exportRequest = New-AzSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName ' - -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype - StorageKey $StorageKey -StorageUri $BacpacUri ` - -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

Answer 160

Use the New-AzSqlDatabaseImport cmdlet - $importRequest = New-AzSqlDatabaseImport -ResourceGroupName "" ` - -ServerName "" -DatabaseName "" ` - -DatabaseMaxSizeBytes "" -StorageKeyType "StorageAccessKey" ` -StorageKey $(Get-AzStorageAccountKey ` - -ResourceGroupName "" - StorageAccountName "").Value[0] ` - -StorageUri "https://myStorageAccount.blob.core.windows.net/importsample/sample. bacpac" ` - -Edition "Standard" -ServiceObjectiveName "P6" ` - -AdministratorLogin "" ` - -AdministratorLoginPassword $(ConvertTo-SecureString -String "" -AsPlainText -Force)

Answer 161

Use az sql db import # get the storage account key az storage account keys list --resource-group "" --accountname "" az sql db import --resource-group "" --server "" --name "" ` --storage-key-type "StorageAccessKey" --storage-key "" ` --storage-uri "https://myStorageAccount.blob.core.windows.net/importsample/sample.bacpac" ` -u "" -p ""

Answer 162

- You want to manage database size and performance - Balance workloads and resources across several Managed Instances - Copy/move databases between dev, test, and prod environments. - Combine databases from multiple instances

Answer 163

1. Go to the Managed Instance. 2. Go to Data managaedment > Databases. 3. Select the database(s), and click Move or Copy at the top. 4. In the source details pane, select the source database(s) and Managed Instance. 5. In the destination details pane, select the destination Managed Instance. 6. Click Review + Start, and then Start

Answer 164

- It will say “Copy/Move in progress” in the “operations details” column - You cancel it - It will say “Copy/Move ready for completion” when the data is transferred. Click “ready for completion” and “complete” to complete - If it takes more than 24 hours, the copy/move is cancelled and the destination DB is dropped

1 Plan and Implement Data Platform Resources Flashcards

(193 cards)