add new fields to csv file using powershell

$z = Import-Csv zerotrac.csv
$nums= Import-Csv allleetcode.csv
$md=@{}
#converting one csv into hashmap for quicker search
foreach($r in $nums){
    $md.add($r.frontendQuestionId,$r)
}

foreach($r in $z){
    $nr=$md[$r.id]
    if ($nr){
     $r | Add-Member -MemberType NoteProperty -Name "isPaidOnly" -Value $nr.isPaidOnly
     $r | Add-Member -MemberType NoteProperty -Name "difficulty" -Value $nr.difficulty
     $r | Add-Member -MemberType NoteProperty -Name "topicTags" -Value $nr.topicTags

}

}


$z | Export-Csv -Path "C:\temp\newzerotrac1.csv" -NoTypeInformation

how to configure server while Running sql and BizTalk on same server

When running SQL Server and BizTalk Server on the same machine, it’s crucial to configure both to ensure optimal performance and avoid resource contention. Here are some best practices and configuration steps to follow:

1. Hardware Considerations

• Memory (RAM): Ensure you have adequate memory, ideally 16 GB or more. SQL Server and BizTalk are both memory-intensive applications, so having sufficient RAM is essential.
• CPU: Multi-core processors are recommended, ideally at least 4 cores. Assign CPU affinity if necessary to allocate specific cores to each application.
• Disk Storage: Use separate physical disks (or separate logical partitions if physical separation isn’t possible) for BizTalk’s database files, log files, and BizTalk’s tracking data. This separation minimizes I/O contention.

2. SQL Server Configuration

• Limit SQL Server Memory Usage: To prevent SQL Server from consuming all available memory, set a maximum memory limit for SQL Server. Go to SQL Server Properties -> Memory and set a reasonable cap (e.g., if you have 16 GB, allocate 8-10 GB for SQL).
• Configure TempDB: TempDB can be heavily utilized by BizTalk, so ensure it has multiple data files (one per CPU core, up to 8 files). Each should be set to the same initial size and auto-growth increment.
• Optimize Disk I/O: Place data files and log files on separate drives, if possible, to reduce contention. If the machine uses SSDs, this will significantly improve performance.
• Backup and Maintenance Plans: Regularly back up BizTalk databases, and configure SQL Server Agent jobs to manage and maintain indexes and statistics, especially for message box and tracking databases.
• Disable Unnecessary SQL Server Features: Turn off features that BizTalk doesn’t need, such as SQL Agent jobs unrelated to BizTalk, unnecessary SQL Server features, or components.

3. BizTalk Server Configuration

• Optimize Host Instances: Separate processing, tracking, and adapter communication into different hosts to avoid performance bottlenecks.
  • In-process hosts: For orchestration processing and message processing.
  • Receive hosts: For receiving adapter processing.
  • Send hosts: For sending adapter processing.
• Configure Throttling Settings: Adjust BizTalk throttling settings in the Administration Console to optimize memory and CPU usage, particularly if the server is under heavy load.
• Disable Tracking (if not required): Disable tracking for specific hosts if it’s not needed to save disk space and reduce CPU usage.
• Limit Maximum Message Size: Set maximum message sizes for BizTalk to avoid excessively large messages slowing down processing or overwhelming SQL.

4. Operating System and Network Configuration

• Power Settings: Set the server’s power plan to High Performance to avoid CPU throttling.
• Disk Write Caching: Enable write caching on the disk but be cautious; ensure the server has UPS protection as write caching can cause data loss if there’s a sudden power failure.
• Network Optimization: Use dedicated network interfaces for SQL Server and BizTalk network traffic if possible. This helps avoid network contention on a single interface.
• Firewall and Security Settings: Ensure proper firewall rules are in place to allow only necessary traffic to SQL and BizTalk services.

5. BizTalk Database Maintenance

• BizTalk Database Jobs: Enable and regularly monitor BizTalk’s built-in SQL jobs, such as Backup BizTalk Server (BizTalkMgmtDb), DTA Purge and Archive (BizTalkDTADb), and MessageBox_Message_Cleanup_BizTalkMsgBoxDb. These jobs help maintain database health and performance.
• MessageBox and Tracking Database Size: Keep the BizTalkMsgBoxDb and BizTalkDTADb databases at optimal sizes. Purge data regularly, especially if tracking is enabled, to prevent database growth from impacting performance.

6. Monitor Performance Regularly

• Use Performance Monitor (PerfMon) to track critical counters, such as:
  • BizTalk: Messaging Database Size, Host Throttling State
  • SQL Server: Buffer Cache Hit Ratio, Page Life Expectancy, Batch Requests/Sec
  • System: CPU Usage, Available Memory, Disk I/O
• Implement alerts for critical counters so you can take action before performance issues arise.

7. Consider Virtualization and Licensing

If SQL Server and BizTalk are sharing a virtualized environment, consider separating them into different VMs to allow easier scaling and isolation. Also, review SQL and BizTalk licensing requirements to ensure compliance when running both on the same server.

By following these steps, you can run SQL Server and BizTalk Server on the same machine in a way that minimizes resource contention, ensuring smooth operations.