Free DS0-001 sample questions

Correct answer: Create a new service account exclusively for the business intelligence application.

Correct answer: A primary node with at least one synchronous standby using streaming replication, managed by a tool like Patroni or Stolon.

This architecture meets all requirements. Synchronous streaming replication ensures that a transaction is not considered committed until it has been written to both the primary and the synchronous standby, guaranteeing an RPO of zero. High-availability management tools like Patroni or Stolon use a distributed consensus store (like etcd or Consul) to manage the cluster state, handle leader election, and trigger automatic failover well within the 30-second RTO. They also facilitate routing read queries to the standby nodes.

Correct answer: Use an offline data transfer appliance service provided by the cloud vendor.

Transferring 10TB over a 1 Gbps connection would take approximately 24 hours, leading to significant downtime. Offline data transfer appliance services (like AWS Snowball or Azure Data Box) are designed for this exact scenario. The company can load the 10TB of data onto a physical appliance, ship it to the cloud provider, and have the data loaded directly into their cloud environment. This method is much faster for the initial bulk load than a network transfer over a limited connection, thus minimizing the initial migration downtime. Subsequent changes can be synchronized over the network.

Correct answer: Create a composite index on (`CustomerID`, `OrderDate`) on the `Sales` table.

The presence of a full table scan on the largest table (`Sales`) indicates a missing or ineffective index for the query's `WHERE` clause. Since the filter applies to both `CustomerID` and `OrderDate`, a composite index on these two columns is the most effective solution. This allows the database engine to quickly locate the relevant rows without scanning the entire table, providing a dramatic performance boost. While separate indexes could be used, a single composite index is far more efficient for queries that filter on both columns simultaneously.

Correct answer: Create database views that expose only the non-PII columns and grant developers access to the views.

This solution adheres to the principle of least privilege. By creating views that select only the necessary, non-sensitive columns from the underlying tables, an abstraction layer is created. The developers' direct access to the base tables can be revoked, and they can be granted `SELECT` permissions only on these secure views. This allows them to perform their debugging tasks without ever having access to the PII, effectively mitigating the security risk and helping achieve GDPR compliance.

Correct answer: False

The statement is false. The property being described is `DURABILITY`, which guarantees that once a transaction is committed, it will remain so, even in the event of power loss, crashes, or errors. `CONSISTENCY` ensures that a transaction brings the database from one valid state to another, preserving all predefined rules, constraints, cascades, and triggers.

Correct answer: PowerShell

PowerShell is the native, object-oriented scripting language and automation framework for Windows. It is ideal for this task as it has built-in cmdlets for interacting with SQL Server (`Invoke-Sqlcmd`), file system operations (`Compress-Archive`), and network shares (`Copy-Item`). It allows the entire workflow to be managed within a single, powerful script without installing third-party tools.

Correct answer: Column-oriented database

Column-oriented databases, like Apache Cassandra or HBase, are exceptionally well-suited for time-series data from IoT applications. They excel at high-velocity writes and are designed for massive horizontal scalability. The data model, which stores data by column rather than by row, is highly efficient for the specified query pattern of retrieving data for a specific sensor (partition key) over a time range (clustering key).

Correct answers: Daily full backups with transaction log backups every 10 minutes.; Weekly full backups, daily differential backups, and transaction log backups every 15 minutes.

This strategy meets the RPO of 15 minutes because the maximum potential data loss is limited to the 10-minute interval between log backups. The RTO of 4 hours is achievable as restoring a full backup and subsequent log files can typically be completed within this timeframe.

This strategy meets the 15-minute RPO because transaction logs are backed up every 15 minutes. The RTO is also met because restoring the full backup, the latest differential, and the subsequent log files is a standard procedure that can be accomplished within a 4-hour window.

Correct answer: On Server A or Server B within the Internal Network.

According to the principle of defense in depth, databases containing sensitive data should be placed in the most secure network segment, which is the Internal Network. This network is protected by two layers of firewalls (the external and internal firewalls). Placing the database here isolates it from direct internet exposure and even from the semi-trusted DMZ. The Application Server, which resides in the same trusted zone, can then communicate with the database securely.