วีดีโอ

Just use the public IP and the port number

@@odintree5704 all node server SQL using port custom, create distributor, publisher and subscriber sucess, but while job subscriber running there was error subscriber can not connect Distributor. please advice

Almost. You are missing the bridge table. Card table - based on my rules. Honestly you could get away without the card table if you wish. Should also have a room type.

⁠ ​​ i captured the card info under the Payment table and the room type under Room table . I wish if there is a way I could send you what I created

@@moabbas5525 It is actually very easy to represent your design by using a dependency notation. It goes like this. Assume you have a table called product and a table called supplier. This is how you would represent the design using dependency notation: Product : productID --> productName, productCost, productSell, supplierID Supplier: supplierID --> supplierName, supplierPhone As you can see we have indicated the name of the table. The primary key for the product table is productID (usually we would underline it but I cannot here). But that is not important because the arrow ( -->) after it means that the column productID DETERMINES all the other columns. That is a definition of a PK. In the product table I have a column called supplierID and in the supplier table we have the PK as supplierID so I can infer that supplierID in the product table is a foreign key to the supplier table and is related to the supplierID column in said table. So you can represent your design like this.

@@odintree5704 I’ll try that thank you so much for taking the time to reply back to me .

Maybe I will do some more. Thanks for the positive feedback. Glad it was helpful.

Glad you liked it. Nice GIF

Glad it was helpful!

Glad you found it useful.

there is a lot of steps cant we make a batch file?

These are 3VM's so yes technically they are separate servers. You would have to talk to your IT department to get an idea of the network configuration - for domains and AD's. SQL Server Agent is installed during server install but not on express. If you need to you can do a repair and it should fix it for you.

hello so how exactly do we do repair sql installtion? maybe in the sql installtion software link i think its in the main menu--also i just doing local self testing not in an actually work it setting--Lisa thxz alot for the replies---PS also so we need to disable firewall for servers to ping each other and exchange merge shard sync data info? i stil cant ping seperate physical machines

@@pjmclenon I would not disable the firewall, I would just open a port - default port is 1433 but if you use named instances you should use static ports and open the ports you specify.

hello--okay i will try this

Great question. I am happy to say that what I think you want to do is possible. I know most people say that you need to create the table structure first on the subscriber but this is not required. You can replicate just the data from the view without creating the underlying data structure first. Currently I do not have the time to make the video but I googled and found this link. It should get you what you want, Keep in mind you will not be doing everything mentioned in the article. For instance you are not adding new columns to the underlying data. Take a look at the link and give it a try and if you have any issues let me know and I will see if I can help. virtual-dba.com/blog/replicating-indexed-views-as-tables/

You are correct. Sorry for the late response but youtube sometimes does not show comments. So the only way I find some comments is to scroll through them.

Sorry qqq - for some reason TH-cam did not display your comment to me. You are correct that TDE does not affect the app. Keep in mind there is a small performance hit since data needs to be constantly encrypted and decrypted. You could get up to a 3% performance hit, but depending on what is in the cache this could be reduced.

The publisher needs to talk to the distributor. The distributor needs to talk to the subscriber. As for the ports it depends on your network setup. How many routers (firewalls) are being traversed? Port 1433 (default instance) - standard database engine - Port 80 if connecting over HTTP (if the standard HTTP has not been changed) FTP if you are using the web sync - Port 21 or UDP Port 137,138 possibly TCP 139 and possibly port 445 Need to know your network configuration - I would just try it first with port 1433 and see what happens.

@@odintree5704 if publisher -> distributor -> subscriber is one way port 1433, then why we need to create shared folder for repldata? I think we need publisher <-> distributor ports open both ways.

Also can publisher have it's own subscriber for different replication/subscriber, so kinda one to many replication. to me it looks like publisher has to start clean and shouldn't have any existing publication.

thanks

TDE is transparent to the front end application. You do NOT need to make any changes what-so-ever to your front end application to interact with a TDE enabled database.

You're welcome and good luck with your interview.

Hello JV, Could you rephrase your question please. I am not sure what you are asking.

@@odintree5704 aw sorry ., the situation is like this., i have a 2 servers that needs to be replicated like if the other server have a changes the other server will also be updated ., what process of replication should i do ?

@@jvofficial5838 Sounds like you want Merge Replication

@@odintree5704 both server's receive transactions still merge replication ?

@@odintree5704 sir question.., if the publisher(server1) runs Off., the distrution and subscriber can proceed ? and if yes. then when the publisher wakes.,. would it be the subscribers data be copied or prioritize?

Do you mean between two different RDBMS.You can do a simple google search and get the following instructions for an oracle publisher learn.microsoft.com/en-us/sql/relational-databases/replication/publish/create-a-publication-from-an-oracle-database?view=sql-server-ver16 Or you can try Goldengate which has been purchased by Oracle.

Hello Amaury, That procedure is not straight forward and impossible for me to explain here. I have found this link and it is pretty thorough for moving the distributor. As you may or not be aware tthere is no mechanism for just moving the distributor. You must drop it and recreate it. But as I said the following link has very good step by steps instructions. Good luck. www.sqlshack.com/sql-server-transactional-replication-moving-distribution-database-step-by-step-guide/

Hello Kidist, Good question. I have not looked at the sample database I used for this video in awhile but I will guess that the email does not determine the reservation number since the same person could have multiple reservations with the same email. As to whether a non key attribute could determine the PK it is possible. For instance if a table is in 3NF but has 2 candidate keys then the alternate key could determine the PK. For instance assume an employee table: EmplID --> EmpFname, EmpLname, SSN As you can see we have 2 candidate keys. EmpID and SSN. SSN is the alternate and will also be unique. Hope that helps.

Good question - unlike a cluster enviro where you point to the cluster ip the Listener always has to be pointed to the primary replica. SQL Server is cluster aware and can switch automatically with a cluster but not without a cluster so we have to manual identify the primary since the primary always owns the listener in an AG.

Hello CS, I am at home right now and do not have access to an AG configuration but you could verify. I assume you set the primary to R/W and the connection intent to ReadONly. This I believe will stop any connection string that has application intent = readonly. Check if they can directly connect to your primary without an intent string and see if they can query. If they cannot then you are good but if they can then you may need to remove some database level SELECT privileges (on the Primary only) for users that have only read access. This will force them to only read from the secondary whether the connection string contains intent = readonly or not. Let me know if that makes sense and if you can directly connect to your primary without the intent string in your connection string and still run a Select statement be fore applying the database level restrictions I mentioned.

@@odintree5704 I guess I should have been more specific, I have one data warehouse developer, he's the only one touching this. he creates the reports and they are published out on the sharepoint server. His access is from a Data Warehouse server via a linked server. So really there would be no accidental way to be pulling from the primary. I'll test what I have and see if it works, that's all I can do at this point. I'll post up the configs i used once I work with him on the setup. I'm not sure if I'm making any sense here, but I've not set anything for application intent as I didn't think i would need to.

@@Bullbluegill Thanks CS. I do not know if you know the answer but do you know if your DW is multi-dimensional or tabular. Also do you use cubes. How large is the DW. I really like working with DW and I really like the multi-dimensional approach with cubes. If you could give a description of your setup I would appreciate it. I am just curious because I read different things but I really want to know what people are actually using not what sales/marketing people are trying to push. If you use tabular I would be curious how large the data is and how much memory on your servers.

@@odintree5704 I'll find out for you. I'm still waiting on the DW folks to test...they are super busy with implementing a new ERP system...2 years into it, about 18 months to go. I do know this....they use cubes. It's big as I back up the data, about 800GB big comprised of about 6 DB's. They basically do extracts from our PeopleSoft system, load them into the DW daily. All of our reporting is done via the DW and reports are housed in sharepoint. Outside of that, I don't know too much. The read scale AG is for something totally different. It's for reporting off a different set of applications. The DB servers are not super power houses, 4 core, 16 GB and I allocate 12 to SQL. The extract and loading process takes about 5 hours, from midnight to approx 4:30 to 5 am.

Wow - loaded question. As far as I know there is no specific order. But my preferred order and it is mine and I don't say it is right or wrong or the best or that my logic makes any sense. I move the user databases first and then the system and master. My thinking (right or wrong) is that the master database contains the locations of all user databases and files (just in case the location is corrupted in the user databases) and as you move the databases the locations get updated in the master. Then I move Master last and I feel more secure that the Master has the proper locations. Obviously to move the system databases you need to follow some very specific rules even after they have been moved - such as manually changing the registery entry, certain service account privileges, etc. See the full instructions here learn.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?source=recommendations&view=sql-server-ver16 Good luck - let me know how you made out and if you ran into any problems.

@@odintree5704 That is exactly how i was going to do it as I found one guy have a 2 part post. he did the user, then the system in which master was last. I do understand the process of moving everything, the reg, agent job stuff, default paths and such...so I'll let you know how it goes. Thanks again for the reply. Very helpful. Sorry for the loaded question, but I'm sure i'm not the only one that will run into this.

Glad it was helpful!

Hello CS, Good to hear from you. Unfortunately Availability groups as far as I know the replicated database MUST have the same name as the primary. Just to be sure I did look that up in the documentation and that is a requirement. It does make sense. Not sure why the report writers want that but just thinking out loud here I believe you have you backups done using a 3rd party software on the primary replica. Could you try just creating an SQL Server Agent Job to do a copy_only backup on the secondary replica and then rename the copy_only and use that for the report writers. I know it seems clunking but not sure what else to do. Maybe someone else may have a suggestion. Not even sure you can do a copy_only backup on the secondary replica without a cluster. I know we can do it when it is in a cluster. Be curious if you can.

@@odintree5704 Thanks for the reply. Yes, I think the same names make total sense, but when it comes to others, they have different ideas usually based off what's easier for them. I think all their code, and it can't be much to change a DB name, was done for transactional replication and a naming scheme of DB_PD and DB_TS (their idea). We are going away from that because it seems way too hands on. Agent always stopping for no reason and not logged. there are constant changes to the DB so almost real time changes to be replicated and moved are essential. I'll just tell them the names are going to be the same, live with it, change your code. I know you are thinking, how can an agent stop running and not be logged anywhere....believe it. Nothing in history, nothing in a log of any type. I believe it's because it's not failing to start so it's not logged. Something the network or server team is doing at night is stopping it but no one seems to want to find it. Yes, we use a 3rd party app for backups. That is also one reason we went away from Native Sql backups because agent jobs seemed so unreliable and visibility was somewhat limited even with alerts. We bought Idera SQL Safe, it was cheap, very easy and has been fantastic. Visibility is great, it's super fast too if you don't have encryption enabled using TDE.

@@Bullbluegill Exactly. Developer just needs to do a search and replace. Since the server name is different and probably even the instance name there should be no problem. Easy change. One thing I have noticed and was thinking of doing a video on was SQL Server agent jobs and a situation I have noticed. When I create jobs each command gets its own job step. For instance if you wanted to backup 3 databases and issue the backup command 3 times for the 3 databases and put all 3 backup statements in the same jobstep command, if the first backup fails you get a notification, but if the 1st backup succeeds but the next backup fails, no notification is sent - it appears that the job step was successful. But if you put all backup statements in 3 job steps any one that fails you get a notification. When new people who come in and create a job with multiple commands in one step I get them to change it when I find it. I just do not have the time to trouble shoot issues that can be easily rectified in the first place with a little extra effort. I have never dug too deeply into why this happens I just create job steps for each command and I do not get the problem of step failures for these types of problems. I assume you checked the sysjobstepslog and sysjobhistory tables in the msdb database.

@@odintree5704 my reasons for gong away from Transactional replication was made well before the whole stopping of the agent jobs. When you have DB part of replication, when there are changes you have to remove the articles (tables) from replication, then add them back in. To me, that technology was just too hands on. We have a process, when followed correctly it was fine. When I wasn't notified or they didn't check articles back in we had report discrepancies. So it was not a set it and forget it type of process. So our minds were already made up to move to AG. The agent jobs were just a aftermath basically. I spent time, but didn't spin those wheels too long as I don't have it and going away from it anyway. I just need to make all file paths and instance names the same and we'll be set. Those tables did log something but a generic MS error, something to the effect "job was canceled by user blah blah". There is a process that runs nightly, but again, not worth the time at this point. Until I get to AG, i just have the log reader agent status open and peek at it a few times a day.

@@Bullbluegill I agree - AG is a lot more friendly than replication for a lot of cases.

Thank you. Glad it is helpful.

Yes you can and that is what I always do. That is what I do at the end with the surrogate keys. I make all my primary keys a surrogate key with auto numbers or sequences. I also never call my surrogate PK's just "ID" because that gets confusing when you start creating the queries and any code. I use my naming convention so I would call it something like EmployeeID, CustomerID, etc. rather than just id for all the tables.

Yes. But as indicated in the video I did say I would do it at the end because a lack of screen space. I wanted all the columns to show on the screen. I indicated that I would just have to remember to do it in the last step. But you are correct that it is supposed to be done in the 1st step.

Since when is a DB with surrogate keys not considered a relational database? That is one of the most ridiculous statements I have ever heard. Now if you said it violates 3NF I would 100% agree. But that is perfectly acceptable and is common practice. As a matter of fact a lot of databases violate 3NF. Depending on the use of the DB most DBs would be violating 3NF by not breaking out tables for city, state, country, zip/postal. But who is going to create a table with every know city in say just the USA in every state and create a table for the many ZIP codes. You would only see that for an application for the P.O.. I honestly would not want to ever work on one of your DBs since I can picture the complete mess they would be in.

Obviously, you have missed the point of the video. It was a primer on normalization and the example has limited rules to keep it as simple as possible but still show the concept of normalization. The example also does not take into account renovations to existing rooms and many other things. Where do we end and how complicated would you recommend making it for beginners? But if you point me to your video on normalization I may learn something.

Why not?